Referencing an Excel Workbook

[palebluestar]

Hi

I am trying to make reference to an already opened workbook and its various sheets therein.

I am aware that there are functions for Adding and Opening excel files within VBS, but is there a method that I can use on the following statement to reference an already opened workbook?

Set = xlBook.Workbooks.????????

Thanks in advance for any help at all

[support]

Hi,

Something like:

Set xlbook = xlApp.Workbooks(1)

or

Set xlbook = xlApp.ActiveWorkbook

Also, use GetObject to set xlApp if the Excel app is already running when you start.

[palebluestar]

Hi there,

Thanks for that, I tried it but it's still not working correctly; I get the following runtime error;

ActiveX component cannot create object 'GetObject'

Here is the code;

VBSTART
Public Sub Declarations
Dim xlApp
Dim xlBook
Dim xlSheet
End Sub

Function DailySheet

Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.ActiveWorkbooks
Set xlSheet = xlBook.Worksheets("Sheet2")

End Function

VBEND

Am I correct in the above code??
When I use CreateObject that line is ok but the line after isn't.
Also I would like to refrain from using CreateObject as I am going to be making several refences to the function and similar other functions within the macro, I am therefore concerned about how many instances it is going to create.

This is, as you can probably tell, my first foray into VBS.

Please help

[support]

Hi,

You need to make your variables global and do not need the first subroutine which will only Dim them locally to itself anyway. Your GetObject line appears to be wrong too.

VBSTART
Dim xlApp
Dim xlBook
Dim xlSheet

Function DailySheet

Set xlApp = GetObject("","Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.ActiveWorkbooks
Set xlSheet = xlBook.Worksheets("Sheet2")

End Function

VBEND


This will only work if the existing opened Excel instance is automateable). If the user opened Excel I don't think this will work and it will instead create a new instance (which will therefore cause an error on the ActiveWorkbooks line as no workbook will exist). You're probably better off using CreateObject and opening the workbook you want to access/modify.