Automating Excel from Access

[Lars Brownies]

From Access I'm trying to set the vertical alignment for all cells of an
Excelfile to 'Top':

Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open("c:\Test.xls")
Set XLSheet = XLBook.Worksheets(1)
XLSheet.Activate
XLSheet.Cells.Select

With Selection
.VerticalAlignment = xlTop
End With

XLBook.SaveAs ("c:\Test2.xls")
Set XLSheet = Nothing
XLBook.Close
Set XLBook = Nothing
XLApp.Quit
Set XLApp = Nothing

but I get the message objetvariable or blockvariable With is not set.
..VerticalAlignment = xlTop
is marked yellow.

Can someone see from the code above what I'm doing wrong?

Thanks,

Lars

 

[Yawar Amin]

You haven't defined the variable `Selection'. Excel does this automatically
in an Excel macro so you probably forgot. Anyway a better option is to do:

with XLSheet.Cells
...
end with

 

[Dave Peterson]

I agree with Yawar's response, but you don't need to declare Selection.

But would have to qualify it.

With XLApp.Selection

But it's better to drop the .select and just use:
with XLSheet.Cells

 

[Lars Brownies]

Thanks Yawar, Dave,
Works like a charm! See the (final) code below. Still, I have the feeling
this code can be written more efficiently, especially the with statement.

If you have any additional hints/tips, I'd love to hear them.

Lars

Private Sub btnEditExcel_Click()
Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

On Error GoTo btnEditExcel_Click_Error

Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open("c:\Map16.xls")
Set XLSheet = XLBook.Worksheets(1)

With XLSheet
.Activate
.Cells.VerticalAlignment = xlTop
.Range("A2").Select
XLApp.ActiveWindow.FreezePanes = True
.Rows("1:1").Select
XLApp.Selection.Font.Bold = True
XLApp.Selection.AutoFilter
.Cells(1, 1).Select
XLBook.Save
End With

Exit_this_sub:
Set XLSheet = Nothing
XLBook.Close
Set XLBook = Nothing
XLApp.Quit
Set XLApp = Nothing
Exit Sub
btnEditExcel_Click_Error:
MsgBox "Error " & Err.Number & " :" & Err.Description
Resume Exit_this_sub
End Sub

 

[Dave Peterson]

You need to activate the sheet to use the .freezepanes, but I'd use something
like:

With XLSheet
.Activate
.Cells.VerticalAlignment = xlTop
.Range("a1").select 'make sure A1 is visible
.Range("A2").Select
XLApp.ActiveWindow.FreezePanes = True
.Rows(1).Font.Bold = True
.rows(1).autofilter
.Cells(1, 1).Select
End With
XLBook.Save

 

[Lars Brownies]

You need to activate the sheet to use the .freezepanes

What exactly do you mean by this?

Lars

 

[Dave Peterson]

Try freezing the panes on a sheet/window that isn't active.

What exactly do you mean by this?

Lars

 

[Lars Brownies]

I see. Thanks again.

Lars

Try freezing the panes on a sheet/window that isn't active.

 

[Dave Peterson]

That's one of the few things that you have to use .activate or .select, though.
For the most part, you can work on things directly.

And when you write your code that way, it'll be easier to understand and update
later.

I see. Thanks again.

Lars