Accessing a .xls spreadsheet to retrieve data

Results 1 to 2 of 2

Thread: Accessing a .xls spreadsheet to retrieve data

  1. #1
    Join Date
    Dec 1969

    Default Accessing a .xls spreadsheet to retrieve data

    I am i need of assistance. I am trying to access a .xls file and post the data to sql db. I have tried to use the FileSystem object to no avail. Also, I have tried the Excel application object. I can create .xls file all day, but I can not read them. The error is Object does not support this action. Any thoughts?<BR>Thanks!<BR>Tommy

  2. #2
    Join Date
    Dec 1969

    Default RE: Accessing a .xls spreadsheet to retrieve data

    Take a look at this code, Tommy. I have had a pretty easy time with this. Basically, you just specify the .xls file as a data source, name a range of cells in the spreadsheet (e.g. &#039FourteenRows&#039 in my code below), and treat that range as a recordset.<BR><BR>To name a range of cells: open up your spreadsheet, select all the contiguous cells that you want, go to the Insert menu, select &#039Name&#039 then select &#039Define&#039 then give it a name. Refer to the code below for the rest.<BR><BR>It&#039s easy! Have fun!<BR><BR>--------------------------<BR>Set objConn = Server.CreateObject("ADODB.Connection")<BR> objConn.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & Server.MapPath("../documentation/zip.xls")<BR> Set objRS = Server.CreateObject("ADODB.Recordset")<BR> objRS.ActiveConnection = objConn<BR> objRS.CursorType = 3 &#039Static cursor.<BR> objRS.LockType = 2 &#039Pessimistic Lock.<BR> objRS.Source = "Select * from FourteenRows"<BR> objRS.Open<BR>---------------------<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts