Formatting Excel using Access VBA

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Try:
Code:
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

Board Regular
Joined
Mar 22, 2007
Messages
64
I apologize. I thought the object reference was in place. Thanks.

I think I'm all set now.
 
Last edited:

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Code:
Dim rng as Excel.Range
set rng = xlsheet.range("A1", xlsheet.usedrange.SpecialCells(xlLastCell))
and then format rng directly. Does that make sense?
 

Chexpeare

Board Regular
Joined
Mar 22, 2007
Messages
64
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
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Board Regular
Joined
Mar 22, 2007
Messages
64
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,601
Office Version
365
Platform
Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,809
Messages
5,483,037
Members
407,374
Latest member
davidausten

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top