Macro to set where data should start

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:

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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,408
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
73,448
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
63,408
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
73,448
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

drish

New Member
Joined
Dec 15, 2020
Messages
9
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
9
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
73,448
Office Version
  1. 365
Platform
  1. Windows
What error do you get?
 
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,164,681
Messages
5,838,750
Members
430,567
Latest member
carid

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