formatting an excel spreadsheet using vb code in access
Posted on 2008-11-12
I'm trying to send query results from Access to an Excel spreadsheet and format the spreadsheet using VB code in an Access module. I managed to copy some code from a Lotus Notes application. Some of it works and some not.... I'm not really sure I understand all of the parameters needed.
I'd like to do the following:
- set column widths for some columns
- wrap text for some columns
- format dates
- create header and footer
- set page orientation
- set page margins
I have been able to set the page orientaion, create the header and footer and bold the column heading - but am stuck on the rest....and have no reference guide.
Below is the code that is working.
Thanks for your help,
Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Creating WorkSheet...."
xlApp.Visible = True
exportname = "qry_CCM_Priority_Report"
xlApp.StatusBar = "Formatting Cells...."
xlApp.Selection.Font.Bold = True
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.ReferenceStyle = 3
xlApp.Worksheets(1).PageSetup.Orientation = 2
xlApp.Worksheets(1).PageSetup.CenterHeader = "CCM Priority Report"
xlApp.Worksheets(1).PageSetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
xlApp.Worksheets(1).PageSetup.CenterFooter = ""