×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Loop through worksheet hyperlinks and save files in a different location

Loop through worksheet hyperlinks and save files in a different location

Loop through worksheet hyperlinks and save files in a different location

(OP)
Hi,

I have some code which loops through a given folder and deposits filenames and their locations among other details into a table on a worksheet:



I have some code that then loops through the Hyperlinks in the "File Link" field/column and saves these files to a static location within the code:

CODE

Sub Upload_to_Sharepoint()

Dim hlink As Hyperlink
Dim wb As Workbook
Dim saveloc As String
Dim filen As String

'Timer
Dim StartTime As Double: StartTime = Timer
Dim MinutesElapsed As String    'Remember time when macro starts

Application.ScreenUpdating = False

saveloc = "https://xxx.com/xxx/TEST/Shared Documents/"
For Each hlink In ThisWorkbook.Sheets("FileDirectory").Range("J:J").Hyperlinks

    Set wb = Workbooks.Open(hlink.Address)
    wb.SaveAs saveloc & ActiveWorkbook.Name
    wb.Close True
    Set wb = Nothing
Next

Application.ScreenUpdating = True

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
txEnd = "Done. The code ran successfully in " & MinutesElapsed & "."

MsgBox txEnd

End Sub 

I now need to upgrade this routine by, instead of using a hard-coded file destination, have a user enter a new destination for each file in the table into a new field/column and for the code to use that.

The table is named "Ref_FileList" and the hyperlinks reside with the field "File Links", the new destinations can reside within a field named "File Destination". I'm still new to working with structured tables in VBA so any help you can give with altering my code to work with this table would be highly appreciated.

Thanks very much,
Rich

RE: Loop through worksheet hyperlinks and save files in a different location

Hi,

A picture of your sheet is pretty much, er, uh, pretty. Not worth much more. Fuzzy, but not warm.

But maybe some code like this...

CODE

Dim r As Range

For Each r In [Ref_FileList[File Links]]
    With Workbooks.Open(r.Address)
        .SaveAs Intersect(r.EntireRow, [Ref_FileList[File Destination]]).Value
        .Close
    End With
Next 




Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop through worksheet hyperlinks and save files in a different location

(OP)
Thanks again, Skip. As long I've made someone feel fuzzy and warm, it's been a good day!

I'll try this tomorrow.

Rich

RE: Loop through worksheet hyperlinks and save files in a different location

Great.

Please notice that I changed the SaveAs statement, adding the .Value property of this object.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop through worksheet hyperlinks and save files in a different location

(OP)
Skip, the code you provided throws an error.

I get the following:



On line:

CODE

With Workbooks.Open(r.Address) 

The cell reference in the error dialogue (if you'll consult the picture I provided you(!)) is the correct reference of where the first file location is stored. I'm guessing the error is because of the use of the range object before the address object because if I amend the code to the following I can at least get the file to open:

CODE

Sub Save_New_Code()

Dim r As Range
Dim hlink As Hyperlink

For Each hlink In [Ref_FileList[File Link]].Hyperlinks
    With Workbooks.Open(hlink.Address)
        .SaveAs Intersect(r.EntireRow, [Ref_FileList[File Destination]]).Value
        .Close
    End With
Next

End Sub 

but this throws an "Object variable or With block variable not set" error on line:

CODE

.SaveAs Intersect(r.EntireRow, [Ref_FileList[File Destination]]).Value 

I think this is because the focus is then passed to the newly opened workbook? Maybe?

Did I pass the test you clearly deliberately left me?

Thanks,
Rich

RE: Loop through worksheet hyperlinks and save files in a different location

(OP)
Thanks for that, Skip, although with the below code I can get the first file to open (I had to remove the table references as I couldn't get them to work):

CODE

For Each hlink In ThisWorkbook.Sheets("FileDirectory").Range("J:J").Hyperlinks
    With Workbooks.Open(hlink.Address)
        .SaveAs Intersect(r.EntireRow, ThisWorkbook.Sheets("FileDirectory").Range("K:K")).Value
        .Close
    End With
Next 

But I get the Object Variable or With... error on line:

CODE

.SaveAs Intersect(r.EntireRow, ThisWorkbook.Sheets("FileDirectory").Range("J:J")).Value 

Would this suggest that it's not in fact the Hyperlinks object that is causing the issue, rather it is the Save portion?

RE: Loop through worksheet hyperlinks and save files in a different location

Intersect(r.EntireRow, ThisWorkbook.Sheets("FileDirectory").Range("J:J")).Value

What this says is that the For Each r...Next that r range is on sheet FileDirectory AND column J:J is on the same sheet and we’re getting the j value on the r row.

Is that indeed the intention?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop through worksheet hyperlinks and save files in a different location

(OP)
Sorry! The reference for that line should be K:K, but yes, that is the intention.

J:J is where the file location is stored.

K:K is where the new save location is stored.

But they are all on the same sheet: "File Directory".

Thank you, Skip.

RE: Loop through worksheet hyperlinks and save files in a different location

For clarity in your code you ought to use the Table Heading Value, rather that the nondescript K:K

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close