Save as text file weirdness

Mats Carlsson · May 14th 2004

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
:cool:

Anonymous · May 14th 2004

If I understand correctly, I think you want the line
stFname = Format(Range("B1").Value, "yyyy-mm-dd") & ".txt"

Mats Carlsson · May 14th 2004

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!

/Mats

Anonymous · May 14th 2004

You explained the problem quite well; I just read it too quickly. Now that I understand what happened, I have no idea why the two macros don't produce the same result.

XL-Dennis · May 14th 2004

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:

Code

Option Explicit


Sub SaveFileAs()
Dim wsSheet As Worksheet
Dim rnDate As Range, rnCell As Range
Dim stFpath As String, stFname As String


Set wsSheet = ActiveSheet
stFpath = "C:\Mats\"


With wsSheet
    Set rnDate = .Range(.Range("C1"), .Range("C65536").End(xlUp))
    stFname = .Range("B1").Value & ".txt"
End With
    
For Each rnCell In rnDate
    rnCell.Value = "'" & rnCell.Value
Next rnCell


ActiveWorkbook.SaveAs stFpath & stFname, FileFormat:=xlText
End Sub

Display More

:viking: Dennis

Mats Carlsson · May 25th 2004

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)

Mats

XL-Dennis · May 25th 2004

Mats,

Quote

Is there more "international" oddities in VBA?

Explore and You will find

Keep in mind that Excel per se is a US-software.

Kind regards,
Dennis

Save as text file weirdness

Participate now!

Share