Help handling errors associated multiple instances of Excel

J

jslomka

New Member
Joined
Sep 20, 2006
Messages
22
I'm using Excel 2003 SP2 & Access SP2 on a Windows XP machine.
Our problem is when a user has an instance of Excel open prior to running our process we get error 9 "Subscript out of range" when the code tries to move the "FAR Removal Trend.xls" sheet to our template. Our process is as follows, we send the data from Access using a DoCmd.OutputTo method (the first set of code). We then open the template and the code in the template (second set of code) moves the "FAR Removal Trend.xls" worksheet into the template were we then process the data. Everthing works fine when there are no instances of Excel open prior to starting the process. However when an instance of Excel is already open the "FAR Removal Trend.xls" is opened in one instance and the template is opened in another instance. The code in the template can not see the "FAR Removal Trend.xls" workbook and sets error 9. I have been trying to figure out if I can use API (FindWindowEx in the user32 lib) to resolve this issue. No luck so far. Any help would be greatly appreciated.

Code: 
Private Sub Trend_Curve_Click()

Dim xlApp As Object
Dim strDefaultDir As String
Set xlApp = CreateObject("Excel.Application")
strDefaultDir = Application.GetOption("Default Database Directory")
strDefaultDir = strDefaultDir & "\FAR Removal Trend.xls"
On Error Resume Next
Kill (strDefaultDir)
On Error GoTo Error_Handler
If MTBF_PLOT = 0 And MTBR_PLOT = 0 Then
    MsgBox "Type of plot not selected, please select MTBF and/or MTBR plot.", vbCritical, "Error"
