Macro to export in comma delimited text format

[TonyTheo1]

Hi. I'm trying to create a macro that exports only one of my worksheets (worksheet "GTG") to a comma delimited text file named "GTG_Template". Pasting my current attempt below, but it doesn't work right, seems to save a text file but no delimit character and I think it's saving all worksheets. I've entered the path where I want the file to save to on a worksheet named Menu in cell B8. I've entered the name I want the file saved as (excl the extension) in cell B9.

Sub CreateTextFile

Dim sMaster As Workbook
Dim sMenu As Worksheet
Dim sGTG As Worksheet
Dim txtPath As String

Set sMaster = ThisWorkbook
Set sMenu = sMaster.Worksheets("Menu")
Set sGTG = sMaster.Worksheets("GTG")


txtPath = sMenu.Range("B8") & sMenu.Range("B9")


ActiveWorkbook.SaveAs Filename:= _
txtPath & ".txt", FileFormat:= _
xlText, CreateBackup:=False, TextVisualLayout:=Chr(44)

End Sub

 

[ztodd]

Keep in mind you can press F1 while your cursor is on a command to get help with that command. Do that with the SaveAs command.

The format you're looking for is known as CSV - it stands for Comma Separated Values.

 

[TonyTheo1]

Thanks for the tip, I'll check it out. Hopefully someone also has some code that does this or knows how to write one and can help me out. Also, I actually want it as a .txt file, so I don't think I can use csv.

 

[ztodd]

Actually, a CSV file IS a text file. Like I said, it stand for comma separate values- it's just plain text, with the columns separated by commas- just like you want.

Don't be afraid to experiment, to figure things out by trial and error. Just make a backup of your file first, then you don't need to worry about messing it up too bad to fix it.

Trust me, you'll help yourself out in the long run if you learn to research and use trial and error to code and debug what you need. That's the same way we do it.

 

[TonyTheo1]

Tried it out and I think it worked using xlcsv instead of xltxt. Thanks again. I modified slightly and it seems to work.

Sub CreateTextFile()


Dim sMaster As Workbook
Dim sMenu As Worksheet
Dim sGTG As Worksheet
Dim txtPath As String


Set sMaster = ThisWorkbook
Set sMenu = sMaster.Worksheets("Menu")
Set sGTG = sMaster.Worksheets("GTG")


txtPath = sMenu.Range("B8") & sMenu.Range("B9")


ActiveWorkbook.SaveAs Filename:= _
txtPath & ".txt", FileFormat:= _
xlCSV, CreateBackup:=False


End Sub

 

[ztodd]

Great! Awesome to hear when people figure out the puzzle for themselves.

 

[jimrward]

as a further addition to the above you can use the following lesser known syntax, this uses the separator character that you specify in the language settings for your PC, so you can set it to a vertical bar or a tilde in the language and this will be honoured, without the local :=true, it will be comma delimited

ActiveWorkbook.SaveAs Filename:=SaveFile, FileFormat:=xlCSV, CreateBackup:=False, Local:=True