Asp Forum - Problem automating Excel 2007 under Win7 - microsoft.public.vb.general.discussion

GS

6/17/2012 7:04:00 PM

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!

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

8 Answers

Ralph

6/17/2012 9:57:00 PM

On Sun, 17 Jun 2012 15:04:17 -0400, GS <gs@somewhere.net> wrote:

>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

6/17/2012 11:45:00 PM

ralph formulated on Sunday :
> On Sun, 17 Jun 2012 15:04:17 -0400, GS <gs@somewhere.net> wrote:
>
>> 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

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Henning

6/17/2012 11:56:00 PM

"GS" <gs@somewhere.net> skrev i meddelandet
news:jrlq60$uqf$1@dont-email.me...
> ralph formulated on Sunday :
>> On Sun, 17 Jun 2012 15:04:17 -0400, GS <gs@somewhere.net> wrote:
>>
>>> 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
>
> errExit:
> ' Screen.MousePointer = vbDefault
> Set xlWkb = Nothing: Set xlApp = Nothing
> End Sub
>
> --
> Garry
>
> Free usenet access at http://www.eternal-sep...
> ClassicVB Users Regroup!
> comp.lang.basic.visual.misc
> microsoft.public.vb.general.discussion
>

/Henning

GS

6/18/2012 12:07:00 AM

It happens that Henning formulated :
> 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'.

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS

6/18/2012 12:38:00 AM

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

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

unknown

6/18/2012 10:29:00 AM

"GS" <gs@somewhere.net> wrote in message news:jrlrf9$5bb$1@dont-email.me...
> It happens that Henning formulated :
>> 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

6/18/2012 1:36:00 PM

Farnsworth wrote :
> "GS" <gs@somewhere.net> wrote in message news:jrlrf9$5bb$1@dont-email.me...
>> It happens that Henning formulated :
>>> 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

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!)

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS

6/18/2012 4:41:00 PM

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!

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

microsoft.public.vb.general.discussion

Problem automating Excel 2007 under Win7

GS

Ralph

GS

Henning

GS

GS

unknown

GS

GS

x Login to ForumsZone