Macro Help for complete novice!

[Newbie19]

Hi

First post for me.

I've got a couple of issues with a macro I have running.

The first is when I have the macro converting the xls file into a csv file, the csv file changes the date/time format that was present within the xls file i.e. if today it would save as 14-04-2013 00:00 etc, but the csv file converts it to 14/04/2013 00:00. I really need it to save in the same format. Is this an issue with all csv files?

Secondly, how can I get the macro to save the csv file with todays date i.e. Toga 14-04-2013.csv

Here is a copy of the macro I am running

Sheets("TOGA").Select
Columns("A:C").Select
Selection.Copy
Workbooks.Open Filename:="M:\Site Operations\Control Centre\Toga\Toga.xlsx"
Windows("Toga.xlsx").Activate
ActiveWindow.WindowState = xlNormal
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:= _
"M:\Site Operations\Control Centre\Toga\Toga.csv", FileFormat:= _
xlCSV, CreateBackup:=False

Any help greatly appreciated as I am stumped!

Cheers

 

[JLGWhiz]

The only way I know to stop the conversion of the date time format in the data fields is to make it text before saving. But, it might be easier to just get a macro to reformat it when you re-import back to Excel. I took the liberty of editing the "Selects" out of your code and added in the part that will put the current date in the file name when saved.

Sheets("TOGA").Columns("A:C").Copy
Workbooks.Open Filename:="M:\Site Operations\Control Centre\Toga\Toga.xlsx"
Windows("Toga.xlsx").WindowState = xlNormal
Windows("Toga.xlsx").Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:= _
"M:\Site Operations\Control Centre\Toga\Toga " & Format(Date, "dd-mm-yyyy") & ".csv", FileFormat:= _
xlCSV, CreateBackup:=False

 

[Newbie19]

Thanks JLGWhiz!

I'll persevere with the date format.

The strange thing is when the file is ready to be saved in the csv format, it is as shown as below -

ARCHW-1

16-Apr-2013 00:00

120

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

It is when I get prompted to save i get the following message -

"14-04-2013.csv may contain features that are not compatible with CSV"

Would this be why the date format then changes?

 

[JLGWhiz]

If you are not including the FileFormat parameter when you do the SaveAs, then it will give you that alert to tell you that Excel formats and .csv formats are not compatible. Look up FileFormat in the VBA help file for more information, or google 'FileFormat, VBA' and read the various articles on it. You can probably find some helpful information in the MrExcel FAQ page.

 

[Newbie19]

Hi JLGWhiz,

I have had a look around the forum as suggested and it seems that all may be ok. I opened up the saved csv file using notepad and date is displayed as I wish it to be. Could you verify then that when I send this csv file to its recipient, it will be received in the format desired?

Thanks again!

 

[JLGWhiz]

Hi JLGWhiz,

I have had a look around the forum as suggested and it seems that all may be ok. I opened up the saved csv file using notepad and date is displayed as I wish it to be. Could you verify then that when I send this csv file to its recipient, it will be received in the format desired?

Thanks again!

I have never created nor used a .csv file. All that I know about them comes from help files and what I read on this and other forums.