Scripting.FileSystemObject - how big can be a file?

dscheste · Sep 25, 2004

Hello guys,

I decided to export a knowledge database. The FAQ is running on PHPMyFAQ.

I decided to create a html file and with xmlhttp parse the topic and the FAQ, surrond it with some html formatting - and bingo.

Thought I.

Excel does it perferctly, all the faqs land in the worksheet as I request, providing the running numbers for the question IDs.

When I introduce the Scripting.FileSystemObject and create a text file, the file is created all right but is filled with 5 records only. Then the code throws

Run time error 5 and errors out.

Why is that? Is there a cure? I hoped to have the page like 50 pages, but it just does not want to grow 5 records. If I cut the topics or formatting, it accomodates 6. But that is it.

Please help.

Here is the code:

the variable cell is the ID number of the FAQ

Sub Get_Me_The_FAQ()
With Application
.EnableEvents = False
Dim fso, tf, xmlhttp, topic, faq, mystart, myend, artikelsize
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set tf = fso.CreateTextFile("c:\parsed.html", True)
 Set Rng = Range("B2:B50")
 tf.Writeline ("<html><head><title>Parsed from myPHP</title></head><body>")
For Each cell In Rng
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
strURL = "my_working_URL"
xmlhttp.Open "GET", strURL, False, "", ""
xmlhttp.Send
rtnpage = xmlhttp.ResponseText
 'Get the topic
 Range("A2").Value = cell.Row
 mystart = InStr(rtnpage, "[/i]")
 myend = InStr(rtnpage, "</h2>")
 artikelsize = myend - mystart
 topic = Mid(rtnpage, mystart + 5, artikelsize - 5)
 cell.Offset(0, 1).Value = topic
 tf.Write ("some html format" & topic & "some html format") ' Writes the name of the topic
 Set topic = Nothing
 'Get the topic text
 mystart = InStr(rtnpage, "")
 myend = InStr(rtnpage, " ")
 artikelsize = myend - mystart
 faq = Mid(rtnpage, mystart + 22, artikelsize - 21)
 cell.Offset(0, 2).Value = faq
 'MsgBox (faq)
 tf.Write ("some html format " & faq & "some html format") 'writes the actual answer
 Set faq = Nothing
Next

.EnableEvents = True
End With
 tf.Writeline ("</body></html>")
 tf.Close
 Exit Sub
error:
 MsgBox ("Error occured")
 tf.Writeline ("</body></html>")
 tf.Close
End Sub

dscheste · Sep 25, 2004

I had to do mail merge through word using the parsed data from excel.
Scripting.FileSystemObject thang just won't write to the file.

The mail merged file was saved as text and then renamed into html. Looks beautiful, but that should have been done with .write...

Any ideas?

dscheste · Sep 25, 2004

I guess all the active gurus enjoy the WE

I managed to resolve the issue, however, I would like a piece of advice as why the regular CreateTextFile and then .write does not seem to work in this case.

I have resolved the issue by not only creating the file, but by rather opening it as a text stream for writing. I do not see the difference, Microsoft MSDN keeps silence as well.

The working code is:

Sub Get_Me_The_FAQ()
Const ForWriting = 2
With Application
.EnableEvents = False
Dim fso, tf, xmlhttp, topic, faq, mystart, myend, artikelsize, ID, cell, f1
 Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.GetFile("c:\parsed.html")
Set tf = f1.OpenAsTextStream(ForWriting, True)
 Set rng = Range("B2:B155") 'specify which FAQs to download - in my case just running numbers
 tf.Writeline ("<html><head><title>Parsed from myPHP</title></head><body>") 'light html formatting
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
For Each cell In rng
strURL = "my_working_URL"
xmlhttp.Open "GET", strURL, False, "", ""
xmlhttp.Send
rtnpage = xmlhttp.ResponseText
 'Get the topic
 Range("A2").Value = cell.Row
 mystart = InStr(rtnpage, "[/i]")
 myend = InStr(rtnpage, "</h2>")
 artikelsize = myend - mystart
 topic = Mid(rtnpage, mystart + 5, artikelsize - 5)
 cell.Offset(0, 1).Value = topic
 tf.Write ("formatting" & topic & "formatting") ' Writes the name of the topic
 'Get the topic text
 mystart = InStr(rtnpage, "")
 myend = InStr(rtnpage, " ")
 artikelsize = myend - mystart
 faq = Mid(rtnpage, mystart + 22, artikelsize - 21)
 cell.Offset(0, 2).Value = faq
 tf.Write ("formatting" & faq & "formatting") 'writes the actual answer
Next
.EnableEvents = True
End With
 tf.Writeline ("</body></html>")
 tf.Close
 Exit Sub
error:
 MsgBox ("Error occured")
 tf.Writeline ("</body></html>")
 tf.Close
End Sub

Scripting.FileSystemObject - how big can be a file?

dscheste

Board Regular

Excel Facts

dscheste

Board Regular

dscheste

Board Regular

Similar threads

Forum statistics

Share this page

Scripting.FileSystemObject - how big can be a file?

dscheste

Board Regular

Excel Facts

dscheste

Board Regular

dscheste

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock