By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Format Excel document from Access

P: 94
I have succesfully exported an Access report to Excel and then opened it, now i want to format it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3.     Dim Date1
  4.     Dim stReportAddress As String
  5.  
  6.     Date1 = Format(Date, "dd-mm-yyyy")
  7.     stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
  8.  
  9.     'Export Report
  10.     DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
  11.  
  12.     'Open Report
  13.     Application.FollowHyperlink stReportAddress
  14.  
  15. End Sub
Not sure how to perform the formatting now it is open...

I basically want to resize all the columns, create a header and place a few cells in bold.

Take resizing the columns, i know the code from the excel document would be:

Expand|Select|Wrap|Line Numbers
  1. Cells.Select
  2. Cells.EntireColumn.AutoFit
I'm just not sure how to prefix it as simply putting that code in doesn't work.

Any ideas out there?????
Feb 13 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
I have succesfully exported an Access report to Excel and then opened it, now i want to format it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3.     Dim Date1
  4.     Dim stReportAddress As String
  5.  
  6.     Date1 = Format(Date, "dd-mm-yyyy")
  7.     stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
  8.  
  9.     'Export Report
  10.     DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
  11.  
  12.     'Open Report
  13.     Application.FollowHyperlink stReportAddress
  14.  
  15. End Sub
Not sure how to perform the formatting now it is open...

I basically want to resize all the columns, create a header and place a few cells in bold.

Take resizing the columns, i know the code from the excel document would be:

Expand|Select|Wrap|Line Numbers
  1. Cells.Select
  2. Cells.EntireColumn.AutoFit
I'm just not sure how to prefix it as simply putting that code in doesn't work.

Any ideas out there?????
Just subscribing to this unanswered Thread, will get back to you later on, hopefully with an answer. (LOL).
Feb 13 '08 #2

ADezii
Expert 5K+
P: 8,597
I have succesfully exported an Access report to Excel and then opened it, now i want to format it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3.     Dim Date1
  4.     Dim stReportAddress As String
  5.  
  6.     Date1 = Format(Date, "dd-mm-yyyy")
  7.     stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
  8.  
  9.     'Export Report
  10.     DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
  11.  
  12.     'Open Report
  13.     Application.FollowHyperlink stReportAddress
  14.  
  15. End Sub
Not sure how to perform the formatting now it is open...

I basically want to resize all the columns, create a header and place a few cells in bold.

Take resizing the columns, i know the code from the excel document would be:

Expand|Select|Wrap|Line Numbers
  1. Cells.Select
  2. Cells.EntireColumn.AutoFit
I'm just not sure how to prefix it as simply putting that code in doesn't work.

Any ideas out there?????
The following will be more than enough to get you on the right track:
Expand|Select|Wrap|Line Numbers
  1. 'The following code will open an Excel Spreadsheet via Automation,resize all Columns
  2. 'in the Worksheet, set the Size and Bold attributes for a specific Cell, then Exit
  3. 'while saving the changes
  4.  
  5. Dim Date1 As Date, strReportAddress As String
  6. Dim objActiveWkb As Object, appExcep As Object
  7.  
  8. Set appExcel = CreateObject("Excel.Application")
  9. appExcel.Visible = False
  10.  
  11. appExcel.Application.Workbooks.Open ("C:\Test\TestReport.xls")
  12.  
  13. Set objActiveWkb = appExcel.Application.ActiveWorkbook
  14.  
  15. With objActiveWkb
  16.    .Worksheets(1).Cells.Select
  17.    .Worksheets(1).Cells.EntireColumn.AutoFit
  18.    .Worksheets(1).Cells(5, 3).Font.Size = 14
  19.    .Worksheets(1).Cells(5, 3).Font.Bold = True
  20. End With
  21.  
  22. objActiveWkb.Close savechanges:=True
  23.  
  24. appExcel.Application.Quit
  25.  
  26. Set objActiveWkb = Nothing: Set appExcel = Nothing
Feb 13 '08 #3

P: 94
Thanks!! just what i was after, and no need for hyperlink to open the excel document.

One other question though.....(sorry!)
When performing the save it tells me that the file "is a Microsoft Excel 5.0/95 Workook. Do you want to overwrite it with the latest Excel format?"

I presume this has something to do with when the report is exported..... though i'm not sure where. I am running Office 2003 and don't understand why it would export the file in an older format.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatSNP, SnpRptAddressByEmpl
I have looked into acFormat but XLS is the only excel format.
The way i see it i can either somehow try to export the file in the latest format, or i can automatically have the code say "Yes" when the pop up appears.

Can you help?
Feb 13 '08 #4

ADezii
Expert 5K+
P: 8,597
Thanks!! just what i was after, and no need for hyperlink to open the excel document.

One other question though.....(sorry!)
When performing the save it tells me that the file "is a Microsoft Excel 5.0/95 Workook. Do you want to overwrite it with the latest Excel format?"

I presume this has something to do with when the report is exported..... though i'm not sure where. I am running Office 2003 and don't understand why it would export the file in an older format.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatSNP, SnpRptAddressByEmpl
I have looked into acFormat but XLS is the only excel format.
The way i see it i can either somehow try to export the file in the latest format, or i can automatically have the code say "Yes" when the pop up appears.

Can you help?
Try substituting "MicrosoftExcelBiff8(*.xls)" for acFormatXLS
Feb 13 '08 #5

P: 94
Unfortunately that has made no difference...
Feb 14 '08 #6

ADezii
Expert 5K+
P: 8,597
Unfortunately that has made no difference...
You got me stumped for now, sorry.
Feb 14 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.