Macro to set where data should start

[drish]

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:

My code is as follows:

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)
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 4
Cells(x, 5).Value = Cells(x, 4) & "\" & Cells(x, 3)
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

Is it possible to add some code that will stop the data from outputting to C1 and rather start at C2?


Thanks.

 

[Joe4]

It looks like you are using the variable "i" to indicate the row number. If you initialize it to 1, then the first calculation will make it 2, i.e. add the row in red to your code:

i = 1
For Each xFile In xFolder.Files
i = i + 1
ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
Next

 

[Fluff]

Just add this line as shown

Set xFolder = xFSO.GetFolder(xPath)
i=1
For Each xFile In xFolder.Files

 

[drish]

Just add this line as shown

Set xFolder = xFSO.GetFolder(xPath)
i=1
For Each xFile In xFolder.Files

Thank you @Fluff - worked wonderfully

 

[Joe4]

I guess you missed my earlier post, which said the same thing!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[drish]

I guess you missed my earlier post, which said the same thing!

Thank you also @Joe4 much appreciated!

 

[drish]

My code was working fine yesterday but today there is an error on the below:

'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

Red above has an error and I do not know why as nothing has changed from the initial code.

 

[Fluff]

What error do you get?