• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20445
  • Last Modified:

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


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;"
      End With
      Dim sSQL,rs,vRows, strtext
      Set rs = CreateObject("ADODB.Recordset")
      sSQL = "SELECT * FROM [" & sSheetName & "$]"

      rs.open sSQL, conn

      do while not rs.eof
            for each field in rs.fields
                  if Trim(strtext)="" then
                        strtext=strtext & field.value
                        strtext=strtext & "      " & field.value
                  end if

      Set rs = nothing
      Set conn = nothing
End Function
  • 2
1 Solution
check out the answer in this question.  it does what you need.
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
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.
YZlatAuthor Commented:
Thanks! That was exactly what I needed
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now