Kenneth,
After a few weeks of work, and with help of the gurus for this and other forums, I was able to download ~100k .pdf files from my database.
Now I'm facing a similar challenge, but this time I need to deal with the non-pdf files in my database. The .pdf files are downloaded automatically (I had to change options in Adobe Acrobat to disable the .pdf support to IE), the rest of the files, I found no way to force IE to download them, (.txt, .doc, .jpg, .bmp, etc..). I found an alternative using HTML5 (see here), I was able to create .html files that when open, only display a link, when the link is clicked, the file starts to download. This is an improvement but since I'm not familiar with HTML5 I need help in making the code to do the following:
1. Automatically click the link, so it will trigger the downloading of the file
2. If possible, give a name to the file to download file, it is simply saved as ParseFile.htm, since I need to do this several thousand times, I would like the file to have a meaningful name.
The following code creates the .html file with the required link and opens the file later on, but I still need to click the link and if possible give a meaningful name to the file, preferably before downloading it.
#If VBA7 Then Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
Sub CreateHTML()
Sheets("Summary-1").Activate
VaultMin = Cells(2, 2)
VaultMax = Cells(3, 2)
FilePath = Cells(2, 5)
TextA = """"
Text1 = "<a href="
Text2 = "http://xx.rrrrr.com/new_fgtfgtfgtASP/fileParse.php?attachID="
Text4 = "&fileID="
Text6 = " download="
Text7 = "FileName"
Text8 = ">File abc</a>"
For t = VaultMin To VaultMax
Text = ""
If Cells(t, 5) <> ".pdf" Then
Text3 = Cells(t, 6)
Text5 = Cells(t, 7)
Filename = "HTML-" & Text3 & "-" & Text5 & ".html"
MyFile = FilePath & Filename
Text = Text1 & TextA & Text2 & Text3 & Text4 & Text5 & TextA & Text6 & TextA & Text7 & TextA & Text8
Cells(1, 1) = Text
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(MyFile, True)
f.Write Chr(9) & Text 'strMyPage
f.Close
Call OpenAnyFile(MyFile)
Else: End If
Next t
End Sub
Function OpenAnyFile(FileToOpen As String)
Call ShellExecute(0, "Open", FileToOpen & vbNullString, _
vbNullString, vbNullString, 1)
End Function
By the way, this HTML5 script doesn't work in IE, just in Chrome, so I have the additional problem of controlling Chrome with VBA (with IE is well documented, but I couldn't find the same level of documentation for Chrome)
Warmest regards and thanks in advance for all your help!
Pegaso