Need help in GetObject/CreateObject excelapp from Attachmate

[hellebore08]

Hi,

I have a macro from attachmate that transfers data to an excel file. My problem is that i can't get these lines to work correctly (code that I use from Attachmate Extra!).

Set obj = Getobject("H:\ultimate.xls")
 
if obj is nothing then
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
end if
 
obj.visible = true

What I need is to attach the macro to an existing excel file - if the excel is already open. And if it is not open, i want the macro to create a new instance of excel and then attach to the newly opened file.

What it does so far is the getobject only but is not creating a new instance of excel when it is not opened. Hope you can help me.. thanks!

I've tried adding an errorhandler but found no luck to make it work. Below is my code with errorhandler:

On Error GoTo openerrorh
 
Set obj = Getobject("H:\ultimate.xls")
 
obj.visible = true
 
exit sub
 
openerrorh:
Set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
 
end sub

 

[hellebore08]

Hi,

I'm not sure but I also think it is a modified version of VBA..

Do not use GetObject if you always want a new instance.

Yes, I always want a new instance but I do NOT want it to make a new instance if the file is already opened.. is it possible?

I am running the macro from Attachmate but most codes I use in it is the same in Excel so I think it is not a big problem.

 

[ZVI]

... I always want a new instance but I do NOT want it to make a new instance if the file is already opened.. is it possible?

Yes, it's possible - see 2 versions of code below.

Version with testing of MyFile presence in active Excel application:

' Check if MyFile is open in active Excel application
Sub Test1()
  
  Const MyFile$ = "ultimate.xls"
  Const MyDrive$ = "H"
  
  Dim obj As Object
  
  ' Check presence of an active Excel application
  On Error Resume Next
  Set obj = GetObject(, "Excel.Application")
  ' Not Err means that active Excel is present
  If Not Err Then
    ' Try to find MyFile in active Excel
    With obj.Workbooks(MyFile): End With
    Set obj = Nothing
    ' If MyFile is already open then exit
    If Not Err Then Exit Sub
  End If
  On Error GoTo 0
  
  ' Create new Excel instance
  Set obj = CreateObject("Excel.Application")
  
  ' Open MyFile in new Excel instance
  obj.Workbooks.Open MyDrive & ":\" & MyFile
  obj.Visible = True
  Set obj = Nothing
  
End Sub

Version with testing of MyFile open state in any Excel instance:

' Check if MyFile is open in any Excel instance
Sub Test2()
  
  Const MyFile$ = "ultimate.xls"
  Const MyDrive$ = "H"
  
  Dim obj As Object, FN As Integer
  
  ' Check "already open" state of MyFile
  FN = FreeFile
  On Error Resume Next
  Open MyDrive & ":\" & MyFile For Random Access Read Write Lock Read Write As #FN
  Close #FN
  ' Err means that MyFile is already open in Excel to edit
  If Err Then Exit Sub
  On Error GoTo 0
  
  ' Create new Excel instance
  Set obj = CreateObject("Excel.Application")
  
  ' Open MyFile in new Excel instance
  obj.Workbooks.Open MyDrive & ":\" & MyFile
  obj.Visible = True
  Set obj = Nothing
  
End Sub

Both are not tested.

For the great and strict solution of Jaafar Tribak have a look on: Open file in own instance of Excel application

Regards,

 

[hellebore08]

Thanks for the code ZVI..

however, upon testing..
Test1 is not working.. it does not open any excel file..
Test2 is opening another excel file even though there is one already opened.

The code seems clever enough to get what i need but i can't understand why it doesn't work.. any ideas?

 

[ZVI]

As I've indicated earlier both versions of the code were not tested.

The version 1 is not good because it looks only into the active instance of Excel.
It doesn't prevent of opening MyFile one more in case it was already open in not active Excel instance.
But just for consistency here is the fixed version 1:

' Check if MyFile is open in active Excel application
Sub Test1()
  
  Const MyFile$ = "ultimate.xls"
  Const MyDrive$ = "H"
  
  Dim obj As Object
  
  ' Check presence of an active Excel application
  On Error Resume Next
  Set obj = GetObject(, "Excel.Application")
  ' Err=0 means that at least one (active) instance of Excel is present
  If Err.Number = 0 Then
    ' Try to find MyFile in active Excel
    With obj.Workbooks(MyFile): End With
    Set obj = Nothing
    ' If MyFile is already open then exit
    If Err.Number = 0 Then Exit Sub
  End If
  On Error GoTo 0
  
  ' Create new Excel instance
  Set obj = CreateObject("Excel.Application")
  
  ' Open MyFile in new Excel instance
  obj.Workbooks.Open MyDrive & ":\" & MyFile
  obj.Visible = True
  Set obj = Nothing
  
End Sub

After testing I would recomend fixed version 2:

' Check if MyFile is open in any Excel instance
Sub Test2()
  
  Const MyFile$ = "ultimate.xls"
  Const MyDrive$ = "H"
  
  Dim obj As Object, FN As Integer
  
  ' Check "already open" state of MyFile
  FN = FreeFile
  On Error Resume Next
  Open MyDrive & ":\" & MyFile For Random Access Read Write Lock Read Write As #FN
  Close #FN
  ' Err <> 0 means that MyFile is already open in Excel to edit
  If Err.Number <> 0 Then Exit Sub
  On Error GoTo 0
  
  ' Create new Excel instance
  Set obj = CreateObject("Excel.Application")
  
  ' Open MyFile in new Excel instance
  obj.Workbooks.Open MyDrive & ":\" & MyFile
  obj.Visible = True
  Set obj = Nothing
  
End Sub

 

[hellebore08]

Wee!.. Amazing!..

It worked!.. thanks ZVI!..

actually at the time before your reply, Ive searched for an answer from another site which gave me a macro that has a declared Function.. well, yours is shorter and is 100% accurate to what i'm been looking for..

uhm ok, to share with what i got from another site, here it is.. but i prefer to use the code you provided since it is shorter..

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0
Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
End Select
End Function
 
Sub testing()
        Const MyFile$ = "DRGSCR ditto - updated.xls"
        Const MyDrive$ = "H"
 
        Dim obj As Object
 
        ' Create new Excel instance
        If Not IsFileOpen(MyDrive & ":\" & MyFile) Then
        Set obj = CreateObject("Excel.Application")
        ' Open MyFile in new excel instance
        obj.Workbooks.Open MyDrive & ":\" & MyFile
        obj.Visible = True
        Set obj = Nothing
        End If
End Sub

the "SUB" part is derived from your code and the function is just a straight copy paste from the other site.. http://www.excelforum.com/excel-programming/613988-open-new-instance-of-excel-with-named-file.html

 

[ZVI]

Glad to hear you sorted this out!

 

[thnghiachau]

Dear all,
Similar to this subject, now i have situation in VB6:
Normal:

    Set objExcelApp = CreateObject("Excel.Application")

    Set objWB = objExcelApp.Workbooks.Open("D:"\Test.xlsx")

        For Each sht In objWB.Worksheets

            List1.AddItem (sht.Name)

        Next

It's run OK.

Now, File "Test.xlsx" is OPENED already, so how to SET objWB for file Test.xlsx without using Workbooks.Open (because it's opened already!!)
have any advidse, please !

Thanks for all