Excel and VB6

[Phil]

Hello world of experts,

I know this is no VB6 newsgroup, but I bet you can help on this:.

I am trying from VB6 to access an Excel object and am filling okay a
spreadsheet. The only thing I cannot do is to left justify all spreadsheet
data at the end of my shovelling experience (-;

I am doing something like :

dim xlapp as object
xlapp = CreateObject("Excel.Application")
....
....

at the end I'd like to do (as I would do with Excel) :

xlapp.Cells.Select
With Selection
.HorizontalAlignment = xlLeft
End With

but I keep getting an error message saying that applying HorizontalAlignment
cannot be done on the object...
I certainly do something wrong but I can't see it...

Any hint?

Thanks a lot

Phil

 

[Bob Phillips]

Firstly, you need

Set xlapp = CreateObject("Excel.Application")

and have you set a reference to Excel in you VB6 project, openened a
wokbook, activated a sheet? I would use

dim xlapp as object
dim xlWb as object
dim xlWs as obejct

Set xlapp = CreateObject("Excel.Application")

Set xlWb = xlapp/Workbooks.Open(Filename:="c:\myBook.xls")
Set xlWs = xlWb.Activesheet
With xlws
.HorizontalAlignment = xlLeft
End With

that is keep all the object specifically identified


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Your code works for me.

It will fail if there is no workbook open. Is that the case?

 

[Phil]

Hi there,

The workbook is open, but visible = false...Does this make a difference?

Thanks

 

[Phil]

Hi,

Firstly, you need

Set xlapp = CreateObject("Excel.Application")

I do...

and have you set a reference to Excel in you VB6 project, openened a
wokbook, activated a sheet? I would use

dim xlapp as object
dim xlWb as object
dim xlWs as obejct

Set xlapp = CreateObject("Excel.Application")

Set xlWb = xlapp/Workbooks.Open(Filename:="c:\myBook.xls")

I am creating it on the fly, so I do not open one. I just create one and
fill in the sheets...All sheets are filled anyway...

Set xlWs = xlWb.Activesheet
With xlws
.HorizontalAlignment = xlLeft
End With

The message I get is: "This object doesn't handle this property of
method"...when getting to the .HorizontalAlignment line

Thanks

 

[Guest]

No the visible flag does not make any difference.

 

[Guest]

It is possible that the 'Selection' is not 'Cells' i.e. it could be another
object. Try:

xlapp.Activesheet.Cells.Select

or, better,

clapp.Sheets(n).Cells.Select w' where n is one of your worksheets.

 

[Bob Phillips]

Had a missing cell in that line, should read
Set xlapp = CreateObject("Excel.Application")

Set xlWb = xlapp.Workbooks.Add
Set xlWs = xlWb.Activesheet
With xlWs.Cells
.HorizontalAlignment = xlLeft
End With

and this works for me (but then again, so did your original). Have you set a
reference to

Again I ask, have you set a reference to Excel in the VB project. If you
haven't, and you don't have Option Explicit at the start of the code, it
will run until it gets to that line then bomb out as there will be no value
associated with xlLeft (VB does not have an xlLeft constant).

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

Hi Bob,

Again I ask, have you set a reference to Excel in the VB project. If you
haven't, and you don't have Option Explicit at the start of the code, it
will run until it gets to that line then bomb out as there will be no value
associated with xlLeft (VB does not have an xlLeft constant).

For my interest, would it (and similar) work with xlLeft as a value:

With xlWs.Cells
.HorizontalAlignment = -4131 ' xlLeft
End With

Regards,
Peter

 

[Bob Phillips]

Yes it would, this is part of the principle of late binding, where you don't
have access to the type library information. The compiler will just
translate that variable to its value and use that, so you can just as
validly use the value directly.

If Phil hasn't set a reference to the Excel library, he is per se late
binding, so he won't have access to any of the Excel constants, just the VB
constants.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Phil]

Again I ask, have you set a reference to Excel in the VB project. If you
haven't, and you don't have Option Explicit at the start of the code, it
will run until it gets to that line then bomb out as there will be no
value
associated with xlLeft (VB does not have an xlLeft constant).

Well, I did and didn't...I mean, I first have to asssume that Excel may NOT
be present on the user's computer. In this case can I still make a reference
to Excel or will it collapse on the user's computer?

Then I tried and had no more success. Had no "Option Explicit". Will try
again and keep you posted..

Thanks

Phil

 

[Phil]

Right,

Works!!

Now just let me know. Got it for the xlLeft invalid value. Now, what if the
user doesn't have Excel. Trying to create the install program, I see the
install package is binding Excel with it...Weird!!

My goal was to use some "liteweight" thing...Any way to avoid this?

again, many thanks for your help

Phil

 

[Bob Phillips]

Phil,

If the user doesn't have Excel, then there is no way that you can use it,
either as a stand-alone product, or via automation.

--

HTH

RP
(remove nothere from the email address if mailing direct)