VBScript and Excel

[liveitupmore]

Am I correct in understanding that after you make a call using VBEval to the VB Code that all variables are lost, eg the next time you make the same call you start with an empty set of variables other than those you pass in.

I am trying to script multiple calls to read cell contents from Excel. Each time I call the VBcode it opens the excel file, reads the contents of the cell and then closes the file. This takes 2 secs per read (approx). I need to make 50 reads and that is too slow.

I have alse tried using DDERequest, this is much faster but requires the excel spreadsheet to be open and doesn't seem to be as clean a method as VBEval.

Here is the code ....

VBSTART


Function XLTest(FileName,xlSheet,xlRow,xlColumn)

Dim xlApp
Dim xlBook
Dim xlPage
Dim TestVal

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Filename)
Set xlPage = xlBook.Worksheets(xlSheet)

xlApp.visible = False

XLTest = xlPage.Cells(xlRow, xlColumn).Value

xlBook.Close
xlApp.Quit
Set xlPage = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Function
VBEND

let>FileName=e:\180509.xls
let>xlSheet=5
let>xlRow=3
let>xlColumn=2
Vbe>XLTest("%FileName%",%xlSheet%,%xlRow%,%Column%),TestVal
let>xlRow=4
let>xlColumn=2
Vbe>XLTest("%FileName%",%xlSheet%,%xlRow%,%Column%),TestVal1
let>xlRow=5
let>xlColumn=2
Vbe>XLTest("%FileName%",%xlSheet%,%xlRow%,%Column%),TestVal2

mdl>%TestVal% %TestVal1% %TestVal2%


Any comments??

Alistair

[Marcus Tettmar]

This function, opens Excel, gets the data then closes Excel again.

If you want to do lots of GETs then why not split the function into three. One function opens Excel. The other gets the cells. The other closes Excel:

Code: Select all

VBSTART

Dim xlApp
Dim xlBook

Sub OpenXL(FileName)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = False
  Set xlBook = xlApp.Workbooks.Open(Filename)
End Sub

Function XLTest(xlSheet,xlRow,xlColumn)
  Dim xlPage
  Dim TestVal

  Set xlPage = xlBook.Worksheets(xlSheet)
  XLTest = xlPage.Cells(xlRow, xlColumn).Value
  Set xlPage = Nothing
End Function

Sub CloseXL
  xlBook.Close
  xlApp.Quit
  Set xlBook = Nothing
  Set xlApp = Nothing
End Sub
VBEND

let>FileName=e:\180509.xls
let>xlSheet=5
let>xlRow=3
let>xlColumn=2

VBRun>OpenXL,FileName

VBEval>XLTest(%xlSheet%,%xlRow%,%Column%),TestVal
let>xlRow=4
let>xlColumn=2
VBEval>XLTest(%xlSheet%,%xlRow%,%Column%),TestVal1
let>xlRow=5
let>xlColumn=2
VBEval>XLTest(%xlSheet%,%xlRow%,%Column%),TestVal2

mdl>%TestVal% %TestVal1% %TestVal2%

VBRun>CloseXL

In fact look at the sample script "Excel Functions" which comes with the software - in the General macro group. It does exactly the same thing - it has functions OpenExcelFile, CloseExcel, GetCell and SetCell.