Calling an Excel sheet in VBScript

Question

I have the following code:

Option Explicit
Randomize
Dim a, song, album
a = Int((Rnd*195)+1)
song = "B" & a
album = "A" & a

Dim objApp, objWbs, objWorkbook, objSheet

Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open("C:\Users\Name\Documents\Music.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")

song = objSheet.Range(song).Value 
album = objSheet.Range(album).Value

objWorkbook.Close False
objWbs.Close 
objApp.Quit 

Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing

MsgBox("Album name: " & album & vbNewLine & "Song name: " & song)

It prints two random cells between row 1 and row 195 from the Excel sheet "Music". One of them - the one in column A - represents the album, and the other represents the song. The problem is that it takes quite a long time to return the results, about 20 seconds.

I was wondering whether there was a more efficient method I could use to get the results more quickly.

Answer 1

I think Ansgar Wiechers' answer is probably correct that starting Excel is the slowest part of the script. You could try using ADO to connect to the Excel file as if it were a database. This would avoid starting Excel:

Option Explicit

Randomize
Dim conn, rst, song, album

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\Name\Documents\Music.xlsx;" & _
    "Extended Properties='Excel 12.0 Xml;HDR=NO';"

' Select a random record; reference https://stackoverflow.com/a/9937263/249624
' Asc(album) is just a way to get some numeric value from the existing data
Set rst = conn.Execute("SELECT TOP 1 F1 AS album, F2 as song FROM [Sheet1$] ORDER BY Rnd(-(100000*Asc(F1))*Time())")

If rst.EOF Then
    song = "[NO RECORDS]"
    album = "[NO RECORDS]"
Else
    song = rst("song").Value
    album = rst("album").Value
End If

MsgBox("Album name: " & album & vbNewLine & "Song name: " & song)

The one possible snag here is that VBScript is run by default using the 64-bit version of wscript.exe, and the 64-bit ACE.OLEDB is only available if you installed the 64-bit version of Office 2010 or higher. This can be worked around, though, by running the script with the 32-bit version of wscript.exe (e.g., see How do I run a VBScript in 32-bit mode on a 64-bit machine?).

If you decide to go this route and can control the input Excel file, I would recommend adding a header row to the spreadsheet and changing HDR=NO to HDR=YES in the connection string. That way, you can refer to the columns by name in the query (e.g., SELECT TOP 1 album, song ...) instead of relying on the "F1" syntax.

Answer 2

The most time-consuming steps in your script are most likely

• starting Excel and
• opening the workbook.

One thing you could do is using an already running Excel instance instead of creating a new one all the time:

quitExcel = False

On Error Resume Next
Set objApp = GetObject(, "Excel.Application")
If Err Then
  Set objApp = CreateObject(, "Excel.Application")
  quitExcel  = True
End If
On Error Goto 0

The variable quitExcel indicates whether you need to close Excel at the end of your script (when you created a new instance) or not (when you used an already running instance).

You could also check if the workbook is already open:

wbOpen = False
For Each wb In objWbs
  If wb.Name = "Music.xlsx" Then
    Set objWorkbook = wb
    wbOpen = True
    Exit For
  End If
Next

If Not wbOpen Then
  Set objWorkbook = objWbs.Open("C:\Users\Name\Documents\Music.xlsx")
End If

Other than that your only options are changing the way the data is stored or buying faster hardware, AFAICS.

Answer 3

Cheran, I disagree with the answers here.

I just ran your script on my 5 year old laptop, and got the answer in about 2 seconds. Whether an instance of Excel was already open made no difference in run time.

(I created a test Music.xlsx spreadsheet by entering "A1" in cell A1, and "B1" in cell B1, and dragged those cells down to row 195 to get a nice set of unique sample data).

Why don't you make Excel visible when it runs, so that you can see for yourself what is going on?

You might see, for example, that Excel takes one second to open, and the Excel Add-ins you have are taking the other fifteen seconds to initialize. It's also possible that your machine and/or hard drive is slow and does indeed take 20 seconds to run this. Who knows...

To get some insight, please make objApp.Visible = True and rerun.

You might also comment out the final eight lines, except for the MsgBox line so that your Excel file stays open after script is done, so that you might see other clues.

Other observations: 1) Your method of opening Excel with CreateObject from a .vbs script seems to be the most reliable/accepted method of automating Excel.

2) It's not stated here HOW you are running the .vbs script (command line vs. double-click from Explorer). Your script is running, but be aware that using cscript.exe to run the .vbs is also common when people try to automate this.

3) I'm not used to seeing an external vbs interact with the data inside Excel...I'm used to having vbs open Excel.xlsm, then letting a Macro do the number crunching. But, Macros bring an entirely different set of headaches. I'm not saying your method is good or bad...just not used to that approach.

Good luck!