O
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.
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.