Macro to set where data should start

drish

New Member
Joined
Dec 15, 2020
Messages
8
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:

1608645190153.png


My code is as follows:

VBA Code:
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,736
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
i = 1
For Each xFile In xFolder.Files
i = i + 1
ActiveSheet.Hyperlinks.Add Cells(i, 3), xFile.Path, , , xFile.Name
Next
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
Just add this line as shown
Rich (BB code):
Set xFolder = xFSO.GetFolder(xPath)
i=1
For Each xFile In xFolder.Files
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,736
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

drish

New Member
Joined
Dec 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I guess you missed my earlier post, which said the same thing!
;)
Thank you also @Joe4 :) much appreciated!
 

drish

New Member
Joined
Dec 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,771
Office Version
  1. 365
Platform
  1. Windows
What error do you get?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,087
Messages
5,628,593
Members
416,326
Latest member
NinaChristal

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