Opening a file using a hyperlink with VBA

[Tim_Excel_]

Hiya forum,

I'm trying to open some files automatically, which shouldn't be too hard. Unfortunately, so far I've been really held back by the file being located on a weblink. Let's call the weblink http://classifiedlink.com/" & VRl & "type=PDB.

Set sh = Workbooks(var1).Worksheets(1)
With sh
lr = .Range("D" & .Rows.Count).End(xlUp).Row
    With .Range("D1:D" & lr)
For Each cell In .Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible)
    If cell <> "" Then
    cell.Select
    VRl = ActiveCell
    ActiveWorkbook.FollowHyperlink ("http://classifiedlink.com/" & VRl & "type=PDB")

This code works, but not the way I want it. Because it's a hyperlink, both MS and IE ask me if I want to open the file. This stops my VBA code, while I want the Macro to run automatically and without user interference. Does anyone know a way to surpass all the popup windows and open it right away?

Many thanks

 

[RedBeard]

This is by design, you need to use a different method to open the hyperlink.
ShellExecute being one: https://support.microsoft.com/en-us/kb/170918

 

[Tim_Excel_]

I tried to work my way through the code of Shell, but I find it difficult to understand. So far I haven't found a way to open a weblink directly with acrobat reader. I think this is due to the file being a hyperlink. However, when I open up acrobat reader and try to open the file by pasting the hyperlink in the filename box, the file opens perfectly fine. That what made me think to make VBA create keystrokes with SendKeys, but this would be I think very time consuming for VBA.

Shell "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe, http://confidentiallink.com/type=PDB" 'with this code, adobe fails to open the file

Shell "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
'now I would need SendKeys to open and print the files.

I'm stuck at the second code, since it opens Reader but doesn't open the window. Is there a VBA code to bring the window to the foreground?

 

[Tim_Excel_]

Got it to work:

MyApp = Shell("C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe", vbNormalFocus)

Still, if you were to find a way (a faster and more efficient way) to print the files without having to open them / use SendKeys, that would be just great