How to export from Access to Excel with the columns set to autofit for width? (Brand new to access!)

[StreetFighter]

Hello,

I apologize if this is a dumb question as all this is new to me.

All I'm wanting to do is have Microsoft Access export a query to Microsoft Excel with the columns autofit for width. I have seen that it can be done, but I'm far from understanding everything just yet. I've created a test database for myself and so far have the following code:

Private Sub Export_Selected_Data_to_Excel_Button_Click()
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Export Selected Query", Environ("USERPROFILE") & "\Desktop\ExportedData.xlsx", True
    


End Sub

I know it is simple as I am very new to Microsoft Access and coding/VBA at all. Right now I am happy as I have learned to export the query from my table to my desktop and it looks great so far.

I would love to have the columns autofit to width, and also if it is possible I would love to have the top row frozen? I know that's asking a lot! I'm hoping someone can help!

Thank you so much for your time!

[ranman256]

try:
DoCmd.OutputTo acOutputTable, "qsMyQry", acFormatXLSX, "c:\temp\myufile.xlsx"

tho it has less parameters on sheet names and multiple tabs.

[StreetFighter]

Hello Ranman256!

Thanks for helping me out! I see you helped other people and just want to say thanks first of all!

Do I copy past that exactly into my access code? It gives me an error when I do. Or do I have to change something to fit my own databse?



Would I change the "qsMyQry" to the Query name I'm using? I'm very new to this so I'm sorry if this sounds dumb! Below is how I have it at the moment:

[Welshgasman]

You would need to run some excel automation code on the exported data to autofit etc.
You can record a macro in excel to do just that and amend to suit.

[StreetFighter]

Okay I guess should take my time and learn more as this seems above my head at this point. I have only learned any code or SQL or VBA as of two weeks now I was hoping the solution to autofit the columns was a simple one! I think for now I'll just manually autofit and freeze the top row in the exported excel file as it isn't too much trouble I was just hoping to automate it which would have been icing on my novice coding cake.

Thank you!

[Micron]

googling "docmd.outputto" brings you to the likes of

https://learn.microsoft.com/en-us/of...docmd.outputto

Pretty sure that the first suggestion applies to where you have an Excel file with appropriately sized columns that you will output to. It may or may not be appropriate for your needs. Unfortunately those are not exactly clear.

[Minty]

To provide formatting you would need to open the excel file from Access after you have created it, and run some VBA code.
There is no way to do it automatically without using some VBA beyond that.

However it's not really that difficult, it just requires some effort to understand the syntax required.

I have a routine that takes any query and creates a Excel table from the results.
It is not overly complicated. I don't have the code to hand (I'm on a new computer) but suspect I have posted it on here somewhere.

[CJ_London]

See this link
https://www.accessforums.net/showthr...t=Export+excel

Post #4 has code to export and format - for resizing columns look for ‘autofit’ in the code