Thread: Import data from a web page

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.

Private Sub CommandButton1_Click()

    Dim I As Long
    Dim Password As Object
    Dim Shell As Object
    Dim SubmitBtn As Object
    Dim UserName As Object
    Dim Wnd As Object
    
        URL = "http://itinventory.v16.va.gov/report.cfm?rptid=3"
        
        Set ieApp = CreateObject("InternetExplorer.Application")
        ieApp.Visible = True
        ieApp.Navigate URL

            While ieApp.Busy Or ieApp.readyState <> 4: DoEvents: Wend
            
            Set Shell = CreateObject("Shell.Application")
            
            For Each Wnd In Shell.Windows
                If Wnd.Hwnd = ieApp.Hwnd Then
                   Set ieDoc = Wnd.Document: Exit For
                End If
            Next Wnd
            
            'Set Password = ieDoc.getElementById("Password")
            'Set UserName = ieDoc.getElementById("Username")
            'Set SubmitBtn = ieDoc.getElementById("Submit")
            
            UserName.Value = TextBox1
            Password.Value = TextBox2
            SubmitBtn.Click
            
        Set ieDoc = Wnd.Document
        SelectReport
        
        Set ieDoc = Wnd.Document
        ImportTable
        
      ' Open hyperlinks and save .CSV to a prompted location with a unique filename
        combinespreadsheets
        
        Unload Me
        
End Sub

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:

UserName.Value = TextBox1

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:

For TR = 3 To TableObj.Rows.Length - 2

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