Formatting Excel Export from Access

[Bremen217]

Hi All!

Just a quick question. I have VBA code exporting multiple queries to multiple tabs in a single Excel Workbook (Access 2007 user).

I have posted the code below.

I need to modify this code to do 2 formatting things:

1) Bold the headers on each sheet.
2) Autofit (or BestFit) all of the columns on each sheet.

I have the queries BestFit set when viewed as a datasheet, but it does not retain this setting upon export.

Thanks!
Mike

Private Sub Command162_Click()

    DoCmd.TransferSpreadsheet 1, 8, "US T1-DOMESTIC ARC List", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "US ARC List"

    DoCmd.TransferSpreadsheet 1, 8, "CA T2-IATA List", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "Canada IATA List"

    DoCmd.TransferSpreadsheet 1, 8, "US T3-IATAN Only List", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "US IATAN Only"

    DoCmd.TransferSpreadsheet 1, 8, "US T4-INTL IATA List", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "M&G INTL IATA List"

    DoCmd.TransferSpreadsheet 1, 8, "US T5-ARC-IATA Additions", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "ARC-IATA Additions"

    DoCmd.TransferSpreadsheet 1, 8, "US T6-ARC-IATA Deletions & Closures", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "ARC-IATA Deletions & Closures"

    DoCmd.TransferSpreadsheet 1, 8, "US T7-ARC-IATA Changes", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "ARC-IATA Changes"

    DoCmd.TransferSpreadsheet 1, 8, "US T8-CTD Info", "C:\Users\" & [Forms]![Main Menu]![userID] & "\Desktop\US Monthly Export.xls", True, "CTD Info"

   

End Sub

 

[llkhoutx]

TransferSpreadsheet merely transfers data, it does not format it. To format via Access, take a look "Access Excel Automation" to format your file after it's exported.

 

[Bremen217]

Thanks for your reply!

I know that the current script is just a transfer, I've been trying to find example code that will do both of the two formatting items after export that can be combined with the above script on click (i.e. transfer, then format the exported document).
I'll keep looking.
Thanks!
mike

 

[llkhoutx]

Again, take a look "Access Excel Automation" and my earlier response.

 

[Bremen217]

Thank you, I am doing so.

Not sure why the snarky was needed here though as I already mentioned I was still looking and thanked you for your direction/guidance.

Thanks!
Mike

 

[BigHappyDaddy]

Take a look at a few of the examples here:

http://btabdevelopment.com/code-snippets/page/2/

Exports your data and reformats.

 

[Bremen217]

Thank you BigHappyDaddy, that's exactly the starting point I needed and can modify as needed. Thanks so much!
Mike

 

[llkhoutx]

Locate Litwin's Acess Developer's Handbook, 2 volumes. There's 97, 2000 & 2002 editions. New on Amazon they're several hundred dollars. Used, they're reasonable.

I consider them "the Access bible" for learning anything about Access. The sections on automation are excellent. I've never seen that code anywhere else.