• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy hyperlink conent into relevant location patch

H

ham.sajjadi

New Member
I've two columns in Excel called A & B. And I've many hyperlinks .pdf file in column A which need to be copied to a relevant location in column B. For example, A2 hyperlink file copy to B2 location patch. I am an amateur with VBA. Is there a way to handle this problem with VBA?
 
Not exactly sure what you need to accomplish here. Can you upload sample file clearly showing what you currently have and what you want to accomplish?

If not, try using HYPERLINK() function in combination with VLOOKUP or other lookup type function.
 
Chihiro said:
Not exactly sure what you need to accomplish here. Can you upload sample file clearly showing what you currently have and what you want to accomplish?

If not, try using HYPERLINK() function in combination with VLOOKUP or other lookup type function.
Click to expand...

tnx for your attention.
here is an sample excel file
 

Attachments

  • sample.xlsx
    10.2 KB · Views: 4
Refer to the link. It will show you how to move/copy files using Scripting.FileSystemObject
https://www.rondebruin.nl/win/s3/win026.htm

Other option is to just use Name function.

Code: 
Sub Demo()
Dim cel As Range
Dim mySource As String, myFile
For Each cel In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
    If cel.Hyperlinks.Count > 0 Then
        mySource = cel.Hyperlinks(1).Address
        myFile = Split(mySource, "\")
        myFile = myFile(UBound(myFile))
        Name mySource As cel.Offset(, 1).Value & "\" & myFile
    End If
    
Next
End Sub
EDIT: If you want to copy and not move the file, replace "Name" with "FileCopy"

However, I can't test your language on my machine. So you may need to alter things a bit.

Also... Please read the link for forum rules and etiquette on cross-posting.
https://chandoo.org/forum/threads/new-users-please-read.294/
 
Last edited:
Chihiro said:
Refer to the link. It will show you how to move/copy files using Scripting.FileSystemObject
https://www.rondebruin.nl/win/s3/win026.htm

Other option is to just use Name function.

Code: 
Sub Demo()
Dim cel As Range
Dim mySource As String, myFile
For Each cel In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
    If cel.Hyperlinks.Count > 0 Then
        mySource = cel.Hyperlinks(1).Address
        myFile = Split(mySource, "\")
        myFile = myDest(UBound(myDest))
        Name mySource As cel.Offset(, 1).Value & "\" & myFile
    End If
    
Next
End Sub
EDIT: If you want to copy and not move the file, replace "Name" with "FileCopy"

However, I can't test your language on my machine. So you may need to alter things a bit.

Also... Please read the link for forum rules and etiquette on cross-posting.
https://chandoo.org/forum/threads/new-users-please-read.294/
Click to expand...

special thanks for your answer.
i did it,but i got "run time error =13,type missmatch" .as a Chance,I marked "Microsoft scripting runtime" in reference liberary too,but the problem stood.I re upload sample file with simple English character.would u please retest it by your own??? (i made hyperlink without formula .is it the case?)
 

Attachments

  • sample2.xlsx
    9.2 KB · Views: 4
You have relative reference for Hyperlink. You need absolute path.

Absolute path: C:\test\myfolder\test.pdf
Relative path/reference: ../Downloads/myfile.pdf
 
so
Chihiro said:
You have relative reference for Hyperlink. You need absolute path.

Absolute path: C:\test\myfolder\test.pdf
Relative path/reference: ../Downloads/myfile.pdf
Click to expand...
sorry,but have u tested this macro before?using "/" or "\" didn't make any different with error.it won't work.would u please test with a sample hyperlink file in a new sheet?
 
To clarify slash direction isn't the issue here. Either will work as long as you change delimiter for the Split function to match it.

But absolute path does not change based on workbook location. (Ex. Fixed to path C:\test\myfolder\test.pdf). Where as relative path will change path based on workbook location. Let's say path is defined as "../myfolder/test.pdf".

It will look from where the workbook is saved, for subfolder named "myfolder". Not necessarily in "C:\test\myfolder\".

When naming/moving files using code. It is important that you use full path & file name and not the relative path.

And yes, I've tested my code and works fine as long as you have absolute path as your hyperlink address. (Note: change Split function's delimiter if you use "/" instead of "\")

Also, you don't need any external reference for the code in my example.
 
Chihiro said:
To clarify slash direction isn't the issue here. Either will work as long as you change delimiter for the Split function to match it.

But absolute path does not change based on workbook location. (Ex. Fixed to path C:\test\myfolder\test.pdf). Where as relative path will change path based on workbook location. Let's say path is defined as "../myfolder/test.pdf".

It will look from where the workbook is saved, for subfolder named "myfolder". Not necessarily in "C:\test\myfolder\".

When naming/moving files using code. It is important that you use full path & file name and not the relative path.

And yes, I've tested my code and works fine as long as you have absolute path as your hyperlink address. (Note: change Split function's delimiter if you use "/" instead of "\")

Also, you don't need any external reference for the code in my example.
Click to expand...

would u please upload your excel workbook (macro enabled mode)?i want to re-engineer the code(Reverse Engineering)
 
Here, find attached. Extract the zip to your C-drive's root. Or if you don't have permission there, to folder of your choice and update the link address and destination as needed.

Code modified a bit to check if destination directory exists. If it doesn't, new code will create the directory.
Code: 
Sub Demo()
Dim cel As Range
Dim mySource As String, myFile
For Each cel In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
    If cel.Hyperlinks.Count > 0 Then
        mySource = cel.Hyperlinks(1).Address
        myFile = Split(mySource, "\")
        myFile = myFile(UBound(myFile))
        If Dir(cel.Offset(, 1).Value, vbDirectory Or vbHidden) = vbNullString Then
            MkDir cel.Offset(, 1).Value
        End If
        FileCopy mySource, cel.Offset(, 1).Value & "\" & myFile
    End If
  
Next
End Sub

Refer to thread below to set file to always use absolute path.
https://chandoo.org/forum/threads/links-could-not-be-opened.31763/#post-188773
 

Attachments

  • DemoCode.zip
    247.9 KB · Views: 6
very thank u .
Chihiro said:
Here, find attached. Extract the zip to your C-drive's root. Or if you don't have permission there, to folder of your choice and update the link address and destination as needed.

Code modified a bit to check if destination directory exists. If it doesn't, new code will create the directory.
Code: 
Sub Demo()
Dim cel As Range
Dim mySource As String, myFile
For Each cel In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells
    If cel.Hyperlinks.Count > 0 Then
        mySource = cel.Hyperlinks(1).Address
        myFile = Split(mySource, "\")
        myFile = myFile(UBound(myFile))
        If Dir(cel.Offset(, 1).Value, vbDirectory Or vbHidden) = vbNullString Then
            MkDir cel.Offset(, 1).Value
        End If
        FileCopy mySource, cel.Offset(, 1).Value & "\" & myFile
    End If

Next
End Sub

Refer to thread below to set file to always use absolute path.
https://chandoo.org/forum/threads/links-could-not-be-opened.31763/#post-188773
Click to expand...
thank u very much for your hints.it works perfect.it was very crucial for my job.another question.is there a way to copy a hyperlinked folder content into another?for example column A has a hyperlink folder in which there are many files that we wanna copy them into relevant patch in column B.
 
You must log in or register to reply here.
Back
Top