Hello everyone,

I'm using a form which requires external data from a webpage. This webpage is (fictionally) located at: www.server.com/test.txt

The form does this on open, in the background:
1. Go to the webpage
2. Save the txt file at "ActiveWorkbook.Path & \test.txt"
3. Read the file from step 2 into the form
4. Delete file from step 2

The code from step 2:
Sub GetWebPageText1()

  Dim Filename As String
  Dim FSO As Object
  Dim ieApp As Object
  Dim Txt As String
  Dim TxtFile As Object
  Dim URL As String
     
    URL = "www.server.com/test.txt"
    Filename = ActiveWorkbook.Path & "\test.txt"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set TxtFile = FSO.CreateTextFile(Filename, True, False)
         
    Set ieApp = CreateObject("InternetExplorer.Application")
    ieApp.Visible = False
    ieApp.Navigate URL
    
      While ieApp.Busy Or ieApp.ReadyState <> 4
        DoEvents
      Wend
      
        Txt = ieApp.Document.Body.InnerText
        TxtFile.Write Txt
        TxtFile.Close
        
    ieApp.Quit
    
    Set ieApp = Nothing
    Set FSO = Nothing

End Sub
The code from step 3 and 4:
Private Sub LoadFile()
Dim Filename As String
          
Filename = ActiveWorkbook.Path & "\test.txt"
StrTmp = "": sList = ""

Open Filename For Input As #1
Do Until EOF(1)
  X = X + 1
  Line Input #1, StrTmp
  sList = sList & StrTmp & vbCr
Loop
Close #1

While Right(sList, 1) Like "[" & vbCr & vbLf & vbCrLf & "]"
  sList = Left(sList, Len(sList) - 1)
Wend

For i = 0 To UBound(Split(sList, vbCr))
  UserForm1.Controls("ListBox1").AddItem Split(Split(sList, vbCr)(i), ",")(0)
Next

On Error Resume Next
Kill ThisWorkbook.Path & "\test.txt"
On Error GoTo 0

End Sub
This all works fine. The problem lies with the fact that our users can either save the file, or open the file in the web browser when downloading the document. When they save it, it works fine. When they open it in the web browser it can't load the external file. It returns a "Bad file name or number" error.

The reason for this is probably that the code tries to save the external file (www.server.com/test.txt), at ActiveWorkbook.Path, which at that moment is "www.server.com". Obviously out of security reasons, this isn't possible.

I tried disabling the code at step 2 as a test since the code at step 3 searches for a file located at "ActiveWorkbook.Path & "\test.txt", where ActiveWorkbook.Path = "www.server.com". But this doesn't work. I also tried to change the "Filename" path at step 3 to the code below, but both return the "Bad file name or number" error.

Filename = "www.server.com/test.txt"
What I would like to know is, can I directly read the external file from www.server.com/test.txt into my UserForm without having to temporarily saving it somewhere?

I hope this is clear enough! If you have any questions, please ask.

Thanks in advance!

Marco