Macro to Save As in Two Formats (Including Date in File Name)

[DeadSpace4]

Hey all, this is my first post here so please bear with me.

I am attempting to create a macro that will be used in hundreds of very similar Excel files. The files are for a sales replenishment system and most of the information will be the same from week to week. However, the store number to which the order is to be shipped will change and it is this cell that needs to be referenced in the file name along with the date that the purchase order is being completed.

Additionally, the file needs to be saved as both a .xlsm format (to be used again the following week) AND a Tab Delimited format, as the replenishment software will only recognize a Text file.

The ultimate goal here is to create 400+ spreadsheets that will be filled out manually each week with the store numbers, PO numbers, date, etc and then all the quantities that are to be shipped to each store. To expedite the process of exporting 400+ Tab Delimited Text files to their proper location I need to create this Save As macro.

The file name needs to be something similar to "832-11.8.2011.txt". Additionally, and hopefully within the same macro, I can get it to save a copy with the same file name but in a Macro Enabled XLS file as well.

I hope I am not being to confusing here.

This is what I've got so far for the macro:



Sub SaveAsTDTXTFile()
'
' SaveAsTDTXTFile Macro
'
' Keyboard Shortcut: Ctrl+e
'
ThisFile = Range("O1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile

End Sub



As you can see, I need to get the current date into the name of the file as well as having the lines for saving it as both the .txt file and the .xlsm file. I would also like to use the FileFormat numbers so that the macro is recognizable to versions before 2007.

Eventually, I will also need to have the .txt file saved to a different location (a network drive on our VPN) and the .xlsm file to the local hard drive. But for now I can settle for both being on the local drive.

Thanks in advance.

 

[Smitty]

Welcome to the Board!

You can use this to get the date:

ThisFile = Range("O1").Value & "-" & Format(Date,"mm.dd.yyyy")

As for the different formats/locations you can record a macro for that.

HTH,

 

[DeadSpace4]

Great! Thanks.

However, it's not including the file extensions so when it goes to save the second copy of the file as a TD text I get an error saying that this file already exists.

Here is the macro as I have it now:




Sub Macro1()
'
' Macro1 Macro
'

'
ThisFile = Range("O1").Value & "-" & Format(Date, "mm.dd.yyyy")

ActiveWorkbook.SaveAs Filename:=ThisFile _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=ThisFile _
, FileFormat:=xlText, CreateBackup:=False

End Sub

 

[Smitty]

You can add the extension:

Range("O1").Value & "-" & Format(Date, "mm.dd.yyyy") & ",xls"

As for the File Exists message, you can suppress that with Application.DisplayAlerts = False. Just make sure to set it back to True at the end.

 

[DeadSpace4]

Do you happen to know the FileFormat number codes for the file formats I am trying to use? I currently use Excel 2010 but the version of Excel on our VPN is 2003 and therefore might not recognize the 2010 code.

Also, I added the XLS string and it only added "xls" as the end of the file name but not as the actual file extension.

 

[Smitty]

There's a typo in the extension I posted, it should be .xls, not ,xls.

2003 is FileFormat:=xlExcel8

 

[DeadSpace4]

Great, thank you.

As this project continues and the macro develops I am sure I will be back for more advice.