Excel Sheet Names - How to retrieve

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
djs
Junior Coder
Posts: 47
Joined: Sun Apr 29, 2012 4:21 pm

Excel Sheet Names - How to retrieve

Post by djs » Sun Oct 06, 2013 11:51 pm

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
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Post by hagchr » Mon Oct 07, 2013 7:30 am

Hi, I think you need to change

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

djs
Junior Coder
Posts: 47
Joined: Sun Apr 29, 2012 4:21 pm

Post by djs » Mon Oct 07, 2013 2:19 pm

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

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts