Save excel file as text file with ; as separator

[Eva]

Dear Sir

I change the list separator(under control panel) from " , " to " ; " in win2000 and winxp. I write macro that will save the excel file as text file with ';' separator but the result is still a ',' but this program can work on win98. The coding is as below

Worksheets("ProdPrice").Activat
Cells.Selec
Selection.Cop

Workbooks.Ad
Windows("Book1").Activat
Range("A1").Activat
ActiveSheet.Past
ChDir "C:\
ActiveWorkbook.SaveAs Filename:="C:\ClientInfo\Prodprice.txt",
FileFormat:=xlCSV, CreateBackup:=Fals
Windows("Prodprice.txt").Close SaveChanges:=Fals

Please give me any advice to solve it

Thanks a lo
Eva

 

[Bernie Deitrick]

Eva,

Copy the macro below into your codemodule, then remove your line starting
with

ActiveWorkbook.SaveAs Filename:="C:\ClientInfo\Prodprice.txt", _

and replace it with

ExportToSemiCSV "C:\ClientInfo\Prodprice.txt"

HTH,
Bernie
MS Excel MVP


Sub ExportToSemiCSV(FName As String)

Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If WholeLine = "" Then
WholeLine = Cells(RowNdx, ColNdx).Text
Else
WholeLine = WholeLine & ";" & Cells(RowNdx, ColNdx).Text
End If
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

Dear Sir,

I change the list separator(under control panel) from " , " to " ; " in

win2000 and winxp. I write macro that will save the excel file as text file
with ';' separator but the result is still a ',' but this program can work