Open Excel workbook from Access

[Opal]

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.

 

[Jim Cone]

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.

 

[Opal]

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?

 

[Jim Cone]

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?

 

[Opal]

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.