Automating Excel from Access

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

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
 
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
 
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
 
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
 
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
 
You need to activate the sheet to use the .freezepanes

What exactly do you mean by this?

Lars
 
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.

Lars said:
I see. Thanks again.

Lars
Click to expand...
 
Back
Top