Editing an existing TXT file w/Macro

[cstimart]

I'm running in to an issue that has me perplexed.

I have an Access 2007 db that has a table that's populated from a TXT file and all works as needed.

However, as new part of the process, I added a portion of code to "strip out" some sensitive data from the TXT file and save it in another location, in case it's needed in the future.

It runs fine, but when I would try to import from the new/revised file, the import bombs. In viewing the import process in Access, some of the rows are being shown has having a leading double quote character....and I don't know why. I've opened the TXT file and all the cells in Column A are formatted as "general". I've compared the original and the new TXT files, and they look identical, except for the characters that I replaced with spaces.

Any ideas?

This import is only an issue for any back up purposes, as the original import is OK, as I "skip" the designated character range in the import process.

 

[james_lankford]

"I've opened the TXT file and all the cells in Column A are formatted as "general"."

open it in a text editor, not excel

if notepad won't work try notepad++
https://notepad-plus-plus.org/

 

[cstimart]

OK...I've reviewed the TXT file with notepad and see that some of there are quotes now included in the various rows.

So, in my code, I've tried to "replace" the quotes without any luck....any ideas?

Cells(i, "A") = Replace(Cells(i, "A"), Chr(34), vbNullString)

 

[xenou]

I wouldn't advise trying to edit text files with Excel. It's not a text editor and will do things "automatically" that you may not want done.

 

[cstimart]

I wouldn't advise trying to edit text files with Excel. It's not a text editor and will do things "automatically" that you may not want done.

So I can dump the Excel Reference? Is there a different reference and code that I could use rather than...

Set xlApp = New Excel.Application

With xlApp
    .Workbooks.Open "X:\TESTFILE.TXT"
        LR = Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To LR
                With .Cells(i, "A")
                    .Value = .Replace(.Value, Mid(.Value, 555, 9), "         ")
                    .Value.NumberFormat = "General"
                    .Value = .Replace(.Value, Chr(34), vbNullString)
                End With
        Next i
'Save TXT File
    With .ActiveWorkbook
        .SaveAs "X:\TESTFILE.TXT"
        .Close
    End With
End With
If Not xlApp Is Nothing Then Set xlApp = Nothing

 

[xenou]

I didn't realize you had this scripted. But you could for instance just use ordinary text file reading and writing methods, combined with your typical string functions and/or regex.

You could also import the data as is, and do the cleanup in Access. Which introduces a little bit of danger of the routine being intercepted. But you've already got that weakness - the data is also vulnerable there in Excel.

 

[cstimart]

I didn't realize you had this scripted. But you could for instance just use ordinary text file reading and writing methods, combined with your typical string functions and/or regex.

You could also import the data as is, and do the cleanup in Access. Which introduces a little bit of danger of the routine being intercepted. But you've already got that weakness - the data is also vulnerable there in Excel.

OK...thanks for the info!