VBA error for formula

Status
Not open for further replies.

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
143
Office Version
  1. 2010
Hey guys!

I hope you are all well.

I have the following which loops into a folder of excel files and pulls data and pastes onto active worksheet, however I am trying to do a hyperlink formula so I can open the specific excel file for each row of data but the problem I'm getting is that it only pastes the last rows hyperlink for the whole column?

e.g
Column A Column B
Abc Xyz.xls
EFG Xyz.xls
XYZ Xyz.xls

Please see below and many thanks in advance!

VBA Code:
Sub GetData()

   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim mylinkpath As String
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim filename As String
   Dim RowTarget As Long         'output row

   RowTarget = 2
   
   'check the folder exists
     If Not FileFolderExists("C:\Users\me\") Then
      MsgBox "Specified folder does not exist, exiting!"
      Exit Sub
   End If
   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet1")
   
   'loop through the Excel files in the folder
   mylinkpath = ("C:\Users\me\")
   sFile = Dir("C:\Users\me\" & "*.xls*")
   Do Until sFile = ""
   
      Set wbSource = Workbooks.Open("C:\Users\me\" & sFile)
      Set wsSource = wbSource.Worksheets(1)
      
      'import the data
      With wsTarget
         .Range("A" & RowTarget).Value = wsSource.Range("B4:C4").Value         'ID No
         
         .Range("B2").Formula = "=IF($C2="""","""",Hyperlink(""C:\Users\me\" & sFile & """,""" & sFile & """))"
         .Range("B2:B50").FillDown
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Thread closed at OP's request
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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
Top