Save workbook as two separate formats (CSV and XLSX) and leave XLSX open

I have a macro that creates a report in a new unsaved workbook.

1. I want the workbook to be saved as XLSX format - and remain open after saving.
2. While saving the XLSX, I also want a copy of this workbook to be saved in CSV format (without any warnings about possible data loss or incompatibility with CSV format)(I accept the risk for this particular report)
3. After the macro has finished running, I should end up with a CSV and a XLSX saved in the same location (Documents path) and only the XLSX file should be open.
4. The macro should run with no prompts (the location and filenames will be preset by the macro)

I think I can handle writing all of this except for the CSV aspect. Help would be appreciated.

(I want two formats because XLSX is more readable - with tables and filters - but I need a copy in CSV for importing to other programs)



UPDATE:
My post wasn't as clear as I thought it was. Clarifying the above requirements:

• The macro exists outside of the workbook that is generated. "I have a macro that creates a report in a new unsaved workbook".
• The XLSX needs to retain all of its formatting. The CSV will have to be created from a copy of the open workbook. "XLSX is more readable - with tables and filters - but I need a copy in CSV for importing" & "(without any warnings about possible data loss or incompatibility with CSV format)(I accept the risk for this particular report)"

Maybe something like this. I saved the excel file as a macro enabled file as you need to store the macro somewhere.

Please Login or Register  to view this content.

Thanks for the suggestion Alan.

Originally Posted by alansidman

I saved the excel file as a macro enabled file as you need to store the macro somewhere

The macro and the output report are in separate files. (As my opening post "macro that creates a report in a new unsaved workbook")

When I run the macro, it produces a report in a new workbook. It's this new unsaved workbook that I want to save as a XLSX and CSV.

Also you are using

Please Login or Register  to view this content.

to save as CSV before saving as XLSX. Saving a workbook as a CSV will cause it to lose anything that isn't compatible with this format - such as formatting and tables. As noted in the opening post, I want a copy of the workbook to be saved as a CSV. I don't want to lose formatting from the XLSX file.

How about just using the Macro Recorder and then post here for changes that are required to make it more dynamic.

I think I made my original question too specific.

I'll reframe the question: Suppose I have VBA referencing a XLSX file that is currently open (Assume that the XLSX has already been saved). I want:

1. a copy of the open XLSX saved as a CSV file (It doesn't matter if the CSV file will lose formatting or anything incompatible with the CSV format)
2. without any prompt for where the CSV will be saved (The CSV should be in the same folder as the XLSX. This will be application.defaultuserpath if that helps. Filename will be preset. Call it Import.csv)
3. without any warning message re feature loss in the CSV
4. without needing to close the XLSX at any point
5. without losing any data or formatting from the open XLSX

Copy the workbook
Save the copy as CSV
Close the copy
Save the original as xlsx

Please Login or Register  to view this content.

Thanks AlphaFrog. I will test this code in the next few days and let you know how it goes (and if it solves it, will rep you ofc)

(BTW there is a minor error but I can handle it. The last line is saving XLSM format not XLSX)

Sorry AlphaFrog but I couldn't get your code to work. I get a Runtime error 438 on the line

Please Login or Register  to view this content.

I have since solved all of the outcomes in the opening post so I am closing the ticket. (I would have preferred to avoid close & reopen the XLSX but it doesn't seem to be possible to avoid doing that. Unless somebody can offer a different solution?)

relevant code extract below:

Please Login or Register  to view this content.