Transferspeadsheet and autofit columns

[ralphyehle]

I am using Office 2010 Access and Excel software. And use the TransferSpreadsheet method in VBA to export data from an Access database into multiple spreadsheets located in workbook files. Can I autofit the columns of my spreadsheet as part of this VBA code? Or can I include a VBA module to do the autofit within the newly created workbooks? I don’t want to have to open each workbook, there are 34 of them with up to 9 spreadsheets in each and we recreate them once each month. They are created with a loop in an Access form module.

How would I make a copy of a preformatted Excel workbook, save it to a new folder location and change the file name, and
Create empty formated excel files with the same sheet names (all created queries are named as that name, so they are exported to the same named sheet). Before each export create a copy of that file using the filename you are about to export and then export.

something like:
rs.Fields(0)="County05.2011"
empty excel file = "myCounty05.2011.xls"
formated sheet name "County05.2011"
copy that as "file County05.2011.xls" --> FileCopy "C:\myCounty05.2011.xls", "C:\file County05.2011.xls"
Export to "X:\MyLocation\County05.2011.xls"

Then have VBA run within the workbooks to autofit columns when they are opened. Something like:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Columns.AutoFit
End Sub
If this would work, how would I have this autofit run on all 9 spreadsheets?

 

[boblarson]

Well, one way would be to use code like I have in this file here:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=37197&d=1305294370
or once they are done, have the code iterate through, open them do the autofit and then close.

But if you do that, be aware of this:
http://www.btabdevelopment.com/ts/excelinstance