The code below does everything you requested and has been tested on my system
- this works perfectly for me using Firefox (I do not have IE on my PC)
- I expect it to work for you with very little modification
- I hope that Internet Explorer does not throw in any tricky surprises
- VBA subdivided into separate procedures to make it easier to debug each bit in isolation
THIS TEST
- this first test uses files we both can access (= those on this forum)
- the attached workbook contains direct links to 3 files (and the names of the 3 files)
- the files are downloaded (to default downloads folder) and then moved to selected destination folder
- before that happens the previous copy of each file is deleted to avoid any confusion
- files in both folders are deleted
- this method avoids opening and closing each file which should be unnecessary
FOR INFO
- automatically downloading files from the internet is not as straightforward as it may appear when you do things manually
- a link may not exist (how does VBA handle that?)
- internet performance is variable
- controlling the browser via VBA is not simple
- getting a "handle" on the open tab etc is complicated
- the code below uses SENDKEYS (something I try to avoid)
- SENDKEYS are what you would type if doing it manually
- SENDKEYS can be unpredictable (eg if another program takes the focus) because they apply to the ACTIVE window
- VBA is fast, but handling things via the internet is slow
- VBA can easily get ahead of itself
- so "delays" have been deliberately incorporated to make VBA wait 5 seconds every time
- be aware that if everything does not download that simply increasing the delay may fix the problem
- message returned at end if any expected files have not been downloaded
TO RUN THE TEST
- open IE and make sure that you are automatically signed into the forum when a new tab is opened (otherwise you cannot download files)
- browserPath - is this path to Internet Explorer correct?
- finalPath - create new destination folder on your system and amend finalPath string to match
- downloadPath - amend string to match your own default download path
- I woud move existing files out of the downloads folder (you do not have to but it is easier to find test files if they are the only ones!)
- VBA runs in attached workbook with {CTRL} k
- when the open/save screen pops up IGNORE IT - VBA should do what's required
- everything should be complete in less than 30 seconds
- 3 empty browser tabs need closing manualy at the end (may be able to automate that in IE later)
- close those and check destination folder
EXPECTED RESULT
- new destination folder contains 3 workbooks
- download folder contains none of them
FEEDBACK
- let me know how it goes
- if VBA throws an error I need to know on which line, the error code and the message
- is it carrying out the required tasks?
- is there anything different with the real file? (- even minor differences may require a code mod, so let me know)
APPLYING TO YOUR SPECIFIC REQUIREMENTS
- we must ensure everything working correctly using forum files before modifying code to meet to your specific requirement
- amend finalPath
- replace links and file names in sheet "Links"
Const browserPath As String = "C:\Program Files\Internet Explorer\iExplore.exe" 'AMEND (= your Internet Explorer path)
'Const browserPath As String = "C:\Program Files\Mozilla Firefox\firefox.exe"
Const finalPath As String = "C:\TestArea\Doc" 'AMEND (= destination folder)
Const downloadPath = "C:\Users\kev\Downloads" 'AMEND (= file download folder)
Const myLinks As String = "Links"
Dim FSO As Object
Dim oldFile As String
Dim ws As Worksheet, filelist As Range, linkList As Range, f As Range, url As Range
Sub Master()
SetCommonThings
DeleteOldFiles
GetLatestFiles
MoveFiles
End Sub
Private Sub SetCommonThings()
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ws = Sheets(myLinks)
End Sub
Private Sub DeleteOldFiles()
Set ws = Sheets(myLinks): Set filelist = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
For Each f In filelist
oldFile = finalPath & "\" & f: If fileExists(finalPath, f) Then Kill oldFile
oldFile = downloadPath & "\" & f: If fileExists(downloadPath, f) Then Kill oldFile
Next f
End Sub
Private Sub GetLatestFiles() '
Set ws = Sheets(myLinks): Set linkList = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp))
For Each url In linkList
Application.DisplayAlerts = False: 'On Error Resume Next
Shell (browserPath & " " & url.Value)
Application.Wait (Now() + TimeValue("00:00:05"))
If Not Err > 0 Then SendKeys "%s~":
Application.DisplayAlerts = True: 'On Error GoTo 0
Next url
Application.Wait (Now() + TimeValue("00:00:05"))
Application.Windows(ThisWorkbook.Name).Activate
End Sub
Private Sub MoveFiles() 'moves files from download folder tospecified folder
Static NotFound As String
Set ws = Sheets(myLinks): Set filelist = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
For Each f In filelist
oldFile = downloadPath & "\" & f
If fileExists(downloadPath, f) Then
FSO.movefile Source:=oldFile, Destination:=finalPath & "\"
Else
NotFound = NotFound & vbCr & f 'create message string
End If
Next f
If NotFound <> "" Then MsgBox NotFound, vbExclamation, "Files not Found:"
End Sub
Function fileExists(aFolderPath, aFileName) As Boolean
fileExists = FSO.fileExists(aFolderPath & "\" & aFileName)
End Function
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by kev_
Internet Explorer has added the name of the thread in front of the file name
Bookmarks