CODEWITHSUNDEEP
vbaexcel
Sam Mitchell
Sam Mitchell

Reputation: 29

Code changes date format when saving as a text file

I have written code to save each worksheet in a workbook individually as text files. If I do this manually using the Text (Tab delimited) option from the Save As type dropdown, the text file retains my original formatting of time and UK date, i.e. 00:15 29/12/2001. The system I am using afterwards to import the data from the text files requires it to be in exactly this format. When I run the macro to export the data, the text files produced lop off the leading zero from the time, and flip the date to the US format, i.e. 0:15 12/29/2001, which causes the subsequent system to fail over.

Why is the text file OK when I do it manually but not when the macro does it? How do you get around it? The code I am using is below:

Sub SaveSheetsAsText()

Dim sFile As String
Dim sht As Worksheet

For Each sht In Worksheets
    sht.Copy
    sFile = sht.Name & ".txt"
    ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlText, CreateBackup:=False, Local:=True
    ActiveWorkbook.Close SaveChanges:=True
Next sht

End Sub

UPDATE: Some more info about the spreadsheet:

Time is in column A - formula bar shows 00:15:00 but cell is formatted to display just 00:15

Date is in column B - formula bar shows 29/12/2001, cell displays the same

Actual data is a numerical value in column C, e.g. 2.497 - no problems with this column.

I've had a look at the link that was posted, but it's over my head.

Upvotes: 1

Views: 3811

Answers (3)

Oscar Leal Díaz
Oscar Leal Díaz

Reputation: 21

Is an issue quite old, but the solution that works only takes 1 line: Put these before saving, on the column you need.

Columns("D:D").NumberFormat = "dd/mm/yyyy"

It really worked for me. Regards

Upvotes: 2

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

I'm just guessing if I could help... 

Sub SaveSheetsAsText()

Dim sFile As String
Dim sht As Worksheet

For Each sht In Worksheets

'myImplementation
    Dim rng As Range 'always is better to set te var in the beginning
    Dim r
    Dim c
    Dim i
    Dim t

    r = Range("A1").End(xlDown).Row 'take the row of the last cell in column A
    c = 1 'Column A 'Just take the number of the column

    Set rng = Range(Cells(1, 1), Cells(r, c)) 'Set the range with the dates

    For Each i In rng 'for each date in the range
        t = i.Text 'Store the TEXT (not the date) inside the var
        i.NumberFormat = "@" 'Set the format of the cell to Text
        i.Value = t 'set the value of the cell to the text of the date
    Next i 'next one please!
'myImplementation

    sht.Copy
    sFile = sht.Name & ".txt"
    ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlText, CreateBackup:=False, Local:=True
    ActiveWorkbook.Close SaveChanges:=True
Next sht

End Sub

As i told you in the comment: if you got a date in the cell, when you see the formula bar you see the unformat date (the raw content: 1/19/2016 9:00:00 AM) but in the cell you format that content as a date, number or anything else. If you see in the cell this 08:23 21/01/2016 as in the formula bar, you don't have a date you have a text inside de cell.

Your code goes to every sheet, but before save anything, change the date to a text... this works for me, but please tell me if this not work to impove it.

Upvotes: 0

jkpieterse
jkpieterse

Reputation: 2956

Unfortunately VBA speaks "American" which means that as soon as you have VBA do a save as text it will use the US format for dates. The only way around this is to completely control the creation of the textfile in VBA. Luckily, you're not the first to encounter this, see: http://dailydoseofexcel.com/archives/2004/11/09/roll-your-own-csv/

Upvotes: 0

Related Questions