Using VBA Code in Access to format Excel Sheet 3

[jw45]

I am trying to format an Excel spreadsheet from within Access VBA. I have written a fair amount of VBA code in Access but its the first time I've tried to manipulate another application. I'm using Access 97.

When I try to execute the code below it highlights xlCenter and tells me Compile error: variable not defined.

Also, the close command prompts for user to save, "Yes, No, Cancel". How can I bypass this and automatically save the file.

Thanks,
JW

Dim oXL As Object 'Excel.Application
Dim oWb As Object 'Excel.Workbook
Dim oSh As Object 'Excel.Worksheet
Dim X As Integer
Dim currentRow, currentColumn, currentQuarter As String, categoryChanged As Boolean, currentCategoryID As Integer


Set oXL = CreateObject("Excel.Application")
Set oWb = oXL.Workbooks.Open("M:\matlmgmt\lineitm3Comm.xls")
Set oSh = oWb.Sheets("lineitm3Comm")

currentRow = 1
currentColumn = 1

With oWb.ActiveSheet
    .Cells(currentRow, currentColumn).Value = "BUDGET"
    .Cells(currentRow, currentColumn).Font.Bold = True
    .Cells(currentRow, currentColumn).Font.Name = "Arial"
    .Cells(currentRow, currentColumn).Font.Size = 11
    .Cells(currentRow, currentColumn).HorizontalAlignment = xlCenter
End With

oWb.Close

'Do not forget to clean up  properly:
Set oSh = Nothing
Set oWb = Nothing
Set oXL = Nothing

End Function

 

[RoyVidar]

This means you've completely late bound the app (removed the refernce to the Excel library), then Access don't know the Excel constants, and you'll need to suply the litteral value.

I think xlCenter is -4108

I think you could just issue a save:

oWb.Save
oWb.Close

Roy-Vidar

 

[KenReay]

Hi

A guess

xlCenter is a constant defined in Excel

You probably do not have a reference to the object library in which it is defined, see tools\references and the Excel object explorer to see which object libary xlCenter is defined in

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jw45]

Thanks and stars to both of you.

The .Save work and all I had to do was add the Excel Library refernce to my Access cosde.


One additional question:

Where can I get a list of functions/controls?
ie:
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.etc
.etc

Thanks agian.

 

[PHV]

When in Excel VBE feel free to play with the F2 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

Thanks PHV, that was what I was looking for.

As they say,

Every solution breeds new problems

After I added the 'Microsfot Excel 10.0 Object Libary' reference, the VBA code worked fine. The new problem is that this is a multiple user database using different operating systems.

The program blew up on a different user. Looking at the reference it had:
"MISSING:'Microsfot Excel 10.0 Object Libary"

Any suggestions on how I can make this work on all computers?
Can I manually add the required things to his computer?

Thanks yet again,
JW

 

[RoyVidar]

Continue to use late binding - i e - remove the reference again, but change all Excel constants to the value they represent.

While you still have the references, for each constant, just go to the immediate pane (ctrl+g), and type the constant with a preceeding question mark

[tt]? xlBottom [/tt]

hit Enter, and use the resulting value. I often like my code to look something like this:

[tt] .HorizontalAlignment = -4108 ' xlCenter
.VerticalAlignment = -4107 ' xlBottom[/tt]

Roy-Vidar

 

[PHV]

Any suggestions on how I can make this work on all computers
By using late binding as in your original post.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

I now have a differnt problem, use late binding and the following code. I get an error message stating that Variable not defined and it highlights the word 'Selection'. The 'With Slection' works by adding in the reference library. How can I do this using late binding?

    oSh.Columns("A:G").Select
    With Selection
        .HorizontalAlignment = -4108  'xlCenter
        .VerticalAlignment = -4107  'xlBottom
        .WrapText = False
    End With

 

[RoyVidar]

The selection is an Excel selection, and needs to "be anchored" to the correct Excel object...

[tt]' either
With oXL.selection ' or
With oSh.selection[/tt]

Roy-Vidar