VBA help, export from Excel to text file, insert delimiter

[wvdheever]

Morning all, I have compiled a piece of code which exports data in a worksheet to a text file with semi-colons between values.

This code inserts the semi-colon after each value that it exports. However, I do not want the code to insert the semi-colon after the value in the last column (to the right).

Please note that the number of columns differ. Some rows have data in only 1 column (i.e. A), while others might have data in 5 or 8 columns. What I need is that the value in the last column (to the right) of every row, after being exported, does not have a semi-colon inserted to the right of it. Thank you so much !



Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

' The delimiter can be set either before or after the cell value
sOut = sOut & myField.Text & DELIMITER

Next myField
Print #1, Mid(sOut, 1)
sOut = Empty
End With
Next myRecord
Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub

[大灰狼1976]

Hi wvdheever!
Welcome to vbax forum!
As your notes, "The delimiter can be set either before or after the cell value"
So I moved the delimiter to the front for easy handling.
something like below:


Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

' The delimiter can be set either before or after the cell value
sOut = sOut & DELIMITER & myField.Text

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub

[wvdheever]

Hi wvdheever!
Welcome to vbax forum!
As your notes, "The delimiter can be set either before or after the cell value"
So I moved the delimiter to the front for easy handling.
something like below:


Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

' The delimiter can be set either before or after the cell value
sOut = sOut & DELIMITER & myField.Text

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub


Thank you so much, it works perfect.

I have just discovered another bit of coding to be done. The macro should run through the whole of column M, if and where it finds text (as opposed to a number or a blank) in any cell, that text should be enclosed in double quotes (i.e. " on both sides of the text).

[大灰狼1976]

Hi wvdheever!
Like below:


Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String, qts$

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

' The delimiter can be set either before or after the cell value
If IsNumeric(myField.Text) Then qts = "" Else qts = Chr(34)
sOut = sOut & DELIMITER & qts & myField.Text & qts

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub

[wvdheever]

Thank you very much. However, your suggestion adds double quotes to every cell on the worksheet that contains text. It should only add double quotes to the cells that contain text in column M. :clap:

[snb]

Why don't you use:


Sub M_snb()
activesheet.saveas "C:\Users\Public\ConvertToText.dat",23
End Sub

[wvdheever]

Why don't you use:


Sub M_snb()
activesheet.saveas "C:\Users\Public\ConvertToText.dat",23
End Sub


Because when I open my original text file in Excel (to do some manipulation), Excel throws away the double quotes that are in column M (for all text cells in that column). The cells in column M contain mostly values (and some text) so when I open the text file in Excel, it sees all the cells in that column as numbers and discards the double quotes that are there. I need to get them back before exporting/printing to a text file.

[snb]

Please do not quote !


Sub M_snb()
for each it in columns(13).specialcells(2,2)
it.value=chr(34) & it & chr(34)
next

activesheet.saveas "C:\Users\Public\ConvertToText.dat",23
End Sub

[wvdheever]

Apologies.

[wvdheever]

snb, I incorporated part of your code into mine as per below. It works perfectly, except if I want to convert/export a file that has no text values in any cells in column M (which happens from time to time). I then get an error message when running the code.


Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String

For Each it In Columns(13).SpecialCells(2, 2)
it.Value = Chr(34) & it & Chr(34)
Next

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

sOut = sOut & DELIMITER & myField.Text

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub

[snb]

Why don't you use my code for 100% ?

[wvdheever]

The saved text file - in the cells, that contain values, in column M, I get a comma after the last value (-3), which I do not want.
ESTIMATE,2019,September,YTD,90425,USD,Other_Inc_Exp_IAS,[ICP None],[None],SA_GAAP_Disclose,[None],[None],-3,

And

In the text file, for the cells that do contain text in column M, it generates three double quotes on each side of the text (I only need 1 on each side):
ESTIMATE,2019,September,YTD,90425,USD,Other_Prep_Debit_Bal,[ICP None],[None],[None],[None],[None],"""Other prepayment""",12

[snb]

Please analyse the code.

[wvdheever]

Thank you.

[wvdheever]

Had a look at the code again but could not resolve the problem. I guess that is why I posted this issue on the forum in the first place. I do not have adequate knowledge.

[wvdheever]

I have a piece of code in which I manipulate text, imported from a text file, in Excel. One of the steps (in red below) is to find text (as opposed to values or blanks) in column M and put the contents of all these text cells in double quotes ("). Problem is, if there is no text in any of the cells in column M (which is possible), I get an error message. What I need is code to execute this step (in red) only if there are text in any cells in column M. If there are no text, just continue running the code, without giving an error message.

Tried this. Also not working...


Sub Export_Text()
Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String

With Columns(13)
If Application.CountA(.Areas(1)) > 0 Then
For Each it In Columns(13).SpecialCells(2, 2)
it.Value = Chr(34) & it & Chr(34)
Next it
End If
End With

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

sOut = sOut & DELIMITER & myField.Text

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly


End Sub

[大灰狼1976]

Sub Export_Text()

Const DELIMITER As String = ";"

Dim myRecord As Range
Dim myField As Range
Dim sOut As String, qts$

Open "C:\Users\Public\ConvertToText.dat" For Output As #1

For Each myRecord In ActiveSheet.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In ActiveSheet.Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))

' The delimiter can be set either before or after the cell value
If myField.Column <> 13 Or IsNumeric(myField.Text) Then qts = "" Else qts = Chr(34)
sOut = sOut & DELIMITER & qts & myField.Text & qts

Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1

MsgBox "C:\Users\Public\ConvertToText.dat has been created successfully", vbOKOnly

End Sub

[wvdheever]

:clap::clap::thumb:thumb