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

 

[RoryA]

Try:

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 = = xlApp.InchesToPoints(0.25)
 .RightMargin = xlApp.InchesToPoints(0.25)
 .TopMargin = xlApp.InchesToPoints(0.25)
 .BottomMargin = xlApp.InchesToPoints(0.25)
 .HeaderMargin = xlApp.InchesToPoints(0.25)
 .FooterMargin = xlApp.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

assuming you have a reference set to the Excel object library. If not, you will have to use a literal value for the papersize or declare your own constant.

 

[Chexpeare]

I apologize. I thought the object reference was in place. Thanks.

I think I'm all set now.

 

[Chexpeare]

I do have another question.

Once Access creates the Excel spreadsheet, it is supposed to highlight the data range and format it with gridlines. When I try to select the data range using the following lines of code, Excel will respond ONCE IN A WHILE. What am I missing?

xlSheet.Range("A1").Select (always works)
xlSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select (works once in a while)

...directly followed by the appropriate gridline formatting code (only works once in a while)
...OR followed by a call to the gridline formatting subroutine (only works once in a while)

Thanks

 

[RoryA]

You should avoid selecting - it's almost always unnecessary to select objects to work with them. You should also never use an unqualified reference to an Excel object from an automating application - so don't ever use just Activecell, always specify xlApp.Activecell but again, you don't need to activate. You can use something like

Dim rng as Excel.Range
set rng = xlsheet.range("A1", xlsheet.usedrange.SpecialCells(xlLastCell))

and then format rng directly. Does that make sense?

 

[Chexpeare]

You should avoid selecting - it's almost always unnecessary to select objects to work with them. You should also never use an unqualified reference to an Excel object from an automating application - so don't ever use just Activecell, always specify xlApp.Activecell but again, you don't need to activate. You can use something like

Dim rng as Excel.Range
set rng = xlsheet.range("A1", xlsheet.usedrange.SpecialCells(xlLastCell))

and then format rng directly. Does that make sense?

Thank you, Rory. Yes, I understand. It's funny, your suggestion (quoted above) worked like a charm...the first time. When I tried running it again, Excel did not format. I checked to see if perhaps there were any Excel processes still residing in memory. Sure enough, there was one. I manually deleted the Excel process while Access was active, but the spreadsheet still didn't format. I closed and reopened Access and the macro ran properly. This leads me to believe that perhaps I'm not closing out the process properly within the macro. Any suggestions?

I have the following code at the end of the formatting routine:

xlApp.Workbooks(FileName).Close SaveChanges:=True
xlApp.Close
Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSheet = Nothing
Set rng = Nothing ----> Incidentally, rng = Nothing throughout the entire routine

Thanks

 

[RoryA]

It should be xlApp.Quit not Close (You ought to get an error). Also xlLastCell should really be xlCellTypeLastCell (the values are the same, but there's no guarantee they will remain so in different versions!)
If you still get a process left open with the above change, then can you post the whole code - there may be another unqualified reference in there, which will cause additional processes.

 

[Chexpeare]

It should be xlApp.Quit not Close (You ought to get an error). Also xlLastCell should really be xlCellTypeLastCell (the values are the same, but there's no guarantee they will remain so in different versions!)
If you still get a process left open with the above change, then can you post the whole code - there may be another unqualified reference in there, which will cause additional processes.

Your suggestions are in place, but the following line keeps generating an error:

Set rng = xlSheet.Range("A1", xlSheet.UsedRange.SpecialCells(xlCellTypeLastCell)).Select

ERROR '424': Object required

If I include "On Error Resume Next" the code runs, but no formatting and rng is set to "Nothing".

 

[NateO]

How did you declare xlApp? Are you binding Late or Early?

Make sure you go into your Task Manager and kill all running Excel.exe processes before you attempt to rerun this procedure. A hung Excel instance will cause you all kinds of headaches.

PTI.

 

[Norie]

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.