Problem with txt

[Guest]

Hi everyone!

I was wondering if you cound help with a problem I'm having with Excel. I
have a list of codes that I want Excel to save in txt format with the "".
I'll explain: I have thousands of codes in a numerical format (e.g. 3173471;
3174742, etc.) but I need to save a txt file that puts "" around the value,
i.e., "3173471", "3174742", etc. I've tried to use the concatenate function,
replace and even tried to program a macro (I'm not that proficient in macros,
so something could be wrong...) but nothing worked. The best i could was
using the replace function and, in fact, in Excel it looked as if everything
was correct, i.e., i saw "3174742", . However, when I saved it to txt I got
this weird result: """3174742""". I could try to make the replacement in word
or notepad, but given the huge amount of files I'm using this would be to
cumbersome.
Does anyone has an idea how I could get around this?

Cheers,

Nuno

 

[Bernie Deitrick]

Nuno,

Better to control exactly what gets written to the file using a macro. See
an example below.

HTH,
Bernie
MS Excel MVP

Sub ExportToTXT()

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

FName = "C:\Excel\ExportText.txt"

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

With Range("A2").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
WholeLine = WholeLine & " """ & _
Cells(RowNdx, ColNdx).Text & """ "
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

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


End Sub