Problem automating Excel 2007 under Win7

[GS]

Hi all,

Does anyone know why my VB6.exe can't get Excel 2007 to run under Win7?
It works fine when I set the default Office as MSO 2003, but not
working when default Office is MSO 2007. Both installs are
'Professional' editions. (This doesn't happen under XP)

Thanks in advance. All/any help will be most appreciated!

[ralph]

Post by GS
Hi all,
Does anyone know why my VB6.exe can't get Excel 2007 to run under Win7?
It works fine when I set the default Office as MSO 2003, but not
working when default Office is MSO 2007. Both installs are
'Professional' editions. (This doesn't happen under XP)
Thanks in advance. All/any help will be most appreciated!

Need more information.

"... can't get Excel 2007 to run ..."?
Error messages? Hangs? Nothing? Odd behavior? ...

Automation - late or early binding? Library?

...

-ralph

[GS]

Post by ralph

Post by GS
Hi all,
Does anyone know why my VB6.exe can't get Excel 2007 to run under Win7?
It works fine when I set the default Office as MSO 2003, but not
working when default Office is MSO 2007. Both installs are
'Professional' editions. (This doesn't happen under XP)
Thanks in advance. All/any help will be most appreciated!

Need more information.
"... can't get Excel 2007 to run ..."?
Error messages? Hangs? Nothing? Odd behavior? ...
Automation - late or early binding? Library?
...
-ralph

Using CreateObject("Excel.Application") and it doesn't open Excel.
Here's my code...

Sub StartApp()
Dim xlWkb As Excel.Workbook, oCA As Object

On Error GoTo errExit
Set xlApp = CreateObject("Excel.Application")
With xlApp
If .Version >= 12 Then
For Each oCA In .COMAddIns: oCA.Connect = False: Next 'oCA
Set xlWkb = .Workbooks.Open(FileName:=App.Path & gsAPP_WKB_UI12,
Password:=gszPWRD)
End If
Set xlWkb = .Workbooks.Open(FileName:=App.Path & "\" & gszAPP_WKB,
Password:=gszPWRD): xlWkb.RunAutoMacros xlAutoOpen
.WindowState = xlMaximized: .Visible = True: .UserControl = True
End With

errExit:
' Screen.MousePointer = vbDefault
Set xlWkb = Nothing: Set xlApp = Nothing
End Sub

[Henning]

Post by ralph

Post by GS
Hi all,
Does anyone know why my VB6.exe can't get Excel 2007 to run under Win7?
It works fine when I set the default Office as MSO 2003, but not working
when default Office is MSO 2007. Both installs are 'Professional'
editions. (This doesn't happen under XP)
Thanks in advance. All/any help will be most appreciated!

Need more information.
"... can't get Excel 2007 to run ..."?
Error messages? Hangs? Nothing? Odd behavior? ...
Automation - late or early binding? Library?
...
-ralph

Using CreateObject("Excel.Application") and it doesn't open Excel. Here's
my code...
Sub StartApp()
Dim xlWkb As Excel.Workbook, oCA As Object
On Error GoTo errExit
Set xlApp = CreateObject("Excel.Application")
With xlApp
If .Version >= 12 Then
For Each oCA In .COMAddIns: oCA.Connect = False: Next 'oCA
Set xlWkb = .Workbooks.Open(FileName:=App.Path & gsAPP_WKB_UI12,
Password:=gszPWRD)

Missing & "\" in App.Path & gsAPP_WKB_UI12?

End If
Set xlWkb = .Workbooks.Open(FileName:=App.Path & "\" & gszAPP_WKB,
Password:=gszPWRD): xlWkb.RunAutoMacros xlAutoOpen
.WindowState = xlMaximized: .Visible = True: .UserControl = True
End With
' Screen.MousePointer = vbDefault
Set xlWkb = Nothing: Set xlApp = Nothing
End Sub
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

/Henning

[GS]

Post by Henning
Missing & "\" in App.Path & gsAPP_WKB_UI12?

Const gsAPP_WKB_UI12 As String = "\ui12su.xlam"

I did not prepend the backslash to gszAPP_WKB because I use the name
without it various places. The only thing gsAPP_WKB_UI12 does is setup
the UI via its XML component in versions of Excel that use the
'Ribbon'.

[Farnsworth]

Post by GS

Post by Henning
Missing & "\" in App.Path & gsAPP_WKB_UI12?

Const gsAPP_WKB_UI12 As String = "\ui12su.xlam"
I did not prepend the backslash to gszAPP_WKB because I use the name
without it various places. The only thing gsAPP_WKB_UI12 does is setup the
UI via its XML component in versions of Excel that use the 'Ribbon'.

Since you mentioned that you have a USB version, use a global variable
instead of using App.Path directly. App.Path could have "\" at the end if
your app is running from a root folder such as from a network drive
dedicated to applications, or USB drives. Example App.Path contents:

C:\Program Files\My App <- No backslash
E:\ <- USB or network drive, root folder, has backslash

So use code like this:

Public AppPath As String

AppPath = App.Path
If Right(AppPath, 1) = "\" Then
AppPath = Left(AppPath, Len(AppPath) - 1) ' Remove the trailing \, such
as in "C:\"
End If

[GS]

Post by Farnsworth

Post by GS

Post by Henning
Missing & "\" in App.Path & gsAPP_WKB_UI12?

Const gsAPP_WKB_UI12 As String = "\ui12su.xlam"
I did not prepend the backslash to gszAPP_WKB because I use the name
without it various places. The only thing gsAPP_WKB_UI12 does is setup the
UI via its XML component in versions of Excel that use the 'Ribbon'.

Since you mentioned that you have a USB version, use a global variable
instead of using App.Path directly. App.Path could have "\" at the end if
your app is running from a root folder such as from a network drive dedicated
C:\Program Files\My App <- No backslash
E:\ <- USB or network drive, root folder, has backslash
Public AppPath As String
AppPath = App.Path
If Right(AppPath, 1) = "\" Then
AppPath = Left(AppPath, Len(AppPath) - 1) ' Remove the trailing \, such
as in "C:\"
End If

Interestingly, I do exactly that in app code for the opposite reason,
and set it up in my 'InitGlobals' proc at startup...

Public gsAppPath$

gsAppPath = App.Path
If Not Right(gsAppPath, 1) = "\" Then gsAppPath = gsAppPath & "\"

The structure I use for storage (regardless of drive/location) is to
put all my apps under my main folder...

C:\
MyMainFolder\
MyApp\
MyOtherApp\
...and so on

..where the root could be a hard drive on a local machine or removeable
storage device. Since there's not much call/use to run my apps from a
network share I don't support that config because my licensing
methodology is structured to use local installs that validate on the
server.

What I have in this scenario is a frontloader that validates licensing
before startup. I was temporarily brain dead when I modified my code to
unload any COMAddins from my auto-instance, knowing full well an error
would occur if there weren't any. (Normally, auto-instances have no
addins or files that normally load from a 'startup' folder. However,
some developers wrongfully put their COMAddins under HKLM instead of
HKCU and so these will persist no matter what. This does not happen
with workbook-based addins!)

[GS]

Farnsworth,
Your input has prompted me to update this app's frontloader consistent
with my frontloader template code. The templates includes several
components (modules, forms) but only 1 module (_mOpenClose) is
app-specific and so I use SaveAs for the project and this module to the
app's 'Frontloader' folder.

The template for _mOpenClose has this code...

Sub StartApp()
Dim xlWkb As Excel.Workbook, oCA As Variant

On Error GoTo errExit
Set xlApp = CreateObject("Excel.Application")
With xlApp
If .Version >= 12 Then
Set xlWkb = .Workbooks.Open(FileName:=gsAppPath & gsAPP_WKB_UI12,
Password:=gszPWRD)
End If
Set xlWkb = .Workbooks.Open(FileName:=gsAppPath & gszAPP_WKB,
Password:=gszPWRD): xlWkb.RunAutoMacros xlAutoOpen
.WindowState = xlMaximized: .Visible = True: .UserControl = True
End With
On Error Resume Next
For Each oCA In xlApp.COMAddIns: oCA.Connect = False: Next 'oCA

errExit:
Set xlWkb = Nothing: Set xlApp = Nothing
End Sub

..whereby I now do not include the backslash in constants for
filenames.

Unlike my VB6 apps, the frontloader doesn't use 'InitGlobals' because I
specify them in hard code for each app in _mOpenClose. In this case
I've inserted the following at the top of 'Sub Main'...

gsAppPath = App.Path
If Not Right(gsAppPath, 1) = "\" Then gsAppPath = gsAppPath & "\"

..so the frontloaders are now consistent with my regular apps.

****
Your post also raises another issue that requires revising
'InitGlobals' in my regular apps so that the file locations (which are
stored in 'app.ini') don't include the path anymore. This makes the
apps much easier to manage when run from a USB since the likelyhood of
the drive letter never being the same is fairly high.

Thank you!

[GS]

I figured it out by a process of msgbox following each line of my With
block. Turns out that after investigating further, the COMAddins
collection on the 2 test machines is empty. IOW, no COMAddins are
installed. This is not the case with my 2 XP machines and so went
undetected. Here's my revised code...

Sub StartApp()
Dim xlWkb As Excel.Workbook, oCA As Variant

On Error GoTo errExit
Set xlApp = CreateObject("Excel.Application")
With xlApp
If .Version >= 12 Then
Set xlWkb = .Workbooks.Open(FileName:=App.Path & gsAPP_WKB_UI12,
Password:=gszPWRD)
End If
Set xlWkb = .Workbooks.Open(FileName:=App.Path & "\" & gszAPP_WKB,
Password:=gszPWRD): xlWkb.RunAutoMacros xlAutoOpen
.WindowState = xlMaximized: .Visible = True: .UserControl = True
End With

On Error Resume Next '//if none exist
For Each oCA In xlApp.COMAddIns: oCA.Connect = False: Next 'oCA

errExit:
' Screen.MousePointer = vbDefault
Set xlWkb = Nothing: Set xlApp = Nothing
End Sub