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:
[code=vb]
'The following code will open an Excel Spreadsheet via Automation,resi ze 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, strReportAddres s As String
Dim objActiveWkb As Object, appExcep As Object

Set appExcel = CreateObject("E xcel.Applicatio n")
appExcel.Visibl e = False

appExcel.Applic ation.Workbooks .Open ("C:\Test\TestR eport.xls")

Set objActiveWkb = appExcel.Applic ation.ActiveWor kbook

With objActiveWkb
.Worksheets(1). Cells.Select
.Worksheets(1). Cells.EntireCol umn.AutoFit
.Worksheets(1). Cells(5, 3).Font.Size = 14
.Worksheets(1). Cells(5, 3).Font.Bold = True
End With

objActiveWkb.Cl ose savechanges:=Tr ue

appExcel.Applic ation.Quit

Set objActiveWkb = Nothing: Set appExcel = Nothing[/CODE]

[Lewe22]

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

One other question though.....(sor ry!)
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.....(sor ry!)
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 "MicrosoftExcel Biff8(*.xls)" for acFormatXLS

[Lewe22]

Unfortunately that has made no difference...

[ADezii]

Unfortunately that has made no difference...

You got me stumped for now, sorry.