File Operations in Excel 1

[FoxProProgrammer]

Hello folks,

Several weeks ago I posted here about a problem that I was having inserting a web counter into a spreadsheet. A few kind people attempted to help me resolve the issue, but couldn't come up with a solution. I figured out a workaround but it's not ideal. To quickly summarize:

I registered for a free web counter. The company that provides the web counter gave me code to include in my HTML file that displays a web counter on my web page. The web page is generated from an Excel spreadsheet using the Save As Webpage option. I couldn't get Excel to properly save the code for the web counter, which would automate the entire update process. My workaround is to open the HTML file that Excel creates in WordPad and manually pasting the HTML code for the web counter. Then I have to open the HTML file with FrontPage to properly position the web counter image. If possible I am trying to automate this process so I don't have to do it manually everytime I update my spreadsheet.

One idea that I had was to open the HTML file in Excel and paste the HTML code through VB code. I tried the Open Method, OpenAsTextStream Method, and OpenTextFile Method to no avail. Does anyone know how to do this?

I want to do the following:

1. Open the HTML file that contains my updated spreadsheet.

2. Open a text file that contains the HTML code for the web counter.

3. Copy the text from the file in step 2 and insert it at a specific location in the file opened in step 1.

Any help would be greatly appreciated.

Thanks!

Prayers to our troops in the Middle East.
dz
dzaccess@yahoo.com

 

[Zathras]

Assuming that you know the exact line number where the insertion needs to take place, here is a simple way to insert lines from one file into another:

Option Explicit

Sub test()
  MergeFiles "C:\a.txt", "C:\b.txt", 2, "C:\c.txt"
End Sub

Sub MergeFiles(FileA As String, FileB As String, _
            InsertAfter As Long, FileC As String)

' Create a new file [FileC] from [FileA]
' with text from [FileB] inserted
' after record number [InsertAfter]

Dim sBuffer As String
Dim nRecordNumber As Long
  Open FileA For Input As #1
  Open FileB For Input As #2
  Open FileC For Output As #3
  While Not EOF(1)
    Line Input #1, sBuffer
    Print #3, sBuffer
    nRecordNumber = nRecordNumber + 1
    If nRecordNumber = InsertAfter Then
      While Not EOF(2)
        Line Input #2, sBuffer
        Print #3, sBuffer
      Wend
    End If
  Wend
  Close
End Sub

If you don't know the line number, you could alter the test to look for a particular character string to identify the record to insert after.

If you want to insert text in the middle of a line, you could with a little more code, but it isn't really necessary just to create HTML code.

Hope this helps.

 

[FoxProProgrammer]

Thanks a lot, Zathras. I'll try this code tomorrow and let you know how it goes. Since the the line number can change after each update, I wouldn't be able to hard code the record where the code should be inserted. I will try to search for a keyword such as </body>, although I need to insert the code one line before that. By &quot;record&quot; I assume that you mean line number in the text file. Is there a way to return the line number in the file where it finds </body>? I am not very familiar with low level file operations in Visual Basic. I have written VB code to append data to an open file in Access, but didn't have any reason to keep track of the line numbers.

Best regards,
dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Maybe I can use the InStr function to determine where </body> exists. I'll try it later.

If InStr(sBuffer,&quot;body&quot Then
...

Thanks again.
dz
dzaccess@yahoo.com

 

[Zathras]

Yes, this version should be what you want:

Option Explicit

Sub test()
  MergeFiles &quot;C:\a.txt&quot;, &quot;C:\b.txt&quot;, &quot;</body>&quot;, &quot;C:\c.txt&quot;
End Sub

Sub MergeFiles(FileA As String, FileB As String, _
            InsertAfter As String, FileC As String)

' Create a new file [FileC] from [FileA]
' with text from [FileB] inserted
' after record that contains the string [InsertAfter]

Dim sBuffer As String
  Open FileA For Input As #1
  Open FileB For Input As #2
  Open FileC For Output As #3
  While Not EOF(1)
    Line Input #1, sBuffer
    Print #3, sBuffer
    If InStr(sBuffer, InsertAfter) > 0 Then
      While Not EOF(2)
        Line Input #2, sBuffer
        Print #3, sBuffer
      Wend
    End If
  Wend
  Close
End Sub

 

[FoxProProgrammer]

Thanks. I must have been working on it while you were posting. <grin> I came up with something very similar. It works great. Now I just have to open the web page with FrontPage to position the web counter in the right location. At least I don't have to copy the HTML code into the file each time. Thanks again!


Sub MergeFiles(FileA As String, FileB As String, FileC As String)

' Create a new HTML file [FileC] from the original HTML file [FileA]
' HTML code for the web counter is inserted from [FileB]
' beginning on the first line of the body of FileA

Dim sBuffer As String

Open FileA For Input As #1
Open FileB For Input As #2
Open FileC For Output As #3

While Not EOF(1)
Line Input #1, sBuffer
Print #3, sBuffer
If InStr(1, sBuffer, &quot;<body&quot <> 0 Then
Print #3, &quot;&quot; ' Insert a blank line before the new code is inserted
While Not EOF(2)
Line Input #2, sBuffer
Print #3, sBuffer
Wend
End If
Wend

Close

End Sub

dz
dzaccess@yahoo.com