Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Excel workbook from Access
I am trying to automate the opening of an excel workbook from by
access database - both 2003. I am using the following but am running into a problem when excel is not running: Sub ExcelOpen() Dim xlApp As Excel.Application Dim xlWkb As Excel.Workbook Dim xlWsh As Excel.Worksheet On Error Resume Next ' reference open session of excel Set xlApp = GetObject(, "excel.application") If Err.Number < 0 Then ' excel not already running Err.Clear On Error GoTo 0 Set xlApp = New Excel.Application End If Set xlWkb = xlApp.Workbooks.Open("C:\My Documents\2008 TRACKING\RPSReporting (Open-Closed-Targets).xls") Set xlWsh = xlWkb.Worksheets("Main") With xlApp If Not .UserControl Then ' opened excel using code ..Quit End If End With Set xlWkb = Nothing Set xlApp = Nothing End Sub If excel is running, it opens the workbook without a problem, but if excel is not running it opens and closes the workbook. I know it must be this part of the code: With xlApp If Not .UserControl Then ' opened excel using code ..Quit End If End With but how do I get it not to close? I am new to this type of coding and not sure how to get around this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Excel workbook from Access
Some suggestions...
Add two lines as follows... Set xlApp = New Excel.Application xlApp.Visible = True '<<<< End If On Error GoTo 0 '<<<< '-- Comment out the usercontrol portion... 'With xlApp 'If Not .UserControl Then ' opened excel using code '.Quit 'End If 'End With '-- Set the worksheet reference to Nothing... Set xlWsh = Nothing Set xlWkb = Nothing Set xlApp = Nothing -- Jim Cone Portland, Oregon USA "Opal" wrote in message I am trying to automate the opening of an excel workbook from by access database - both 2003. I am using the following but am running into a problem when excel is not running: Sub ExcelOpen() Dim xlApp As Excel.Application Dim xlWkb As Excel.Workbook Dim xlWsh As Excel.Worksheet On Error Resume Next ' reference open session of excel Set xlApp = GetObject(, "excel.application") If Err.Number < 0 Then ' excel not already running Err.Clear On Error GoTo 0 Set xlApp = New Excel.Application End If Set xlWkb = xlApp.Workbooks.Open("C:\My Documents\2008 TRACKING\RPSReporting (Open-Closed-Targets).xls") Set xlWsh = xlWkb.Worksheets("Main") With xlApp If Not .UserControl Then ' opened excel using code ..Quit End If End With Set xlWkb = Nothing Set xlApp = Nothing End Sub If excel is running, it opens the workbook without a problem, but if excel is not running it opens and closes the workbook. I know it must be this part of the code: With xlApp If Not .UserControl Then ' opened excel using code ..Quit End If End With but how do I get it not to close? I am new to this type of coding and not sure how to get around this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Excel workbook from Access
Thank you Jim, that is very helpful. One more question, I thought the
line: Set xlWsh = xlWkb.Worksheets("Main") would open the workbook on the tab labeled Main, but I have been testing it and its not. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Excel workbook from Access
The Set statement establishes a reference to the sheet.
You can then use that reference in your code. If you want to display the sheet then try... xlWsh.Activate -- Jim Cone Portland, Oregon USA "Opal" wrote in message .... Thank you Jim, that is very helpful. One more question, I thought the line: Set xlWsh = xlWkb.Worksheets("Main") would open the workbook on the tab labeled Main, but I have been testing it and its not. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Excel workbook from Access
On Nov 5, 12:26*pm, "Jim Cone" wrote:
The Set statement establishes a reference to the sheet. You can then use that reference in your code. If you want to display the sheet then try... xlWsh.Activate -- Jim Cone Portland, Oregon *USA "Opal" wrote in message ... Thank you Jim, that is very helpful. *One more question, I thought the line: Set xlWsh = xlWkb.Worksheets("Main") would open the workbook on the tab labeled Main, but I have been testing it and its not. *Any suggestions? Thank you, that's very helpful. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
Access Data from Workbook without seeing the workbook open. | Excel Programming | |||
Open a specific workbook...find value from other open workbook and then insert cells values in cell next to it. | Excel Programming | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Open excel workbook in access | Excel Programming |