Automating Notepad from Excel...

[bapcki1]

Hi,

I was wondering whether anybody can help me please?

Using Excel VBA, I need to:

1) Open a .txt file in notepad. (The file name is specified in an Excel cell.)

2) In notepad, find and replace " with '.

3) Re-save the .txt file.

4) Close notepad.


I am not looking for a script to import the .txt into Excel and then perform the find/replace on the imported. The find and replace should only take place in notepad.

Many thanks!


Paddy

 

[hippiehacker]

Sub bapcki1()

Debug.Print Chr(39)

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = "C:\temp\test.txt"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, Chr(34), Chr(39))

iFileNum = FreeFile
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

End Sub

 

[bapcki1]

Hi hippiehacker,

- that's fantastic.

THANK YOU!!


Paddy

 

[bapcki1]

Hi hippiehacker,

I was wondering whether you could help me just one more time please?

I would like the code you posted previously to work it's way down a list of files and file paths stored in Excel columns, replacing the " with ' in the .txt files.

The code you posted works brilliantly for a single file, thank you.

I attempted to make the adjustments myself, using a loop and with the file names and file paths as strings and then setting sFileName = FilePath & FileName.

Using the FilePath & FileName as strings as well as the loop worked fine, however, the contents of the finished .txt files had been altered so that it had copied data across from the previous file.

The adjustments I made looked something like the following:

[List of file paths in column A, file names in column B]

Sub bapcki1()


Do


Debug.Print Chr(39)


Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Dim FLNME As String
Dim FilePath As String


FLNME = ThisWorkbook.Sheets("Sheet1").Range("B1").Value
FilePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value


' Edit as needed
sFileName = FilePath & FLNME


iFileNum = FreeFile
Open sFileName For Input As iFileNum


Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum


sTemp = Replace(sTemp, Chr(34), Chr(39))


iFileNum = FreeFile
Open sFileName For Output As iFileNum


Print #iFileNum, sTemp


Close iFileNum


ThisWorkbook.Sheets("Sheet1").Select
ThisWorkbook.Sheets("Sheet1").Rows("1:1").Select
Selection.Delete xl Shiftup


Loop Until ThisWorkbook.Sheets("Sheet1").Range("A1").Value=""


End Sub

Can you see why this might happen please?

I only know some VBA and I don't recognise some of the objects in your code so I can't see where the problem is..

Is your code written in another language? - is it VB?

Many thanks, I really appreciate all of your help so far.

Paddy





</pre>

</pre>


[/CODE]

 

[hippiehacker]

like this should do

Sub bapcki1()
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Dim FLNME As String
Dim FilePath As String

'loop from lastrow to row 1 for headers in row 1 replace 1 with 2
For i = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

FLNME = Sheets("Sheet1").Range("B" & i).Value
FilePath = Sheets("Sheet1").Range("A" & i).Value

' Edit as needed
sFileName = FilePath & FLNME

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, Chr(34), Chr(39))

iFileNum = FreeFile
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

'delete row after txt file has been fixed
Sheets("Sheet1").Cells(i, "A").EntireRow.Delete

'reset variables
sBuf = ""
sTemp = ""
iFileNum = 0
sFileName = ""
FLNME = ""
FilePath = ""
Next i
End Sub

 

[bapcki1]

Hi hippiehacker,

- That's fantastic, thank you.

Best regards,


Paddy