Else
    DoCmd.OutputTo acQuery, "FAR Removal Trend", "MicrosoftExcel03(*.xls)", "", True, ""
 
    Set xlApp = GetObject(, "Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Add Template:= _
    "\\MARNV005\Rms-C130J\Apps\APPS\FRACAS\Templates\060927_Universal Trend Template.xlt"
    xlApp.Run "'060927_Universal Trend Template1'!Universal_Trend.Universal_Trend"
    
 End If
 
Exit_Procedure:
    Exit Sub

Error_Handler:
    If Err.Number = 2501 Or 440 Then
        Resume Exit_Procedure
    ElseIf Err.Number = 2302 Then
    MsgBox "An error has occurred in this application. " _
        & vbCrLf & vbCrLf & "Please close the Excel file before running the " _
        & vbCrLf & "requested trend chart.", _
        Buttons:=vbCritical, TITLE:="DMT Error"
    Else
        MsgBox "An error has occurred in this application. " _
        & "Please contact your technical support person and " _
        & "tell them this information:" _
        & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
        & Err.DESCRIPTION, _
        Buttons:=vbCritical, TITLE:="DMT Error"
        Resume Exit_Procedure
        Resume
    End If
    Error [(errornumber)]
End Sub

Code: 
'===============================================
'Moves Query - Trend data to template workbook
'===============================================
    Application.ScreenUpdating = False
    On Error GoTo Error_Handler
    Windows("FAR Removal Trend.xls").Activate
    Sheets("FAR Removal Trend").Move After:=Workbooks( _
        "060927_Universal Trend Template1").Sheets("Pie Chart Data")

Error_Handler:
        If Err.Number = 9 Then
            MsgBox "An error has occurred in this application. " _
            & vbCrLf & vbCrLf & "Please close all instances of Excel before running " _
            & vbCrLf & "the requested trend chart.", _
            Buttons:=vbCritical, Title:="DMT Error"
            Application.DisplayAlerts = False
            ActiveWindow.Close
            Application.DisplayAlerts = True
            Exit Sub
        Else
            MsgBox "An error has occurred in this application. " _
            & "Please contact your technical support person and " _
            & "tell them this information:" _
            & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
            & Err.Description, _
            Buttons:=vbCritical, Title:="DMT Error"
            Resume Exit_Procedure
            Resume
        End If
 
Sort by date Sort by votes
Thanks Norie & Andrew, I will try to modify the code to first use the GetObject and trap any errors and use the CreateObject if no instances of Excel are open. I will let you know if this works.

Regards
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I modified the code with the GetObject method and that works when only one instance of Excel is open prior to running the process(improvment). However, if more than one instance of Excels is open I get the "Subscript out of range" error at the same point as before
Code: 
Windows("FAR Removal Trend.xls").Activate

I'm not really sure how to address this issue. I do not know how to cycle thru the multiple instances to look for the specific workbook. I would like to use
Code: 
Set xlApp = GetObject("FAR Removal Trend.xls").Application
as explained in the previously referenced Microsoft article.

Thanks in advance

Code: 
Private Sub Trend_Curve_Click()

Dim xlApp As Object
Dim strDefaultDir As String
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If Err.Number = 429 Then
    Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo Error_Handler
strDefaultDir = Application.GetOption("Default Database Directory")
strDefaultDir = strDefaultDir & "\FAR Removal Trend.xls"
On Error Resume Next
Kill (strDefaultDir)
On Error GoTo Error_Handler
If MTBF_PLOT = 0 And MTBR_PLOT = 0 Then
    MsgBox "Type of plot not selected, please select MTBF and/or MTBR plot.", vbCritical, "Error"
Else
    DoCmd.OutputTo acQuery, "FAR Removal Trend", "MicrosoftExcel03(*.xls)", "", True, ""
    Set xlApp = GetObject(, "Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Add Template:= _
    "\\MARNV005\Rms-C130J\Apps\APPS\FRACAS\Templates\060927_Universal Trend Template.xlt"
    xlApp.Run "'060927_Universal Trend Template1'!Universal_Trend.Universal_Trend"
    
 End If
 
Exit_Procedure:
    Exit Sub

Error_Handler:
    If Err.Number = 2501 Or 440 Then
        Resume Exit_Procedure
    ElseIf Err.Number = 2302 Then
    MsgBox "An error has occurred in this application. " _
        & vbCrLf & vbCrLf & "Please close the Excel file before running the " _
        & vbCrLf & "requested trend chart.", _
        Buttons:=vbCritical, TITLE:="DMT Error"
    Else
        MsgBox "An error has occurred in this application. " _
        & "Please contact your technical support person and " _
        & "tell them this information:" _
        & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
        & Err.DESCRIPTION, _
        Buttons:=vbCritical, TITLE:="DMT Error"
        Resume Exit_Procedure
        Resume
    End If
    Error [(errornumber)]
End Sub
 
Upvote 0
jslomka,


I do not know how to cycle thru the multiple instances to look for the specific workbook. I would like to use.
Click to expand...

I haven't checked your code properly but GetObject Function will not work if you have more than one XL instance unless you pass to it the fullpathName of the specific workbook you want to access in code.

Regards.
 
Upvote 0
Thanks, I was just trying that approach
Code: 
Set xlApp = GetObject("d:\data\gx02770\My Documents\Reliability Analysis\FAR Removal Trend.xls")
However I keep getting "Object doesn't support this property or method" when the next line of code tries to execute
Code: 
xlApp.Visible = True
I have xlApp dimensioned as an object. Any suggestions?
 
Upvote 0
You actually returning the WorkBook Object NOT the application.

You will need to access the XL Application via the WorkBook Parent Property.

Try this :

Code: 
Dim oWBK As Object

Set oWBK = GetObject("d:\data\gx02770\My Documents\Reliability Analysis\FAR Removal Trend.xls")


oWBK.Parent.Visible = True

Regards.
 
Upvote 0
I tried the code
Code: 
Dim oWBK As Object

Set oWBK = GetObject("d:\data\gx02770\My Documents\Reliability Analysis\FAR Removal Trend.xls")
oWBK.Parent.Visible = True

oWBK.Workbooks.Add Template:= _
    "\\MARNV005\Rms-C130J\Apps\APPS\FRACAS\Templates\060927_Universal Trend Template.xlt"
oWBK.Run "'060927_Universal Trend Template1'!Universal_Trend.Universal_Trend"
I now get "Object doesn't support this property or method" when trying to execute the next line.
Code: 
oWBK.Workbooks.Add Template:= _
    "\\MARNV005\Rms-C130J\Apps\APPS\FRACAS\Templates\060927_Universal Trend Template.xlt"
The code does not recognize oWBK as an XL workbook. Do I need to dimension differently?
 
Upvote 0
The reason is because oWBK is a workbook object as Jaafar pointed out.

Did you see his comments regarding using Parent?
 
Upvote 0
Thanks to all

Thanks to all, the light finally came on and I now understand the concept of the Parent of an object. The program now works as desired. I have included the final code below. Thanks again.
Code: 
Private Sub Trend_Curve_Click()

Dim xlApp As Object
Dim strDefaultDir As String
Dim oWBK As Object
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If Err.Number = 429 Then
    Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo Error_Handler
strDefaultDir = Application.GetOption("Default Database Directory")
strDefaultDir = strDefaultDir & "\FAR Removal Trend.xls"
On Error Resume Next
Kill (strDefaultDir)
On Error GoTo Error_Handler
If MTBF_PLOT = 0 And MTBR_PLOT = 0 Then
    MsgBox "Type of plot not selected, please select MTBF and/or MTBR plot.", vbCritical, "Error"
Else
    DoCmd.OutputTo acQuery, "FAR Removal Trend", "MicrosoftExcel03(*.xls)", "", True, ""

Set oWBK = GetObject(strDefaultDir)
oWBK.Parent.Visible = True
    
oWBK.Parent.Workbooks.Add Template:= _
"\\MARNV005\Rms-C130J\Apps\APPS\FRACAS\Templates\060927_Universal Trend Template.xlt"
oWBK.Parent.Run "'060927_Universal Trend Template1'!Universal_Trend.Universal_Trend"
        
End If
 
Exit_Procedure:
    Exit Sub

Error_Handler:
    If Err.Number = 2501 Or 440 Then
        Resume Exit_Procedure
    ElseIf Err.Number = 2302 Then
    MsgBox "An error has occurred in this application. " _
        & vbCrLf & vbCrLf & "Please close the Excel file before running the " _
        & vbCrLf & "requested trend chart.", _
        Buttons:=vbCritical, TITLE:="DMT Error"
    Else
        MsgBox "An error has occurred in this application. " _
        & "Please contact your technical support person and " _
        & "tell them this information:" _
        & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
        & Err.DESCRIPTION, _
        Buttons:=vbCritical, TITLE:="DMT Error"
        Resume Exit_Procedure
        Resume
    End If
    Error [(errornumber)]
End Sub
Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
VBA code giving error on line .Wrap = wdFindContinue
Replies
2
Views
439
NdNoviceHlp
NdNoviceHlp
F
  • Question
VBA run code based off cell value
Replies
1
Views
358
igold
igold
N
  • Solved
Userform populated w/ word information
Replies
1
Views
361
NdNoviceHlp
NdNoviceHlp
N
  • Question
Delete bookmarks using userform (code is not working as expected)
Replies
6
Views
446
NdNoviceHlp
NdNoviceHlp
S
  • Solved
Miscrosoft has Blocking The Macro Sometimes and disabling the macros
Replies
5
Views
429
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,215,634
Messages
6,125,934
Members
449,274
Latest member
mrcsbenson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top