Exporting excel to semicolon delimited text file

[jnbradl]

Good Afternoon.

I am wanting to take a semicolon delimited dat file, open it in excel, and save it as a semicolon delimited file. I dont believe I can just change the regional setting due to the fact it needs to have quotations around the data. Ex: "123";"test";"123";"test" Is their a Macro that I can run in order to accomplish this? I have a very limited knowledge of VB. Thanks in advance.

 

[AlphaFrog]

Here's a thread with a macro that should work...

Re: to save a file in "text" format with ";"delimiter

 

[jnbradl]

Thank you for your response. That is partly correct, but how would I get it to output the quotations around the value as well? My data has to have the "value";"value" format with the quotations included? Thanks again.

 

[jnbradl]

This Code from the link might help others answer this as well, so here it is.....

Public Sub save_as_text_woth_Delimiter()

Const DELIMITER = ";"
Const MYFILE = "C:\Users\Bradley's\Desktop\test.txt"

Dim Last_Column As Integer
Dim Last_Row As Long
Dim Row_Loop As Long
Dim Column_Loop As Integer
Dim FileNum As Integer

FileNum = FreeFile

If Dir(MYFILE) <> "" Then
If MsgBox(MYFILE & " will be deleted if you click OK", vbOKCancel) = vbCancel Then
MsgBox ("Exiting Code")
End
End If
End If

With ActiveSheet.Cells
Last_Column = .Find("*", [A1], , , xlByColumns, xlPrevious).Column
Last_Row = .Find("*", [A1], , , xlByRows, xlPrevious).Row
End With


Open MYFILE For Output As #FileNum

For Row_Loop = 1 To Last_Row
For Column_Loop = 1 To Last_Column
Print #FileNum, ActiveSheet.Cells(Row_Loop, Column_Loop).Value & DELIMITER;
Next Column_Loop
Print #FileNum,
Next Row_Loop

Close #FileNum

End Sub

 

[Burrgogi]

Good Afternoon.

I am wanting to take a semicolon delimited dat file, open it in excel, and save it as a semicolon delimited file. I dont believe I can just change the regional setting due to the fact it needs to have quotations around the data. Ex: "123";"test";"123";"test" Is their a Macro that I can run in order to accomplish this? I have a very limited knowledge of VB. Thanks in advance.

How about providing a little more detail? For instance, it would helpful to know if the values (or words) are contained in separate cells or if it's a string contained in 1 single cell.

 

[AlphaFrog]

To surround with quotes, In the macro...

Change this line...

Print #FileNum, ActiveSheet.Cells(Row_Loop, Column_Loop).Value & DELIMITER;

To this...

Print #FileNum, [COLOR="Red"]"""" &[/COLOR] ActiveSheet.Cells(Row_Loop, Column_Loop).Value & [COLOR="Red"]"""" &[/COLOR] DELIMITER;

 

[jnbradl]

AlphaFrog - That was exactly what I needed. Thank you

Just to answer your question, Burgoggi, the values were all in separate cells. I hope this can help others in the future.

Thanks again.