Solved

Read from Excel using VBScript

Posted on 2006-11-03
4
20,113 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:YZlat
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
WMIF earned 125 total points
Comment Utility
check out the answer in this question.  it does what you need.
http:Q_21369273.html
0
 
LVL 25

Expert Comment

by:kevp75
Comment Utility
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
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
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.
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
Thanks! That was exactly what I needed
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now