asked on

Read from Excel using VBScript

I use the following VBScript function to read data from Excel.
It works fine, but I have to specify the spreadsheet name (sSQL = "SELECT * FROM [" & sSheetName & "$]").
Is there a way to read from Excel without specifying spreadsheet name?

For example now I call the function

ReadDateFromExcel("c:\myfile.xls", "Sheet1")

I'd like to omit the spreadsheet name (Sheet1) and just read from the first spreadsheet in Excel document, regardless what it's named

ReadDateFromExcel("c:\myfile.xls")

Function ReadDataFromExcel(sFileNameAndPath, sSheetName)
      Dim conn
      Set conn = CreateObject("ADODB.Connection")
      With conn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = _
"Data Source=" & sFileNameAndPath & ";" & _
"Extended Properties=Excel 8.0;"
            .Open
      End With
      Dim sSQL,rs,vRows, strtext
      Set rs = CreateObject("ADODB.Recordset")
      sSQL = "SELECT * FROM [" & sSheetName & "$]"

      rs.open sSQL, conn

      i=0
      do while not rs.eof
            for each field in rs.fields
                  if Trim(strtext)="" then
                        strtext=strtext & field.value
                  else
                        strtext=strtext & "      " & field.value
                  end if
            next
            i=i+1
            rs.moveNext()
      loop

      rs.close
      Set rs = nothing
      conn.close
      Set conn = nothing
      vRows=Split(strtext,vbTab)
      ReadDataFromExcel=vRows
End Function

ASKER CERTIFIED SOLUTION

WMIF

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kevp75

problem with working with excel files (as opposed to a spreadsheet saved as a csv or txt file) is that it can have multiple worksheets.

I think that code in the linnk WMIF posted is good if you know the worksheet nameas already (well...at least for the accepted answer) I haven't tried out that FSO method to find the name, but that may be what you need. Or even further down the post:

dim xlADOConn, rs, strFileName
strFileName = server.mappath("/myfile.xls")
set xlADOConn = Server.CreateObject("ADODB.Connection")
Call xlADOConn.Open("Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & strFileName & ";")
Set rs = xlADOConn.OpenSchema(20)
if not rs.eof then
SheetName = rs("Table_Name")
end if
set rs = nothing
set xlADOConn = nothing

please no points for me...just pointing and elaborating a little on WMIF's post

WMIF

whoa, that accepted answer is not what i remember reading earlier. i must not have been paying that good of attention. sorry about that.

@kevp75 - glad you found something in there to work for him though.

YZlat

ASKER

Thanks! That was exactly what I needed