Import data from a web page

I have no experience pulling data from a web page to import to excel. I've borrowed some code I found here, but am stuck on how to proceed once I reach the website. I've attached the spreadsheet, as well as a word document showing my desires. Any and all help will be HUGELY appreciated!

1. sub Import() needs to make elections before running report. Using the link, copy and paste the table to Summary.

2. Using the links in the table, prompt the user for a location to save each .CSV with a unique name, then sub combinespreadsheets() populates Master.

3. Would like a worksheet change event on Aging inventory D2 to filter Master by column L including blanks, hide the columns in sub CreateAgingList(), then paste the visible cells back to Aging Inventory

One of these links may help you:
http://www.thecodecage.com/forumz/mi...vbulletin.html
http://www.thecodecage.com/forumz/mi...lications.html
http://www.thecodecage.com/forumz/mi...nto-excel.html

Reading the very first part of your question "am stuck on how to proceed once I reach the website" those links i gave you are to code that actually gets data from websites, one of them shows you how to fill in name boxes..etc, so if you look carefully at the code i'm sure it would help. Also there aren't any attachments in your first post

Hello Hoosaskin,

Because the URL is an intranet and internet address, we can not access the site or web page. To help you, you will need to save the page source as a text file and post it (IE menu: View > Source).

Here 3 files showing what the pages looks like, as well as their source code.
Run report is first, then get to table and inventory

Hello Hoosaskin,

Thank you for posting all that information. That is exactly what I was looking for. I am currently writing the code to automate copying the data to the worksheet. Just wanted to let you know what is happening.

Hello Hoosaskin,

I need to know if the sample HTML files are exact copies from the web pages. I have most of the code written but Internet Explorer fails to retrieve the files from my hard drive. I can open the files fine with FireFox and Chrome. If the files are the exact copies then perhaps once you are logged on then Internet Explorer will work. This is a something I have never come across before.

Hello Sean,

It is so nice to Sean spelled as it is in the Gaelic. I feel confident that once you are logged on Internet Explorer the macro will work correctly. I do have another question. When the report is run and I am assuming you want the entire process automated, the choices listed in the Word file will be the same for all users, yes?

Hello Sean,

Thanks for the clarification. If you can post the page for comparison that would be great. It will certainly head off any problems.

Hello Sean,

Thanks for the additional files. It was a big help in checking the code. The attached workbook has one button on the "Instructions" sheet called "Run Report". This macro calls up a UserForm I added that allows you to type in your user name and password. The password appears as asterisks in the text box. The button labeled "Log On" kicks off all the other macros for selecting the report, importing the table, and saving the CSV files.

The only modification that needs to be made is to the "Log On" code. I do not know what the names of the user name input, password input, and submit button are. Perhaps you can add the needed names. The code appears below.

Please Login or Register  to view this content.

Try out the attached workbook and let me know of any problems that occur or changes you would to make.

Hello Leith,

Firstly, thank your for all your time with this! Nothing I tried would work. I just tried running the macro, and after clicking the log on button (there is no user name or password required, just a va.gov user), I got a run time error 91: object variable or with block variable not set for this line of code:

Please Login or Register  to view this content.

Since user name and password aren't necessary, I commented them out. It ran all the way through. One issue is the SUB combinespreadsheets() asks the user to indicate where the .CSV files, from the third screen, are saved. Could you put in an input box for the user to specify this location?

Another is the imported table for the summary tab. The macro grabbed the first page after creating the report. I need it to open the link that created from running the report and import the subsequent table.

And somewhere along the line, since everything runs from the instruction tab, the instructions get cleared, auto-filter and freeze frame are applied. I tried stepping through the macros as they run, but once they've started I can't change from the Instructions tab.

Thank you again for all your time!


Sean

10:22- Now I'm getting a run time error 91 object variable or with block variable not set at this line (with user id/password commented) of IMPORT TABLE:

Please Login or Register  to view this content.

Should this need another ie.app busy or ie.app readystate statement while the site is conducting the query?
smb