VBScript to read Excel with vars

[pgriffin]

I have read everything I can find on this forum and others and am stumped for now.

I need to read an Excel file, Sheet, Row, and Column and return the result to MacroScheduler without using DDE (just looks messy to the end user)....I need to send the File, Sheet, Row, Column as variables to a VBScript function.....

I think I am close, but can't quite get it right.


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(1)

xlApp.visible = False

Set TestVal = xlPage.Cells(xlRow, xlColumn).Value

xlApp.Quit

Set xlPage = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Function

VBEND

let>FileName=c:\work\nodupnoblank.xls
let>xlSheet=1
let>xlRow=6500
let>xlColumn=2
Vbe>XLTest("%FileName%","%xlSheet%","%xlRow%","%xlColumn%"),TestVal
mdl>TestVal

Code: Select all

[Marcus Tettmar]

Numeric values shouldn't be quoted, and to return a value from the function you need do:

function_name = value

Also, Set is only used for creating objects. No need for just assigning a regular value.

Code: Select all

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(1)

  xlApp.visible = False

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

  xlApp.Quit

  Set xlPage = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing

End Function

VBEND

let>FileName=c:\work\nodupnoblank.xls
let>xlSheet=1
let>xlRow=6500
let>xlColumn=2
Vbe>XLTest("%FileName%","%xlSheet%",%xlRow%,%xlColumn%),TestVal
mdl>TestVal

[pgriffin]

Thank you, Marcus! You make it look so easy, I wonder why I bother programming.

but I do enjoy solving the puzzle from time to time.

(and I obviously need learn a little more about VBScript...workin' on it)

[liveitupmore]

I tried to use the code as poted by Marcus, however I need to use different sheets in the workbook. Line 11 of his code is
Set xlPage = xlBook.Worksheets(1)
where (1) is the worksheet.

The VBE call is as follows
Vbe>XLTest("%FileName%","%xlSheet%",%xlRow%,%xlColumn%),TestVal

The xlSheet variable doesnt get used. I have tried to replace the (1) with xlSheet but it fails.

Any suggestions ??

Alistair

[Marcus Tettmar]

That would work. It can be a numeric index or a sheet name. So the code would be:

Code: Select all

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 

  xlApp.Quit 

  Set xlPage = Nothing 
  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Function 

VBEND 

let>FileName=c:\work\nodupnoblank.xls 
let>xlSheet=Sheet1 
let>xlRow=6500 
let>xlColumn=2 
Vbe>XLTest("%FileName%","%xlSheet%",%xlRow%,%xlColumn%),TestVal 
mdl>TestVal

Note that in the above example xlSheet is being passed in quotes, so is being passed as a string. If you were using the numeric index, remove the quotes.