Trying to click on hyperlink using vba but path changes week to week

[cgabe001]

ERT Utilization.xlsm
http://ert.pbg.pvt/infosis/reportvie...HhJqtUxiw_RiTc

I have this formula in cell b1 that I've pulled from and outlook email. I have 9 other hyperlinks with different names following it up to b10. I need to be able to open this link (which opens a read only excel file called reportviewer.xls Each hyperlink opens the same file. I want to open each and extract the data onto a tab in my workbook but cant figure out how I click on the link through a macro since the file path changes week to week. The hyperlink is derived from the text in the email
HYPERLINK(LEFT(MID(A6,FIND("http:",A6),1000),LEN(MID(A6,FIND("http:",A6),1000))-2),LEFT(MID(A6,FIND("http:",A6),1000),LEN(MID(A6,FIND("http:",A6),1000))-2))

Any help would be greatly appreciated.

[AlphaFrog]

This will follow the hypperlink in cell B2

Range("B2").Hyperlinks(1).Follow

Is that what you want?

[cgabe001]

This seems to have worked without referencing the actual path. I have
Range("B1").Hyperlinks(1).Follow
Windows("reportviewer").Activate
ActiveWorkbook.saveas Filename:= _
"D:\personal\10046733\Warehousing\ERT Reports\Southeast.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

I repeat this for each hyperlink, B1 then B2, then B3 and when it gets to B4 there is a run time error '9': subscript out of range. Any suggestions? I am trying to open the link and save as so that I can import the data into the earlier attached sheet and manipulate from there.

[AlphaFrog]

Is the hyperlink in B4 valid? Can you click on it and does it open the file?

[cgabe001]

Yes it is valid and opens if I click on it.

[AlphaFrog]

Can you show your code for B4?

[cgabe001]

ERT Utilization.xlsm
Debugging here on the first line. I've attached the file again for reference. Not sure if you can run since these are internal reports and might not work off our network.
Range("B4").Hyperlinks(1).follow
Windows("reportviewer").Activate
ActiveWorkbook.saveas Filename:= _
"D:\personal\10046733\Warehousing\ERT Reports\Midwest.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

[AlphaFrog]

Correct, the links don't work for me. I don't know why it errors on B4.

This is a way to loop your code for each cell in B1:B10 and name files using the values in column C

Please Login or Register  to view this content.

Also note: it's not recommended to use macro names that are the same as VBA commands or keywords e.g.; SaveAs. This is not the problem though.

[cgabe001]

Thank you for the note and the easier way to loop it. I knew there was an easier way I'm just not well versed in code by any means. It still stops on cell.Hyperlinks(1).follow

On another note, I know I've seen a thread about it before but is there a way to not hit ok when overwriteing the file name?

[AlphaFrog]

On another note, I know I've seen a thread about it before but is there a way to not hit ok when overwriteing the file name?

Please Login or Register  to view this content.

[cgabe001]

Thank you, well it works for B1:B3, is there a way I can repeat the loop for 4:6 then 6:9 then 10 to try and get around the run time error?

[AlphaFrog]

This pops up a message if the hyperlink fails then continues with the next link.

Please Login or Register  to view this content.

[cgabe001]

Thanks, but they all fail after that. I took the above out because then you just have to hit ok. I need to fix the run time error '9' subscript out of range.

I'm going to consider my question on this one solved, thank you for your help. I'll post another thread on the run time error.