Dear all,

I knew nothing about VBA but I managed and felt relieved to have prepared the below codes to download the loads of files in intranet weekly at work through reading online resources. I understood that SENDKEYS is an unreliable method. However, I found other methods incomprehensible. I still would like to know if there are other improvement I can make to the below code to at least download a finite number of files I need. As it is now downloading files at random, sometimes I got all the files I need, with most of the time missing 3 to 4 files at random. Thank you!

*****************************************************************************



Sub Filedownload()

'Things to Remember (https://www.educba.com/vba-sendkeys/)
'Be very careful while using SendKeys since it may give unexpected results if you have multiple applications open.
'You can use the SendKeys to automate the small tasks like saving an Excel File or Closing an Excel File etc.
'Use the SendKeys method as a last option while you are trying to automate the things.

Dim ieApp As InternetExplorerMedium
Dim ieDoc As Object
Dim ieTable As Object
Dim link, dropOptions, op,Form As Object
Dim l As Object
Dim code As String
Dim i As Long
Dim lastRow As Long


'create a new instance of ie
Set ieApp = New InternetExplorerMedium

'you don’t need this, but it’s good for debugging
ieApp.Visible = True

'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "http://XXXXX"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.txt_usr.Value = "XXXXX"
.txt_pwd.Value = "XXXXXX"
.submit



End With


Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

 
Set link = ieDoc.getElementsByTagName("a")
 
For Each l In link

    If l.innerText = "ABC" Then
 
        l.Click
      
        Exit For
     
    End If
     
Next l
    
'Command for one change - ref source: https://www.mrexcel.com/board/threads/excel-vba-code-to-select-option-from-drop-down-box-in-website.1162564/
    
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

Set dropOptions = ieDoc.getElementsByTagName("select")
For Each op In dropOptions
    If op.getAttribute("name") = "variable name" Then
    op.Value = "XXX"
    op.Click
    op.FireEvent ("onchange")
   
    Exit For
End If
Next op

Application.ScreenUpdating = True


lastRow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow


Do While ieApp.Busy: DoEvents: Loop

'Do Until ieApp.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

code = Worksheets(1).Cells(i, 2).Value

ieDoc.getElementsByName("variable name")(0).Value = code

ieDoc.querySelector("[value=　Print　]").Click

Application.Wait (Now + TimeValue("00:00:03"))

Application.SendKeys "%{S}"


Next

MsgBox "Done"


End Sub