[Solved] Exporting: Export Access to Excel- modify column wi

  • I am exporting an Access file to Excel, and I need to modify the default column/cell width in Excel. Is there an Access option or Excel option to accomplish this? I did create a modified workbook template and saved in XLSTART folder, with no success.
    Thanks to all with any suggestions.
    Gary

  • HI Gary,


    Are you doing the export in VBA? Perhaps you could autosize the columns after the export is complete - -


    Columns("A:Z").AutoFit


    .....Ralph

  • Hi Ralph,
    Actually, we are exporting from access 97 to excel, (in Access: save query to external file, selecting the file type to be Excel 97, and exporting to a specified directory). As you can tell, this is not exactly my speciality.
    Thanks... :)

  • Hi Gary,


    Probably the easiest way is to autofit the columns after it is exported to excel.


    Open the new excel file
    Click on the small box to the left of the Col A heading and above the Row 1 heading (this will highlight/select the entire worksheet)
    Click on Format / Column / Autofit Selection
    That will make all the columns on the worksheet the correct size for the imported data.


    Hope this helps
    .....Ralph

  • Hey Ralph,


    That is what I was trying to avoid. With the quantity of reports we generate, I would like to have the columnth be dynamic, or at least have the default increased, to fit in the data without having to manually open each file.


    Thanks..... :)

  • Quote

    Originally posted by GarySmith
    Hi Ralph,
    Actually, we are exporting from access 97 to excel, (in Access: save query to external file, selecting the file type to be Excel 97, and exporting to a specified directory). As you can tell, this is not exactly my speciality.
    Thanks... :)



    How about using MSQuery to run the query in Excel as opposed to constructing it in Access and then exporting it. ?


    As you have Excel Open, you could automate the column width resizing using a little VBA once the dataset is returned to the worksheet...


    Any Good?


    Will

  • Thanks Will for the suggestion.
    We are actually using a front end to an Access DB, and generating a report that is then exported to Excel. I was hoping that there could be an excel or access setting to increase the column width, and avoid having to open the file and run a script for evey report generated, as we generate a fair number of these each day.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!