VB 6.0 to Launch excel

[Jason V]

sI am using VB 6.0 to launch excel just to make it look more profeesional to
use an .exe for the end user. In one of the threads it stated that excel vba
can not be compiled ino an exe so use VB to launch excel. My problem is that
when I open an excel document the enable macros is not set and my Auot_Open
routine which shows my form does not run.

Any help on how to do this?

 

[Peter T]

Hi Jason,

xlApp.myWB.RunAutoMacros xlAutoOpen ' 1 if using late binding

or put your code in the workbook open event.

Or, if your xls open event code is only for use in conjuction with your exe,
the open code could go in your exe. Maybe other vba code too.

Regards,
Peter T

 

[Jason V]

Peter, thank you for your response I am a little late it getting back on it.
I am not sure what late binding is. Can you tell me?

I put in xl.app.xlbook.RunAutoMacros Autpen but it does not work.
the error says that the object does not support this property or method.

Auto_open is the name of the procedure in excel and all it does in
quoteform.show,
How can I do this in my exe?
Thank You

 

[Peter T]

Try writing snipets of code first in VBA before VB6

I put in xl.app.xlbook.RunAutoMacros Autpen but it does not work.

is there really a dot in 'xl.app', assuming not try

xlApp.xlbook.RunAutoMacros xlAutoOpen

xlAutoOpen is a named constant value 1

I am not sure what late binding is. Can you tell me?

Briefly, if you set a reference to Excel in your VB6 (Project > References)
to "Microsoft Excel ver,0 Object Library"
you will have early binding. In developing, one advantage is you will get
all the intellisense and would have seen xlAutoOpen appear in a dropdown
after typing the space after RunAutoMacros. Also you can fully declare
objects, eg
Dim wb As Excel.Workbook

If you are unable to set the reference to the earliest version of Excel your
app will be used with, remove the defence and use late binding. You would
also have to change all named constants like xlAutoOpen to their intrinsic
values, in this case 1&.

Also would need to change all -
Dim xlThing As Excel.SomeThing to
Dim objThing as Object

Regards,
Peter T

 

[Jason V]

Peter,
Here is my actual code
Dim xlapp As Excel.Application 'The Excel application object
Dim xlBook As Excel.Workbook 'The Excel workbook object
Dim xlSheet As Excel.Worksheet 'The Excel spreadsheet object
Dim selObj As Visio.Selection 'Shapes selection collection
Dim shpObj As Visio.Shape 'A shape instance
Dim i As Integer
Dim row As Integer

'Set shpsObj to the current selection
'Set selObj = Visio.ActiveWindow.Selection
Set xlapp = CreateObject("Excel.Application")
xlapp.Application.Visible = True
Set xlBook = xlapp.Workbooks.Open("C:\CPGTools\TalonQuoter.xls")

xlapp.xlBook.RunAutoMacros xlAutoOpen

I have references set for Microsoft excel 11.0 object library.
When I run the above code excel opens and the file "Myfile.xls" opens but
does not come up with screen to enable macros and my Autpen routine
in excel vab does not run. When I step through and execute the last line
I get error 438 "object doesn't support this property or method."

I did not have anything come up after a space on
xlApp.xlbook.RunAutoMacros

Is it fair to say late binding is without references and early binging is
with?

 

[Jon Peltier]

Change that last line to

xlBook.RunAutoMacros xlAutoOpen

- Jon

 

[Peter T]

I slightly misguided you earlier, use Jon's suggestion and omit the xlapp.

When I run the above code excel opens and the file "Myfile.xls" opens but
does not come up with screen to enable macros

That's normal when opening as you are

Is it fair to say late binding is without references and early binging is
with?

Yes but that's somewhat simplified. You are clearly using early binding, you
say with a ref to 11.0 (XL2003) so be aware it might fail for any user with
a lower version. There are numerous postings on the subject in this in this
ng, try searching something like "develop early distribute late binding".

Looking at my previous post I see "defence", must have been my spell checker
changing "reference" without my noticing.

Regards,
Peter T

 

[Jason V]

Thank You. Works fine. Now I am sure I will have a new set of questions but
this
gets me going again.