new instance of Excel

[Guest]

I am trying to open a new instance of Excel from within my code and load a
specific workbook that contains macros. My code looks like this:

Private Sub butTBOps_Click()
On Error GoTo butTBOps_Click_Err
Dim stDocName As String
Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim stDocName5 As String
Dim stDocName6 As String

Dim xlApp As New Excel.Application
Dim xlWkb As Excel.Workbook


Select Case OptCo

Case 13504
DoCmd.RunMacro "macel13504 TB"
Set xlWkb = GetObject("g:\Accounting\AR\JSiegel\JSAutomate.xls")
Set xlApp = xlWkb.Parent

xlApp.Visible = True
xlWkb.Windows(1).Visible = True

xlWkb.Application.Run ("Import_13504_TB")

xlWkb.Close savechanges:=False
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing

But this does not open a new instance, it uses the one that is currently
open (if there is one open). I do this because I don't want the person to
possibly lose data they are working on in excel.

Any help would be appreciated.

 

[Dave Patrick]

Give this a go.

Function Open_zzz()
Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\test.xls")
xlApp.Application.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
End Function


--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I am trying to open a new instance of Excel from within my code and load a
| specific workbook that contains macros. My code looks like this:
|
| Private Sub butTBOps_Click()
| On Error GoTo butTBOps_Click_Err
| Dim stDocName As String
| Dim stDocName1 As String
| Dim stDocName2 As String
| Dim stDocName3 As String
| Dim stDocName4 As String
| Dim stDocName5 As String
| Dim stDocName6 As String
|
| Dim xlApp As New Excel.Application
| Dim xlWkb As Excel.Workbook
|
|
| Select Case OptCo
|
| Case 13504
| DoCmd.RunMacro "macel13504 TB"
| Set xlWkb = GetObject("g:\Accounting\AR\JSiegel\JSAutomate.xls")
| Set xlApp = xlWkb.Parent
|
| xlApp.Visible = True
| xlWkb.Windows(1).Visible = True
|
| xlWkb.Application.Run ("Import_13504_TB")
|
| xlWkb.Close savechanges:=False
| Set xlWkb = Nothing
| xlApp.Quit
| Set xlApp = Nothing
|
| But this does not open a new instance, it uses the one that is currently
| open (if there is one open). I do this because I don't want the person to
| possibly lose data they are working on in excel.
|
| Any help would be appreciated.
|

 

[Guest]

Case 13504
DoCmd.RunMacro "macel13504 TB"
Set xlApp = New Excel.Application
Set xlWkb =
xlApp.Workbooks.Open(g:\Accounting\AR\JSiegel\JSAutomate.xls", 0)
xlApp.Visible = True
xlWkb.Windows(1).Visible = True

xlWkb.Application.Run ("Import_13504_TB")

xlWkb.Close savechanges:=False
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[Guest]

Thanks,

This worked like a charm.

Case 13504
DoCmd.RunMacro "macel13504 TB"
Set xlApp = New Excel.Application
Set xlWkb =
xlApp.Workbooks.Open(g:\Accounting\AR\JSiegel\JSAutomate.xls", 0)
xlApp.Visible = True
xlWkb.Windows(1).Visible = True

xlWkb.Application.Run ("Import_13504_TB")

xlWkb.Close savechanges:=False
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing