Autofit Excel columns from Access VBA

[Al]

I have a pass thru query from Access to SQL Server that
returns the results to an Excel spreadsheet. Everything
works fine but I would like to add the ability to autofit
the results into the columns in Excel when it opens with
the data. Below is a portion of the declarations and code
I'm using that works fine to open the spreadsheet; however
the autofit doesn't work. What am I doing wrong?


Dim strXLPath As String
Dim Sheet As Object, xlWrksht As Object
..
..
..
'Start Microsoft Excel and create a new Worksheet object.
strXLPath = "c:\Database\LIMS.xls"
Set xlWrksht = CreateObject("Excel.Sheet")
DoCmd.OutputTo acOutputQuery, "DateIDAnl", acFormatXLS,_
strXLPath, True

Set Sheet = xlWrksht.workbooks.Open(strXLPath).sheets(1)

'Make Excel visible
xlWrksht.Visible = True
xlWrksht.Columns("A:R").Select
xlWrksht.Columns("A:R").EntireColumn.AutoFit

Thanks in advance,
Al

 

[Matthias Klaey]

I have a pass thru query from Access to SQL Server that
returns the results to an Excel spreadsheet. Everything
works fine but I would like to add the ability to autofit
the results into the columns in Excel when it opens with
the data. Below is a portion of the declarations and code
I'm using that works fine to open the spreadsheet; however
the autofit doesn't work. What am I doing wrong?


Dim strXLPath As String
Dim Sheet As Object, xlWrksht As Object
.
.
.
'Start Microsoft Excel and create a new Worksheet object.
strXLPath = "c:\Database\LIMS.xls"
Set xlWrksht = CreateObject("Excel.Sheet")
DoCmd.OutputTo acOutputQuery, "DateIDAnl", acFormatXLS,_
strXLPath, True

Set Sheet = xlWrksht.workbooks.Open(strXLPath).sheets(1)

'Make Excel visible
xlWrksht.Visible = True
xlWrksht.Columns("A:R").Select
xlWrksht.Columns("A:R").EntireColumn.AutoFit

Thanks in advance,
Al

Here is some code that works for me.
It makes the first row bold, then it freezes some columns, and then it
autofits all columns. Maybe you can use some of these ideas.

Dim objExcel As Excel.Application
Dim objWS As Worksheet

On Error Resume Next
Set objExcel = New Excel.Application
objExcel.Workbooks.Open strExcelFile
Set objWS = objExcel.Sheets(1)
objWS.Rows("1:1").Select
objExcel.Selection.Font.Bold = True
' intSplitColumn = n: Split at column n; 0 -> no columns split
objExcel.ActiveWindow.SplitColumn = intSplitColumn
objExcel.ActiveWindow.SplitRow = 1
objExcel.ActiveWindow.Panes(1).Activate
objExcel.ActiveWindow.FreezePanes = True
objWS.Cells.Select
objExcel.Selection.Columns.AutoFit
objWS.Range("A1").Select
objExcel.ActiveWorkbook.Save
objExcel.Quit
Set objExcel = Nothing

HTH
Matthias Kläy

 

[Al]

Thanks Matthias,
I'm placing this code within Access. When I try to
declare "Dim objExcel As Excel.Application" I get a VB
error message, "Compile Error: User Defined Type not
Defined"

Al

 

[Douglas J. Steele]

You need to set a reference to Excel if you're going to attempt to declare a
variable that way. With any code module open, select Tools | References from
the menu, scroll through the list of available references until you see
Microsoft Excel x.0 Object LIbrary and select it.

On the other hand, if there's a possibility that not all of your users will
have the same version of Excel, you might want to use Late Binding, which
means replacing the line

Dim objExcel As Excel.Application

with

Dim objExcel As Object

and the line

Set objExcel = New Excel.Application

with

Set objExcel = CreateObject("Excel.Application")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks Matthias,
I'm placing this code within Access. When I try to
declare "Dim objExcel As Excel.Application" I get a VB
error message, "Compile Error: User Defined Type not
Defined"

Al