| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.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.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#2
Posted to microsoft.public.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 "joe" wrote in message ... 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! |
|
#3
Posted to microsoft.public.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? |
|
#4
Posted to microsoft.public.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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#5
Posted to microsoft.public.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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#6
Posted to microsoft.public.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? "Chip Pearson" wrote in message ... 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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#7
Posted to microsoft.public.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 "Jimmy" wrote in message ink.net... If I know only one document is running, will Set XL = GetObject(,"Excel.Application") return a reference to that doc? "Chip Pearson" wrote in message ... 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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference the active sheet
Flogging 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? "Chip Pearson" wrote in message ... 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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#9
Posted to microsoft.public.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 "Jimmy" wrote in message ink.net... Flogging 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? "Chip Pearson" wrote in message ... 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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! |
|
#10
Posted to microsoft.public.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. Jimmy wrote: Flogging 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? "Chip Pearson" wrote in message ... 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 "Jimmy" wrote in message ink.net... 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? "Chip Pearson" wrote in message ... 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 "joe" wrote in message ... 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! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using the active cell as a reference in a function | Excel Worksheet Functions | |||
| How to reference an active cell | New Users to Excel | |||
| Active row cell reference | Excel Worksheet Functions | |||
| Active Cell Reference | Excel Worksheet Functions | |||
| Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
Hybrid Mode
