looping through multiple workbooks copying file paths as Hyperlinks run time error 5

Davavo

Board Regular
Joined
Aug 3, 2019
Messages
82
Hi,
i think this is just a syntax issue but i cant seem to get it right. If anyone can help i would be very grateful.

this script works at looping through all of the workbooks in a user defined folder and copying a specified range to a big list in the target worksheet of the workbook where the macro is.
Each row in the target worksheet contains all of the data read from one source workbook.
What i want to do is copy an additional range, one cell in each source workbook that contains the filepath of the source workbook.
I want to create this hyperlink in the targetsheet, in column A, in the correct row obviously, and display the row number, as the hyperlink.
I am not quite sure what i am doing wrong with this.
I am getting run time error 5, invalid procedure call of argument.
I guess the syntax is wrong.

Any help would be greatly appreciated.

Code:
Sub ReadExpenses2()'PURPOSE: To loop through all Excel workbooks in a user specified folder, copy a set range from those files, including a cell that specifies the filepath.


Dim wb As Workbook
Dim TWB As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim Trgtws As Worksheet
Dim ws As Worksheet
Dim SWS As Worksheet
Dim ob As ListObject
Dim SpaceCell As Range


Dim UsedRows As Long
Dim LR As Long




Set TWB = ThisWorkbook
Set Trgtws = TWB.Sheets("Expenses")


'remove protection
Sheets("Expenses").Unprotect password:="Dave"


'Turn off hoggs
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


Call killfilterEXP 'shows all data if th filer is in use


'Remove Totals
With Trgtws.ListObjects("TExpenses")
               .ShowTotals = False
End With




'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With


'In Case of Cancel
NextCode:
    myPath = myPath
        If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"
  
'Target Path with Ending Extention
    myFile = Dir(myPath & myExtension)


    'Loop through each Excel file in folder
    Do While myFile <> ""
    
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(FileName:=myPath & myFile)
      Set SWS = wb.Sheets("ALL")
    
 
'---------------------here i want to copy the filepath from K10 of sheetname 'Sumamry' in the source workbook and paste it into the correct row in column A of the target worksheet of the workbook with the macro
    
Dim r As Long


LR = SWS.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
r = UsedRows + 1




SWS.Range("A2" & ":S" & LR).Copy


Trgtws.Range("B" & UsedRows + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Trgtws.Hyperlinks.Add anchor:=Range("A" & UsedRows + 1), Address:=SWS.Range("K10"), TextToDisplay:=r    '----------but this code is wrong ---- ERROR HERE


    
    '----------------------------------------------------------------------------------------------------------------------------------------------------






    'Save and Close Workbook
    wb.Close SaveChanges:=False
      
    'Ensure Workbook has closed before moving on to next line of code
    DoEvents


    'Get next file name
    myFile = Dir
      
Loop
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
solved it

Code:
Dim i As Long
Dim istring As String

LR = SWS.Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
UsedRows = Trgtws.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

SWS.Range("A2" & ":S" & LR).Copy

Trgtws.Range("B" & UsedRows + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    For i = 2 To UsedRows
      If Trgtws.Cells(i, 2) <> "" And Trgtws.Cells(i, 1) = "" Then
         istring = i
         Trgtws.Hyperlinks.Add Trgtws.Range("A" & i), Address:="file:///" & myPath & myFile, TextToDisplay:=istring
      End If
    Next i
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top