Excel formatting from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with a report that needs to be in Excel. I found some code that
I use to manipulate the headers and footers; however, I need to know how to
change the page setup from portrait to landscape and autosize each of the
columns.

Can anyone help or know of a really good web resource that tailors to
non-programmers?

Thanks

The code I have so far is:

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Workbooks.Open "C:\SomeFile.xls"
With objExcel.ActiveWorkbook.ActiveSheet.PageSetup
..CenterHeader = "Center Header"
..LeftFooter = "Left Footer"
..CenterFooter = "Center Footer"
..RightFooter = "Right Footer"
End With
objExcel.ActiveWorkbook.Close savechanges:=True
objExcel.Application.Quit
Set objExcel = Nothing
 
You can't get there from here without programming!

The first part of what you need to learn to program all happens in the
Excel part of the world. Practice all of the formatting of your
target sheet manually in Excel. Write down every step you take to get
that done. Be sure to start by creating a new Workbook.

With your list at hand, turn on Excel's Macro Recorder. Run through
your list from top to bottom. Turn off the Recorder. Give your macro
a name like "PrepareSpreadsheet". You'll probably have a few
mistakes. You can step through your code with the debugger, add and
modify code until you get it right.

Now shift to the Access world and look in Help for Automation. The
Help on Automation is somewhat terse but it is a great starting point.

Start your code in an new Access module. You will need to set a
Reference to Excel in Tools|References. Copy your code in Excel over
to your new module. This is where it will live and execute.

Press On....

The above is just one way to use Automation to get done what you want.
If the above seems to be too intimidating then you should hire help to
get it done.

HTH
 
Back
Top