Excel Sheet Names - How to retrieve

[djs]

I have an application in MacroScript that automates reading a spreadsheet, puts a bunch of stuff into a browser, lets the backend of the browser do some calculations then extracts info out of the browser and puts it back into a new spreadsheet. Macroscript works well in doing this.

But.. and there is always a but.....

I'm trying to allow the operator to select an excel file to read initially and then have it go unattended. I need to be able to read the sheet names from the file that is selected, which I'm having difficulty with. Marcus posted a very helpful, almost gets me there, solution a while back, which gets the sheet names for an open book. That works well. I've tried to extend this to passing it a variable for a file name, what I've learned is I don't know visual basic very well

I'm hoping someone can point me to where I'm going wrong. Here is the non-functioning code:

Code: Select all

VBSTART
Dim xlApp
Dim xlBook
Sub GetXL(xl)
  Dim xlFileb
  Set xlApp = GetObject(,"Excel.Application")
  Set xlBook = xlApp.Workbooks.Open(xl, 0, True)
End Sub
Function SheetNames
    Dim sList
    sList = ""
    For i = 1 To xlBook.Sheets.Count
        sList = sList & xlBook.Sheets(i).Name & vbCRLF
    Next
    SheetNames = sList
End Function
VBEND
//VBRun>GetXL
Let>xlFile=C:\myspreadsheet.xlsx
XLOpen>%xlFile%,0,xlBook
VBRun>GetXL(xlFile)
VBEval>SheetNames,sSheetList
MessageModal>sSheetList
XLQuit>xlBook

If you run this, the GetXL routine doesn't get a xlbook. If I change it so it has a hardcoded file in GetXL, then it seems to work. This seems to function:

Code: Select all

VBSTART
Dim xlApp
Dim xlBook
Sub GetXL
  Dim xlFileb
  Set xlApp = GetObject(,"Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("C:\myspreadsheet.xlsx", 0, True)
End Sub
Function SheetNames
    Dim sList
    sList = ""
    For i = 1 To xlBook.Sheets.Count
        sList = sList & xlBook.Sheets(i).Name & vbCRLF
    Next
    SheetNames = sList
End Function
VBEND
//VBRun>GetXL
Let>xlFile=C:\myspreadsheet.xlsx
//XLOpen>%xlFile%,0,xlBook
XLOpen>%xlFile%,0,xlBook
VBRun>GetXL
VBEval>SheetNames,sSheetList
MessageModal>sSheetList
XLQuit>xlBook

Notice that in this version, I don't pass anything to GetXL, it just uses what is hardcoded. Unfortunately, this defeats the purpose of what I'm trying to do.

My goal here is to not have Excel open visibly. Not super critical, but I would prefer it to not open. This seems to work since I run XLOpen with it not visible, because, I think, the GetXL routine uses the open book in excel. Problem is, I want to make sure I control which book I get the sheets from, which specifying the path seems to do.

Dan

[/code]

[hagchr]

Hi, I think you need to change

VBRun>GetXL(xlFile)
to
VBRun>GetXL,xlFile

[djs]

This seems to work.
I'll do a bit more testing and then I'll post the corrected code in case someone else needs it in the future.

In my tests so far, it seems to work to get the correct sheet names, even if Excel is open on a different workbook entirely. It also appears not to leave Excel open, which is important.

Dan