VBA to Format Exported Excel Data

[rlsublime]

I have the following do cmd code that will export my query to an excel file. I am trying to edit the format of the file within the code itself. How would I be able to do that?Thanks



DoCmd.OutputTo acQuery, "Investor_Request_Query", "MicrosoftExcel 97-10(*.xls)", "C:\Investor Request Report.xls", True, ""

[pbaldy]

In order to format, you would use automation:

http://support.microsoft.com/kb/247412

[rlsublime]

Thanks still not sure how to implement the following line. The link that you posted has a section on creating a new query within the code but what i am trying to do is format the Repurchase_Report excel file. Thanks

DoCmd.OutputTo acQuery, "Repurchase_Query", "MicrosoftExcel 97-10(*.xls)", "C:\Repurchase_Report.xls", True, ""

[pbaldy]

I deleted your duplicate thread. You would use automation to open the file created by your OutputTo code, and apply your desired formatting.

[rlsublime]

I am not sure how to select "C:\Repurchase_Report.xls" in the code and make formatting changes to the sheet. Any ideas?

[pbaldy]

I happen to be working on an Excel automation project at the moment. Here are the relevant lines (strFile contains the full path to the file):

Dim xl As Object
Set xl = CreateObject("excel.application")

xl.Workbooks.Open (strFile)

I don't know what formatting you're trying to do, but a handy trick I use is to record a macro in Excel doing the things you want. Look in the VBA editor to see the code that got created. It will give you the shell of what you need to do from Access.

[rlsublime]

Thanks. I tried the following code and was able to generate the report, however, I was not able to successfully format the sheet. For example in the code below, the ‘Columns("A:A").EntireColumn.AutoFit” code did not work and gave me a message “Sub or function not defined. Thanks for the help.

 
Dim xl As Object
Set xl = CreateObject("C:\Repurchase_Report.xls")
DoCmd.OutputTo acQuery, "Repurchase_Query", "MicrosoftExcel 97-10(*.xls)", "C:\Repurchase_Report.xls", True, ""
Activesheet.select
Columns("A:A").EntireColumn.AutoFit
xl.Workbooks.Open (C:\Repurchase_Report.xls)

[pbaldy]

When automating from Access, you need to include the Excel object:


xl.Columns("A:A").EntireColumn.AutoFit

Also, I think you want to do the OutputTo before opening the file for formatting (or do the entire process via automation).