manipulating external files using VBA

[eHanSolo]

HI guys,

Was just wondering if the following is possible.

I'd like to open a handful of .htm files using Notepad. Then i'd like to replace and add a single line...save it and close it using VBA. Is this possible? and if so some help on doing it would be greatly appreciated!!!

Many thanks!

E

 

[mikej28621]

It would not be possible using notepad as Notepad is not VBA enabled. It would be possible using a VBA program in Word, Access, Excel or VB, VB.Net.

Using VBA you would not have to open the files. VBA can open the file, search for the text to be replace, add the lines you want, then save the file without it having to be opened.

I would look at the examples at

http://www.vba-programmer.com/

There are multiple sites that give you examples.

 

[eHanSolo]

Oh i see.

Hey, i visited that link you posted. I can't seem to find anything of relevance...

...don't suppose you could provide a link to the page itself??

In the mean time, i'll take your advice and have a search on the web.

thanks for you help!!!

E

 

[fumei]

mikej28621, I am sorry, but...

VBA can open the file, search for the text to be replace, add the lines you want, then save the file without it having to be opened.

If I walk through the steps -

1. VBA can open the file
2. Search for text
3. Adds the lines
4. Saves the file......without opening it.

Uh, please see #1.

VBA can not do anything to the internal parts of a file without opening it. If I am incorrect on this, please do post some code that shows how that could be done.

eHanDolo, simply put, HTM (or HTML) files are text files, nothing more, nothing less.

There are two ways to do what you want.

You can use filehandles and access the file, then use Write add your lines.

I would not recommend this method, unless you learn how to do FileI/O. It requires very specific line identification. But it can be done, and speed wise, is very likely the fastest. It can be done using VBA, but sorry, it still opens the file.

The second way is to open the HTM file in Word. Frankly, opening it in NotePad and copying it to WEord is not a bad idea. The reason being is that Word, as much as Microsoft would like to think of it as an HTML editor, is very very poor as an HTML editor. It is far better to add your line to a straight text file, then name the file HTM.

However, you can force Word to open the file as text.

If they are all in the same folder, look up using the Dir function to get all the files.

If you know where you line to add will go, and it is possible to do a search for the proceding line, that would be the way to go.

I can not give any more details as you would need to supply more details from your end. For example:

Is the line to be added always the same text?
Is it always the same relative location?

Say, you wanted to add a single line of a metatag to a bunch of htm files.

You would open the file.
search for a previous <META and move the cursor back (or maybe forward), insert your line, save the file as text...and go on to the next file.

It certainly is very do-able, but you have to open the files, one by one.



Gerry

 

[mikej28621]

fumei,

you are correct. I ment you would not have to open the files in the since of opening them with a program like notepad. You would however have to progamattically open each file before you can manipulate it. eHanSolo said "I'd like to open a handful of .htm files using Notepad" which would not be necessary.

Basic program to open a file and be done in as few lines as:

Private Sub AppendTextToFile()
intFile = FreeFile
Open "C:\Temp\Test.txt" for Append as intFile
Print #intFile, Textbox1.Text ' Adds contents of the textbox to the file
Close intFile
End Sub


MJ

 

[eHanSolo]

wow...thanks for all your help guys!!!!!!


E

 

[Cresby]

you could try opening the app (app activate) and use

SendKeys "%O" ' to open a file % = ALT
SendKeys "filename" ' to open a file
etc

but using OPEN and GET are easier because you cannot single step through SendKeys as it sends the keystrokes to the VB window. AND the target app has to have focus.

I just found this in a help file

Dim MyAppID, ReturnValue
AppActivate "Microsoft Word" ' Activate Microsoft
' Word.

' AppActivate can also use the return value of the Shell function.
MyAppID = Shell("C:\WORD\WINWORD.EXE", 1) ' Run Microsoft Word.
AppActivate MyAppID ' Activate Microsoft
' Word.



' You can also use the return value of the Shell function.
ReturnValue = Shell("c:\EXCEL\EXCEL.EXE",1) ' Run Microsoft Excel.
AppActivate ReturnValue ' Activate Microsoft
' Excel.