Formatting Excel using Access VBA

[Chexpeare]

Any tips, or suggestions, for formatting and Excel report after it is pushed from Access?
I have the following code set up and every works except for PaperSize and Margins. Is there a standard VBA code reference available somewhere that addresses Access to Excel operations? Thanks

With xlApp.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$C"
.LeftHeader = "XYZ Financial Wonks, Inc."
.CenterHeader = ""
.RightHeader = "COMPANY CONFIDENTIAL"
.LeftFooter = "&Z&F"
.CenterFooter = ""
.RightFooter = "&P of &N"
' .LeftMargin = = xlSheet.InchesToPoints(0.25)
' .RightMargin = xlSheet.InchesToPoints(0.25)
' .TopMargin = Application.InchesToPoints(0.25)
' .BottomMargin = Application.InchesToPoints(0.25)
' .HeaderMargin = Application.InchesToPoints(0.25)
' .FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = False
.Orientation = 2
.Draft = False
.PaperSize.xlPaperLegal
.FirstPageNumber = 1
.BlackAndWhite = False
.Zoom = 100
End With

 

[Chexpeare]

Chexpeare

Don't use Select or On Error Resume Next.

I wouldn't even suggest using either if the code was actually in Excel itself.

The first, ie Select, is generally not needed and the 2nd could just be hiding errors.

And those errors/the code could lead to 'ghost' instances of Excel.

Another point is that Excel VBA constants won't be available in Access VBA unless you set the reference as rory suggested.

And lastly it really would help if we could see the whole code, and if you were to tell us where/when it's being run.

This is the main module:
Private Sub Export_to_KIT_Click()

On Error GoTo Err_Export_to_KITT_Click

Dim xlApp As Object
Dim xlWkbk As Object
Dim xlSheet As Object
Dim DirNew As String
Dim FileName As String
Dim rng As Excel.Range

DirNew = "\\Psls17\Compliance group\Compliance Monitoring\Surveillance Technology Group\Project Tracker\PTS Test Folder\"
FileName = "KIT " & Format(Now(), "mm-dd-yy (hhmmss)") & ".xls"

Set xlApp = CreateObject("Excel.Application")

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry01 - Mtbl01 - KITT"

DoCmd.OutputTo acOutputTable, "Mtbl01 - KITT", acFormatXLS, _
DirNew & FileName, 0

Set xlWkbk = xlApp.Workbooks.Open(DirNew & FileName)
Set xlSheet = xlWkbk.Worksheets("Mtbl01 - KITT")

xlApp.Visible = True
xlApp.ScreenUpdating = True

' ----> The following line generates an error: 424 Object required
Set rng = xlSheet.Range("A1", xlSheet.UsedRange.SpecialCells(xlCellTypeLastCell)).Select

Call xlGridFormat(xlApp, xlWkbk, xlSheet, rng)
Call Format_KITT(xlApp, xlWkbk, xlSheet, DirNew, FileName)

Exit_Export_to_KITT_Click:
Exit Sub

Err_Export_to_KITT_Click:
MsgBox "Error Number: " & Err.Number & Chr(13) & Err.Description
Resume Exit_Export_to_KITT_Click

End Sub

 

[Norie]

Remove .Select.

 

[NateO]

Also (yes, delete Select) Change:

xlCellTypeLastCell

to:

11

And again, open your Task Manager (Ctrl-Alt-Delete) and kill all Excel.exe processes (in the processes tab).

 

[Chexpeare]

Remove .Select.

Bingo! Thanks, Norie!
I removed .Select and the formatting routine works like a charm every single time.

Part of my problem was also early binding of xlApp. Thanks, NateO!

Thank you rorya for the Excel.Range!

The best part is that the ghost EXCEL.exe is closing out on its own.

My goal now is to get up to the kung fu level you guys are kicking around...

Thanks again!

 

[Chexpeare]

Also (yes, delete Select) Change:

xlCellTypeLastCell

to:

11

And again, open your Task Manager (Ctrl-Alt-Delete) and kill all Excel.exe processes (in the processes tab).

Thanks, NateO!
I notice that '11' works best when I'm not referencing the Excel Object Library. Is there a list of these types of codes floating around in MS Heaven somewhere?

 

[NateO]

It's in your Object Browser, in Excel's VBE hit F2, now search on xlCellTypeLastCell.

When it finds it and you select it, note the following at the bottom:

Const xlCellTypeLastCell = 11
Member of Excel.XlCellType

So when you are using Late Binding (no Library reference), you want to use the numeric equivalent of the Constant that's foreign to the Object Model you're using. They're all listed in the Object Browser, which is extremely underrated.

Hi-ya!

 

[Chexpeare]

It's in your Object Browser, in Excel's VBE hit F2, now search on xlCellTypeLastCell.

When it finds it and you select it, note the following at the bottom:


So when you are using Late Binding (no Library reference), you want to use the numeric equivalent of the Constant that's foreign to the Object Model you're using. They're all listed in the Object Browser, which is extremely underrated.

Hi-ya!

Underrated is right! I'll start using it as a reference from now on.
I'm still only a Grey Belt!
Thanks, dude!

 

[NateO]

You are welcome.