SOLVED

Macro to set where data should start

%3CLINGO-SUB%20id%3D%22lingo-sub-2009455%22%20slang%3D%22en-US%22%3EMacro%20to%20set%20where%20data%20should%20start%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009455%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20macro%20which%20does%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EGet%20the%20file%20names%20from%20a%20folder%20on%20Windows%3C%2FLI%3E%3CLI%3ECombines%20the%20name%20with%20the%20folder%20name%26nbsp%3B%3C%2FLI%3E%3CLI%3EHyperlinks%20the%20conjoined%20names%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20it%20looks%20in%20the%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DrishM_0-1608644792706.png%22%20style%3D%22width%3A%20514px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242220iFC0BDC8AA135C7AA%2Fimage-dimensions%2F514x31%3Fv%3D1.0%22%20width%3D%22514%22%20height%3D%2231%22%20role%3D%22button%22%20title%3D%22DrishM_0-1608644792706.png%22%20alt%3D%22DrishM_0-1608644792706.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20code%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Combined()%3C%2FP%3E%3CP%3E'Gets%20file%20names%3C%2FP%3E%3CP%3EDim%20xFSO%20As%20Object%3CBR%20%2F%3EDim%20xFolder%20As%20Object%3CBR%20%2F%3EDim%20xFile%20As%20Object%3CBR%20%2F%3EDim%20xFiDialog%20As%20FileDialog%3CBR%20%2F%3EDim%20xPath%20As%20String%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3ESet%20xFiDialog%20%3D%20Application.FileDialog(msoFileDialogFolderPicker)%3CBR%20%2F%3EIf%20xFiDialog.Show%20%3D%20-1%20Then%3CBR%20%2F%3ExPath%20%3D%20xFiDialog.SelectedItems(1)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ESet%20xFiDialog%20%3D%20Nothing%3CBR%20%2F%3EIf%20xPath%20%3D%20%22%22%20Then%20Exit%20Sub%3CBR%20%2F%3ESet%20xFSO%20%3D%20CreateObject(%22Scripting.FileSystemObject%22)%3CBR%20%2F%3ESet%20xFolder%20%3D%20xFSO.GetFolder(xPath)%3CBR%20%2F%3EFor%20Each%20xFile%20In%20xFolder.Files%3CBR%20%2F%3Ei%20%3D%20i%20%2B%201%3CBR%20%2F%3EActiveSheet.Hyperlinks.Add%20Cells(i%2C%203)%2C%20xFile.Path%2C%20%2C%20%2C%20xFile.Name%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3E'Adds%20the%20name%20to%20folder%3C%2FP%3E%3CP%3EDim%20x%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20x%20%3D%201%20To%204%3CBR%20%2F%3ECells(x%2C%205).Value%20%3D%20Cells(x%2C%204)%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Cells(x%2C%203)%3CBR%20%2F%3ENext%20x%3C%2FP%3E%3CP%3E'Converts%20each%20text%20hyperlink%20selected%20into%20a%20working%20hyperlink%3C%2FP%3E%3CP%3EDim%20xCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20xCell%20In%20Selection%3CBR%20%2F%3EActiveSheet.Hyperlinks.Add%20Anchor%3A%3DxCell%2C%20Address%3A%3DxCell.Formula%3CBR%20%2F%3ENext%20xCell%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIs%20it%20possible%20to%20add%20some%20code%20that%20will%20stop%20the%20data%20from%20outputting%20to%20C1%20and%20rather%20start%20at%20C2%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2009455%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2009473%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20set%20where%20data%20should%20start%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2009473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893158%22%20target%3D%22_blank%22%3E%40DrishM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20looks%20like%20you%20need%20to%20define%20the%20i%20integer.%20Add%20this%20after%20the%26nbsp%3BDim%20i%20as%20Integer%20line%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3Ei%20%3D%202%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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:

 

DrishM_0-1608644792706.png

 

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.

 

1 Reply
Best Response confirmed by DrishM (Occasional Contributor)
Solution

@DrishM 

It looks like you need to define the i integer. Add this after the Dim i as Integer line

i = 2