Format Excel document from Access

[Lewe22]

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

Private Sub Command2_Click()
 
    Dim Date1
    Dim stReportAddress As String
 
    Date1 = Format(Date, "dd-mm-yyyy")
    stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
 
    'Export Report
    DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
 
    'Open Report
    Application.FollowHyperlink stReportAddress
 
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:

Cells.Select
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?????

[ADezii]

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

Private Sub Command2_Click()
 
    Dim Date1
    Dim stReportAddress As String
 
    Date1 = Format(Date, "dd-mm-yyyy")
    stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
 
    'Export Report
    DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
 
    'Open Report
    Application.FollowHyperlink stReportAddress
 
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:

Cells.Select
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).

[ADezii]

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

Private Sub Command2_Click()
 
    Dim Date1
    Dim stReportAddress As String
 
    Date1 = Format(Date, "dd-mm-yyyy")
    stReportAddress = "c:\my documents\siobhan\Employer Funding Report - By Employer (" & Date1 & ").xls"
 
    'Export Report
    DoCmd.OutputTo acOutputReport, "Employer Funding Report (By Employer)", acFormatXLS, stReportAddress
 
    'Open Report
    Application.FollowHyperlink stReportAddress
 
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:

Cells.Select
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:

'The following code will open an Excel Spreadsheet via Automation,resize all Columns
'in the Worksheet, set the Size and Bold attributes for a specific Cell, then Exit
'while saving the changes
 
Dim Date1 As Date, strReportAddress As String
Dim objActiveWkb As Object, appExcep As Object
 
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
 
appExcel.Application.Workbooks.Open ("C:\Test\TestReport.xls")
 
Set objActiveWkb = appExcel.Application.ActiveWorkbook
 
With objActiveWkb
   .Worksheets(1).Cells.Select
   .Worksheets(1).Cells.EntireColumn.AutoFit
   .Worksheets(1).Cells(5, 3).Font.Size = 14
   .Worksheets(1).Cells(5, 3).Font.Bold = True
End With
 
objActiveWkb.Close savechanges:=True
 
appExcel.Application.Quit
 
Set objActiveWkb = Nothing: Set appExcel = Nothing

[Lewe22]

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.

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?

[ADezii]

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.

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

[Lewe22]

Unfortunately that has made no difference...

[ADezii]

Unfortunately that has made no difference...

You got me stumped for now, sorry.