Complex SaveAs and Kill Macro Help

[Sh0ck3d]

Hello folks! Long time lurker but finally decided to ask some questions here since I see you guys have an awesome community...!

I have a somewhat interesting project where I am taking badly formatted data from an export to .XLS, converting it to .CSV format (to strip the crazy groupings and other issues), manipulating the formatting again for column headers and other settings, then saving it again in .XLSX format. I only really need the final output of the .XLSX file, and I want to save it to a specific folder but have the name based on the existing sheet name or file name...!

Currently it basically works (by running the macro from a .XLSM file), but only deletes the generated .CSV file and leaves the original garbage .XLS file there. The files are also saved in the Documents folder (default) and I cannot figure out how to save all the files into a subfolder. I have tried using the following for the path: "%homepath%\documents\ExcelReports" but I could never get this to function with generated file names. Maybe my syntax is just wrong, or I am going about this the wrong way... If anyone can help or make comments it would be much appreciated!

I have cut and spliced and tested to the best of my ability and ended up with this:

Please Login or Register  to view this content.

[JBeaucaire]

Perhaps all this missing it the path to the file? The kill command is being fed a filename, but probably not the path TO that file.

[Sh0ck3d]

Perhaps all this missing it the path to the file? The kill command is being fed a filename, but probably not the path TO that file.

Wow that was FAST! Thanks for the reply. I would agree, but the Kill command does function for the sCSVName file, though it does not have a path defined...? I would love to specify the path and then reference the file name, but as I mentioned I have yet be able to specify the SaveAs location of the files while letting it base the file name on the ActiveSheet or ActiveWorkbook name...

[JBeaucaire]

Declare a string variable, put the path you want to use in that variable, then you can use it throughout the rest of your code.

Since you always want to delete these files when you're done, there's no reason to fiddle with filenames either. Just pick a "junk" filename and use it for saving, opening and deleting.


snippets

Please Login or Register  to view this content.