Exporting to Excel

[JoeM]

I think I already know the answer to this one, but I am taking a short in the dark, because sometimes I am amazed at the functionality I am unaware of and the tricks people come up with.

So, I am creating an Access database that loops through a list of records, build some SQL code, applies it to a query, and exports the query to Excel (using TransferSpreadsheet). So when all is said and done, I end up with a whole bunch of Excel files.

My issue is that all these Excel files have their file widths set to the default 8.1 width, so you have to widen all the fields to actually read the data. Short of opening all the files after they are created in Excel and resizing the columns, is there any way to export the data to Excel and have it so the column-widths are auto-sized so people can read their files easily without having to adjust them themselves? This is going to be a bunch of managers, so the easier it is for them, the happier they are, and the better it is for me.



I know it is probably a long-shot, but thought that I would ask.

[aytee111]

So how about:
- take one of the files with the columns the correct size, delete all the data, save it as something else
- then in the code, copy this file to the new name before doing the export

[JoeM]

I am not quite sure I follow.
What would the VBA of your suggestion look like?

Note that the process is creating/exporting Excel files. It is not copying/pasting or anything like that.

[aytee111]

This is your statement:
that loops through a list of records, build some SQL code, applies it to a query, and exports the query to Excel (using TransferSpreadsheet).

Immediately before the last part, the export, create the file using FileCopy command. This is the file used in the export command.

[JoeM]

So let me see if I am understanding you correctly.

Are you saying to do the following:
- Create a blank Excel template file that has the column widths set as a like them
- Use the FileCopy command to copy the the blank template over to the name of the file I want to export to
- Export the query to the same file name that I just copied to

If so, I have tried it, and it does not appear to be working. My TransferSpreadsheet command does not seem to like to export to an existing Excel file.
Is there some trick in getting in getting TransferSpreadsheet to export to an existing file? It appears that it requires more than just having the existing file name in the File Name argument.

[aytee111]

You have been around long enough to know that "not working" is unacceptable!! What exactly is not working?

[aytee111]

But yes, that's exactly what I meant for you to do. Except how did you create the file - like I said?

[June7]

According to MS on TransferSpreadsheet: "Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook."

However, MS also says can edit linked spreadsheet with Access but I have never been able to do that.

[aytee111]

Haven't tested this, but June does Excel allow two worksheets with the same name?

[JoeM]

So, I was playing around with it some more, and discovered I made a mistake in making the changes, and that was the problem.
I was using the same variable for my SQL criteria and file name, and had added ".xlsx" to the end of it (needed for the FileCopy part).
When I did that, it changed my SQL criteria, so no records were being returned!

So I modified it, and it works like a charm now!

Thanks a lot for you help. I had never really used TransferSpreadsheet to export to existing Excel files before (always to new files), and didn't realize it was just that easy.

[JoeM]

According to MS on TransferSpreadsheet: "Access creates a new spreadsheet when you export data from Access. If the file name is the same as the name of an existing spreadsheet, Access replaces the existing spreadsheet, unless you're exporting to an Excel version 5.0 or later workbook. In that case, Access copies the exported data to the next available new worksheet in the workbook."

However, MS also says can edit linked spreadsheet with Access but I have never been able to do that.

Interesting, that doesn't appear to be the behavior I am experiencing.

My template has just 1 sheet named "Data", and I set the first three column widths.

Here is my export block, that copies the template then exports to that copy:

'       Copy Excel template to export location
        exportFile = exportPath & fName & ".xlsx"
        FileCopy exportTemplate, exportFile
        
'       Export query to Excel table
        DoCmd.TransferSpreadsheet acExport, 10, "qry75-Groups_Members_By_Mgr_EXPORT", exportFile, True, ""

It exports the data to that file, on the Data tab, and my column widths are what I set in my template. So it is not creating any new worksheets in my workbook.
(So it is working exactly as I would have hoped, though seems to be different than quote you cited).

[aytee111]

Now that is odd, I expected Excel to name the worksheet the same as the query name, hence my original instructions. But glad it is working.

[JoeM]

Now that is odd, I expected Excel to name the worksheet the same as the query name, hence my original instructions. But glad it is working.

It does when exporting to a new file, but apparently not when exporting to an existing one.

[aytee111]

THAT explains June's comment!

[JoeM]

THAT explains June's comment!

If it truly replaces it though, I wouldn't expect it to keep the old tab name or column formatting.
It seems to be acting more like it is opening it and pasted into the existing sheet (which is what I want, but did not expect it would do that).