Error in VBA script combining columns

drish

New Member
Joined
Dec 15, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have the following macro which does the following:

  1. Get the file names from a folder on Windows
  2. Combines the name with the folder name
  3. Hyperlinks the conjoined names

How it looks in the table (the folder path is longer now and not sure if that is a problem in the script as to where the error comes up?)

1613554868765.png

I have multiple rows (320) and when I run the macro, there is this error on this line (highlighted in red)

Sub Combined()

'Gets file names

Dim xFSO As Object
Dim xFolder As Object
Dim xFile As Object
Dim xFiDialog As FileDialog
Dim xPath As String
Dim i As Integer
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
xPath = xFiDialog.SelectedItems(1)
End If
Set xFiDialog = Nothing
If xPath = "" Then Exit Sub
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFSO.GetFolder(xPath)
i = 1 'to start on row 2 and keep the heading cell
For Each xFile In xFolder.Files
i = i + 1
ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
Next

'Adds the name to folder

Dim x As Integer

For x = 1 To 320 'Number will change depending on how many rows you have to fill
Cells(x, 5).Value = Cells(x, 4) & "\" & Cells(x, 3) '5/4/3 is the columns here
Next x

'Converts each text hyperlink selected into a working hyperlink

Dim xCell As Range

For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

The macro does not run and add all the files to the rows - just the first row and I am not sure what the problem is?

Any assistance is gladly appreciated.

Thanks.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub Combined()
Dim xFSO As Object
Dim xFolder As Object
Dim xFile As Object
Dim xFiDialog As FileDialog
Dim xPath As String
Dim i As Integer
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
    xPath = xFiDialog.SelectedItems(1)
End If
Set xFiDialog = Nothing
If xPath = "" Then Exit Sub
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFSO.GetFolder(xPath)
i = 1 'to start on row 2 and keep the heading cell
For Each xFile In xFolder.Files
    i = i + 1
    ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
    Cells(i, 5).Value = Cells(i, 4) & "\" & Cells(i, 3) '5/4/3 is the columns here
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 5), Address:=Cells(i, 5).Formula
Next
End Sub
 
Solution

drish

New Member
Joined
Dec 15, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub Combined()
Dim xFSO As Object
Dim xFolder As Object
Dim xFile As Object
Dim xFiDialog As FileDialog
Dim xPath As String
Dim i As Integer
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
    xPath = xFiDialog.SelectedItems(1)
End If
Set xFiDialog = Nothing
If xPath = "" Then Exit Sub
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFSO.GetFolder(xPath)
i = 1 'to start on row 2 and keep the heading cell
For Each xFile In xFolder.Files
    i = i + 1
    ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
    Cells(i, 5).Value = Cells(i, 4) & "\" & Cells(i, 3) '5/4/3 is the columns here
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 5), Address:=Cells(i, 5).Formula
Next
End Sub

Thank you so much! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,776
Members
425,492
Latest member
blueexcel123

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