External data from webpage (txt file) to form

[Marco-Kun]

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:

Please Login or Register  to view this content.

The code from step 3 and 4:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

[Kyle123]

Maybe:

Please Login or Register  to view this content.

[Marco-Kun]

Hello Kyle,

Thank you for responding. Could you explain me what the code does? Does it temporarily load the text file into the memory?

Also, how do I implement it so it can load into my form? If you meant for me to put it in like this in the code from step 3, that doesn't work:

Please Login or Register  to view this content.

[Kyle123]

No, I didn't

My code puts the text of the .txt file directly into a string variable for you to use as you want.

You don't need to use:

Please Login or Register  to view this content.

Since you already have the text file in a variable

[Marco-Kun]

Hello Kyle,

Thanks for that. I indeed won't need to do that since it's already in a string.

I've been trying to fix it on my own, but I can't get it to work completely.

I addded 2 sheets. Both have a password on the VBA editor, which I will send to you in a private message.
The reason for this is that it has the original external file URL.

Test1.xls uses the code which I described in the first post of this topic. When you click on the button you will see how the external file should be added to the form which opens.

Test2.xls uses your code plus my code which I tried to combine. When you click on the button it does load the string with the external data, but not as it should, like in Test1.xls.
It shows "¶" in the form in front of the words which should be in the first list of the form.

Kind regards,

Marco

[Kyle123]

Hi, it's because you're splitting on the wrong character, it should be vblf; try this:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Marco-Kun]

Hi Kyle,

Thanks a lot for your time and effort. It works! This will help me a lot.

Kind regards,

Marco