Save as text file weirdness

  • Hi!

    I am learning VBA and have a Q for the forum. When using this code

    Sub SaveFileAs()
    Dim stFpath As String
    Dim stFname As String
    'Folder to store file
    stFpath = "C:\JBStat\"
    'Name of workbook
    stFname = Range("B1").Value & ".txt"
    ActiveWorkbook.SaveAs stFpath & stFname, FileFormat:=xlText, CreateBackup:=False
    End Sub

    Really simple as you can see...I get a different formating inside the file (the dates in column two gets 5/14/2004 instead of (the way I want) 2004-05-14 as I get when I use the macro recorder and get this code.

    Sub Makro2()

    ActiveWorkbook.SaveAs Filename:="C:\JBStat\20040514.txt", FileFormat:= _
    xlText, CreateBackup:=False
    End Sub

    Examples of the outcome of the different macros.

    Sub SaveFileAs() 1 5/14/2004 10:26 1 91-0010309-8

    Sub Makro2()1 2004-05-14 10:26 1 91-0010309-8

    BTW I use XP and Swedish version of XL 2003.

    Any formatting experts out there? TIA :yes:

    Have a great weekend all! /Mats

  • Thanks Derk, I am sorry if I explained the problem poorly, but the filename formating is ok. It's the formatting inside the file that gets messed up when using the VBA solution instead of the the menu/Macro recorder version.

    When using Sub SaveFileAs() a line inside the file looks like this.
    1 5/14/2004 10:26 1 91-0010309-8

    When using Sub Makro2() the same line looks like this.
    1 2004-05-14 10:26 1 91-0010309-8

    Could it be something with VBA formating and my own Swedish formating options??

    Again, Derk thanks for your effort!


  • Hi Mats,

    Nice to see that You have started to learn VBA as well :)

    Welcome to the world of international-issues! MS have not done their whole homework very well and we need to do some workarounds for the lack.

    Anyway, when doing it from Excel it reads the windows settings and apply the regional dateformat upon saving.

    VBA is only based on US-settings which means that when You do the same actions in VBA You get the output based on US-settings and nothing else.

    So below You find a revised version of Your procedure where we add an apostrophe in front of each datevalue beforing saving to textfile. The apostrophe preserve the dateformat and is not added to the textfile:

    :viking: Dennis

  • Thanks Dennis!

    You are THE MAN! Thanks for your kind help! It works just the way I want it.

    I have serched all over for information about this but found none. Is there more "international" oddities in VBA?

    This is a part of a project where I use SQL Tester to read sales statistics from an Sybase SQL database and make a textfile for uploading to our central database.

    SQL Tester works perfectly, I´ll swear I never gonna use M$ Query again.

    Tack så mycket! (Thank you very much in swedish)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!