Manipulating / Parsing text file in VBA

sabascal · Sep 30, 2003

Hi all,
This is the method I used to save and web page locally, in order to extract an html table I want to paste on a worksheet:

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Sub test()
MsgBox DownloadFile("

http://www.gecapitalrealestate.com/marke...;,

"C:\test.txt&quot

End Sub

=================================
I still need to copy what's between the <BODY> ...</BODY> tags to paste it into a worksheet. Any hints on how to do that.

Thanks,
Seb

VBAjedi · Sep 30, 2003

Well, you could write code to create a Word object, open your source file from Word and do a Find/copy on "<Body>*</Body>", dump that to a new Word document, then do two find/replace operations to delete the tags at the beginning and end. Lastly, import the contents of that second Word doc to Excel, then kill the Word object. . .

Might be a better way out there, but that's what comes to mind at the moment.

VBAjedi [swords]

sabascal · Sep 30, 2003

Here is my solution:
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

Sub test()
MsgBox DownloadFile("

http://www.gecapitalrealestate.com/marketrates/marketrates.asp?page=euro",

"C:\test.txt&quot

End Sub

Private Function ReadFileText(ByVal FileName As String) As String
On Error GoTo EHandler
Dim Handle As Integer
Dim strBegin, strEnd, lenght As Integer
Handle = FreeFile
Open FileName For Input As #Handle
ReadFileText = Input$(LOF(Handle), Handle)
strBegin = InStr(ReadFileText, "<BODY&quot

- 1
strEnd = InStr(ReadFileText, "</BODY>&quot

strLenght = Len(ReadFileText)
ReadFileText = Replace(Left(Right(ReadFileText, strLenght - strBegin), strEnd - strBegin), " ", "&quot

On Error Resume Next
Close #Handle
Exit Function
EHandler:
On Error Resume Next
Close #Handle
End Function

Sub MarketRates()
Set MyData = New DataObject
Dim b As Boolean
b = DownloadFile("

http://www.gecapitalrealestate.com/marketrates/marketrates.asp?page=euro",

"C:\test.txt&quot

Workbooks.Add
Sheets("Sheet1&quot

.Select
Sheets("Sheet1&quot

.Name = "EUR"
MyData.SetText ReadFileText("C:\test.txt&quot

MyData.PutInClipboard
Range("A1&quot

.Select
ActiveSheet.Paste
End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Manipulating / Parsing text file in VBA

sabascal

IS-IT--Management

VBAjedi

Programmer

sabascal

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor