Reference the active sheet  
Author Message
ewumstory





PostPosted: Thu Oct 21 15:09:53 CDT 2004 Top

Excel Programming >> Reference the active sheet

If I run the following code and I have an open spreadsheet with existing
sheets, I get another instance of Excel w/no sheets. How can I reference
the active spreadsheet(w/o knowing the filename?)

Dim oXL as object
Dim xlWorksheet as object

Set oXL = GetObject("", "Excel.Application")
Set xlWorkSheet = oXL.Worksheets.Add

*** Sent via Developersdex http://www.hide-link.com/ ***
Don't just participate in USENET...get rewarded for it!

Excel66  
 
 
Chip





PostPosted: Thu Oct 21 15:09:53 CDT 2004 Top

Excel Programming >> Reference the active sheet If you don't know the filename, you cannot control with instance
of Excel GetObject is going to return.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





> If I run the following code and I have an open spreadsheet with
> existing
> sheets, I get another instance of Excel w/no sheets. How can I
> reference
> the active spreadsheet(w/o knowing the filename?)
>
> Dim oXL as object
> Dim xlWorksheet as object
>
> Set oXL = GetObject("", "Excel.Application")
> Set xlWorkSheet = oXL.Worksheets.Add
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
James





PostPosted: Fri Oct 22 11:19:39 CDT 2004 Top

Excel Programming >> Reference the active sheet Then perhaps I shouldn't be running GetObject at all? I just need a
reference (from Word VBA) to the active Excel document, without knowing
its name. Is this possible?

 
 
Jimmy





PostPosted: Fri Oct 22 11:41:05 CDT 2004 Top

Excel Programming >> Reference the active sheet Then perhaps I shouldn't be running GetObject at all? I just need a
reference (from Word VBA) to the active Excel document, without knowing
its name. Is this possible?




> If you don't know the filename, you cannot control with instance of Excel
> GetObject is going to return.
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>


>> If I run the following code and I have an open spreadsheet with existing
>> sheets, I get another instance of Excel w/no sheets. How can I reference
>> the active spreadsheet(w/o knowing the filename?)
>>
>> Dim oXL as object
>> Dim xlWorksheet as object
>>
>> Set oXL = GetObject("", "Excel.Application")
>> Set xlWorkSheet = oXL.Worksheets.Add
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!
>
>


 
 
Chip





PostPosted: Fri Oct 22 11:52:43 CDT 2004 Top

Excel Programming >> Reference the active sheet Jimmy,

GetObject is used to get a reference to an existing running
instance of Excel. It will fail and return Nothing if there is no
running instance of Excel. If there is more than one instance
running, you have no control over which instance to which you
will get a reference. Perhaps code like the following will work:

Dim XL As Excel.Application
Dim WB As Excel.Workbook
On Error Resume Next
Set XL = GetObject(,"Excel.Application")
If XL Is Nothing Then
Set XL = CreateObject("Excel.Application")
End If
If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com







> Then perhaps I shouldn't be running GetObject at all? I just
> need a
> reference (from Word VBA) to the active Excel document, without
> knowing
> its name. Is this possible?
>
>


>> If you don't know the filename, you cannot control with
>> instance of Excel GetObject is going to return.
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>


>>> If I run the following code and I have an open spreadsheet
>>> with existing
>>> sheets, I get another instance of Excel w/no sheets. How can
>>> I reference
>>> the active spreadsheet(w/o knowing the filename?)
>>>
>>> Dim oXL as object
>>> Dim xlWorksheet as object
>>>
>>> Set oXL = GetObject("", "Excel.Application")
>>> Set xlWorkSheet = oXL.Worksheets.Add
>>>
>>> *** Sent via Developersdex http://www.developersdex.com ***
>>> Don't just participate in USENET...get rewarded for it!
>>
>>
>
>


 
 
Jimmy





PostPosted: Fri Oct 22 12:02:30 CDT 2004 Top

Excel Programming >> Reference the active sheet If I know only one document is running, will
Set XL = GetObject(,"Excel.Application")
return a reference to that doc?


> Jimmy,
>
> GetObject is used to get a reference to an existing running instance of
> Excel. It will fail and return Nothing if there is no running instance of
> Excel. If there is more than one instance running, you have no control
> over which instance to which you will get a reference. Perhaps code like
> the following will work:
>
> Dim XL As Excel.Application
> Dim WB As Excel.Workbook
> On Error Resume Next
> Set XL = GetObject(,"Excel.Application")
> If XL Is Nothing Then
> Set XL = CreateObject("Excel.Application")
> End If
> If Not XL.ActiveWorkbook Is Nothing Then
> Set WB = XL.ActiveWorkbook
> End If
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>


>> Then perhaps I shouldn't be running GetObject at all? I just need a
>> reference (from Word VBA) to the active Excel document, without knowing
>> its name. Is this possible?
>>
>>


>>> If you don't know the filename, you cannot control with instance of
>>> Excel GetObject is going to return.
>>>
>>>
>>>
>>> --
>>> Cordially,
>>> Chip Pearson
>>> Microsoft MVP - Excel
>>> Pearson Software Consulting, LLC
>>> www.cpearson.com
>>>
>>>
>>>


