I
I.T.
Would be obliged for any ideas in relation to the following:
I have a fairly large vba routine which separates data and copies i
into between 2 and 10 different worksheets. The remainder of the cod
runs perfectly.
The data is made up of four columns
data1 data2 date time
The data is imported from a text file (format dd:mm:yyyy) and displaye
in the format dd/mm/yyyy. This format is then copied into th
respective worksheets and is still displayed as dd/mm/yyyy.
The code then saves each sheet to a csv file using the following code:
If Worksheets("Data").Range("K" & c + 5) <> "" Then
xlsfpath = Worksheets("Data").Range("Q5").Value
Sheets("No" & c & "up").Select
ActiveWorkbook.SaveAs FileName:=xlsfpath
Worksheets("Data").Range("O" & c + 5).Value & ".csv"
FileFormat:=xlCSV, CreateBackup:=False
Sheets("No" & c & "down").Select
ActiveWorkbook.SaveAs FileName:=xlsfpath
Worksheets("Data").Range("P" & c + 5).Value & ".csv"
FileFormat:=xlCSV, CreateBackup:=False
End If
taking the filename from a cell on the master sheet ("Data") if ther
is data to be written. This loops through to write up to ten .csv file
to disk.
The code works perfectly apart from the date aspect which outputs th
third column as m/d/yy.
Ironically if I do a manual File>Save As> *.csv for each sheet the dat
is output perfectly as dd/mm/yyyy.
My computer has the international settings as UK and the formatting fo
the column concerned is correctly set to dd/mm/yyyy. It is only whe
the vba does the job that it goes wrong.
As I said earlier any help would be much appreciated.
Iain Thomso
I have a fairly large vba routine which separates data and copies i
into between 2 and 10 different worksheets. The remainder of the cod
runs perfectly.
The data is made up of four columns
data1 data2 date time
The data is imported from a text file (format dd:mm:yyyy) and displaye
in the format dd/mm/yyyy. This format is then copied into th
respective worksheets and is still displayed as dd/mm/yyyy.
The code then saves each sheet to a csv file using the following code:
If Worksheets("Data").Range("K" & c + 5) <> "" Then
xlsfpath = Worksheets("Data").Range("Q5").Value
Sheets("No" & c & "up").Select
ActiveWorkbook.SaveAs FileName:=xlsfpath
Worksheets("Data").Range("O" & c + 5).Value & ".csv"
FileFormat:=xlCSV, CreateBackup:=False
Sheets("No" & c & "down").Select
ActiveWorkbook.SaveAs FileName:=xlsfpath
Worksheets("Data").Range("P" & c + 5).Value & ".csv"
FileFormat:=xlCSV, CreateBackup:=False
End If
taking the filename from a cell on the master sheet ("Data") if ther
is data to be written. This loops through to write up to ten .csv file
to disk.
The code works perfectly apart from the date aspect which outputs th
third column as m/d/yy.
Ironically if I do a manual File>Save As> *.csv for each sheet the dat
is output perfectly as dd/mm/yyyy.
My computer has the international settings as UK and the formatting fo
the column concerned is correctly set to dd/mm/yyyy. It is only whe
the vba does the job that it goes wrong.
As I said earlier any help would be much appreciated.
Iain Thomso