>>>> If I run the following code and I have an open spreadsheet with
>>>> existing
>>>> sheets, I get another instance of Excel w/no sheets. How can I
>>>> reference
>>>> the active spreadsheet(w/o knowing the filename?)
>>>>
>>>> Dim oXL as object
>>>> Dim xlWorksheet as object
>>>>
>>>> Set oXL = GetObject("", "Excel.Application")
>>>> Set xlWorkSheet = oXL.Worksheets.Add
>>>>
>>>> *** Sent via Developersdex http://www.developersdex.com ***
>>>> Don't just participate in USENET...get rewarded for it!
>>>
>>>
>>
>>
>
>


 
 
Chip





PostPosted: Fri Oct 22 12:05:54 CDT 2004 Top

Excel Programming >> Reference the active sheet Jimmy,

No, GetObject will not return a reference to the active workbook.
It returns a reference to the Excel Application. Look at the code
I posted. The variable WB is set to the active workbook.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





> If I know only one document is running, will
> Set XL = GetObject(,"Excel.Application")
> return a reference to that doc?


>> Jimmy,
>>
>> GetObject is used to get a reference to an existing running
>> instance of Excel. It will fail and return Nothing if there is
>> no running instance of Excel. If there is more than one
>> instance running, you have no control over which instance to
>> which you will get a reference. Perhaps code like the
>> following will work:
>>
>> Dim XL As Excel.Application
>> Dim WB As Excel.Workbook
>> On Error Resume Next
>> Set XL = GetObject(,"Excel.Application")
>> If XL Is Nothing Then
>> Set XL = CreateObject("Excel.Application")
>> End If
>> If Not XL.ActiveWorkbook Is Nothing Then
>> Set WB = XL.ActiveWorkbook
>> End If
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>>


>>> Then perhaps I shouldn't be running GetObject at all? I just
>>> need a
>>> reference (from Word VBA) to the active Excel document,
>>> without knowing
>>> its name. Is this possible?
>>>
>>>


>>>> If you don't know the filename, you cannot control with
>>>> instance of Excel GetObject is going to return.
>>>>
>>>>
>>>>
>>>> --
>>>> Cordially,
>>>> Chip Pearson
>>>> Microsoft MVP - Excel
>>>> Pearson Software Consulting, LLC
>>>> www.cpearson.com
>>>>
>>>>
>>>>


>>>>> If I run the following code and I have an open spreadsheet
>>>>> with existing
>>>>> sheets, I get another instance of Excel w/no sheets. How
>>>>> can I reference
>>>>> the active spreadsheet(w/o knowing the filename?)
>>>>>
>>>>> Dim oXL as object
>>>>> Dim xlWorksheet as object
>>>>>
>>>>> Set oXL = GetObject("", "Excel.Application")
>>>>> Set xlWorkSheet = oXL.Worksheets.Add
>>>>>
>>>>> *** Sent via Developersdex http://www.developersdex.com ***
>>>>> Don't just participate in USENET...get rewarded for it!
>>>>
>>>>
>>>
>>>
>>
>>
>
>


 
 
Jimmy





PostPosted: Fri Oct 22 12:10:28 CDT 2004 Top

Excel Programming >> Reference the active sheet **** a dead horse here, if I have 7 excel workbooks open but only 1 is
active, there is no way from Word VBA that I can get a reference to that
active workbook/sheet if I don't have the name?


> Jimmy,
>
> GetObject is used to get a reference to an existing running instance of
> Excel. It will fail and return Nothing if there is no running instance of
> Excel. If there is more than one instance running, you have no control
> over which instance to which you will get a reference. Perhaps code like
> the following will work:
>
> Dim XL As Excel.Application
> Dim WB As Excel.Workbook
> On Error Resume Next
> Set XL = GetObject(,"Excel.Application")
> If XL Is Nothing Then
> Set XL = CreateObject("Excel.Application")
> End If
> If Not XL.ActiveWorkbook Is Nothing Then
> Set WB = XL.ActiveWorkbook
> End If
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>


>> Then perhaps I shouldn't be running GetObject at all? I just need a
>> reference (from Word VBA) to the active Excel document, without knowing
>> its name. Is this possible?
>>
>>


>>> If you don't know the filename, you cannot control with instance of
>>> Excel GetObject is going to return.
>>>
>>>
>>>
>>> --
>>> Cordially,
>>> Chip Pearson
>>> Microsoft MVP - Excel
>>> Pearson Software Consulting, LLC
>>> www.cpearson.com
>>>
>>>
>>>


>>>> If I run the following code and I have an open spreadsheet with
>>>> existing
>>>> sheets, I get another instance of Excel w/no sheets. How can I
>>>> reference
>>>> the active spreadsheet(w/o knowing the filename?)
>>>>
>>>> Dim oXL as object
>>>> Dim xlWorksheet as object
>>>>
>>>> Set oXL = GetObject("", "Excel.Application")
>>>> Set xlWorkSheet = oXL.Worksheets.Add
>>>>
>>>> *** Sent via Developersdex http://www.hide-link.com/ ***
>>>> Don't just participate in USENET...get rewarded for it!
>>>
>>>
>>
>>
>
>


 
 
Chip





PostPosted: Fri Oct 22 12:16:03 CDT 2004 Top

Excel Programming >> Reference the active sheet As shown in the code I posted, you can get the ActiveWorkbook
with code like

Set WB = XL.ActiveWorkbook


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




> **** a dead horse here, if I have 7 excel workbooks open
> but only 1 is active, there is no way from Word VBA that I can
> get a reference to that active workbook/sheet if I don't have
> the name?


>> Jimmy,
>>
>> GetObject is used to get a reference to an existing running
>> instance of Excel. It will fail and return Nothing if there is
>> no running instance of Excel. If there is more than one
>> instance running, you have no control over which instance to
>> which you will get a reference. Perhaps code like the
>> following will work:
>>
>> Dim XL As Excel.Application
>> Dim WB As Excel.Workbook
>> On Error Resume Next
>> Set XL = GetObject(,"Excel.Application")
>> If XL Is Nothing Then
>> Set XL = CreateObject("Excel.Application")
>> End If
>> If Not XL.ActiveWorkbook Is Nothing Then
>> Set WB = XL.ActiveWorkbook
>> End If
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>>


>>> Then perhaps I shouldn't be running GetObject at all? I just
>>> need a
>>> reference (from Word VBA) to the active Excel document,
>>> without knowing
>>> its name. Is this possible?
>>>
>>>


>>>> If you don't know the filename, you cannot control with
>>>> instance of Excel GetObject is going to return.
>>>>
>>>>
>>>>
>>>> --
>>>> Cordially,
>>>> Chip Pearson
>>>> Microsoft MVP - Excel
>>>> Pearson Software Consulting, LLC
>>>> www.cpearson.com
>>>>
>>>>
>>>>


>>>>> If I run the following code and I have an open spreadsheet
>>>>> with existing
>>>>> sheets, I get another instance of Excel w/no sheets. How
>>>>> can I reference
>>>>> the active spreadsheet(w/o knowing the filename?)
>>>>>
>>>>> Dim oXL as object
>>>>> Dim xlWorksheet as object
>>>>>
>>>>> Set oXL = GetObject("", "Excel.Application")
>>>>> Set xlWorkSheet = oXL.Worksheets.Add
>>>>>
>>>>> *** Sent via Developersdex http://www.hide-link.com/ ***
>>>>> Don't just participate in USENET...get rewarded for it!
>>>>
>>>>
>>>
>>>
>>
>>
>
>


 
 
Dave





PostPosted: Fri Oct 22 17:01:49 CDT 2004 Top

Excel Programming >> Reference the active sheet Just to jump in to muddy the waters!

Add a line to this portion of Chip's code:

If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
End If

make it look like:

If Not XL.ActiveWorkbook Is Nothing Then
Set WB = XL.ActiveWorkbook
msbox wb.name
End If

And you'll see WB is what you want.



>
> **** a dead horse here, if I have 7 excel workbooks open but only 1 is
> active, there is no way from Word VBA that I can get a reference to that
> active workbook/sheet if I don't have the name?


> > Jimmy,
> >
> > GetObject is used to get a reference to an existing running instance of
> > Excel. It will fail and return Nothing if there is no running instance of
> > Excel. If there is more than one instance running, you have no control
> > over which instance to which you will get a reference. Perhaps code like
> > the following will work:
> >
> > Dim XL As Excel.Application
> > Dim WB As Excel.Workbook
> > On Error Resume Next
> > Set XL = GetObject(,"Excel.Application")
> > If XL Is Nothing Then
> > Set XL = CreateObject("Excel.Application")
> > End If
> > If Not XL.ActiveWorkbook Is Nothing Then
> > Set WB = XL.ActiveWorkbook
> > End If
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> >
> >


> >> Then perhaps I shouldn't be running GetObject at all? I just need a
> >> reference (from Word VBA) to the active Excel document, without knowing
> >> its name. Is this possible?
> >>
> >>


> >>> If you don't know the filename, you cannot control with instance of
> >>> Excel GetObject is going to return.
> >>>
> >>>
> >>>
> >>> --
> >>> Cordially,
> >>> Chip Pearson
> >>> Microsoft MVP - Excel
> >>> Pearson Software Consulting, LLC
> >>> www.cpearson.com
> >>>
> >>>
> >>>


> >>>> If I run the following code and I have an open spreadsheet with
> >>>> existing
> >>>> sheets, I get another instance of Excel w/no sheets. How can I
> >>>> reference
> >>>> the active spreadsheet(w/o knowing the filename?)
> >>>>
> >>>> Dim oXL as object
> >>>> Dim xlWorksheet as object
> >>>>
> >>>> Set oXL = GetObject("", "Excel.Application")
> >>>> Set xlWorkSheet = oXL.Worksheets.Add
> >>>>
> >>>> *** Sent via Developersdex http://www.hide-link.com/ ***
> >>>> Don't just participate in USENET...get rewarded for it!
> >>>
> >>>
> >>
> >>
> >
> >

--

Dave Peterson