I'm making a program that does many different tasks and using it as an opportunity to get more familiar with VBA. Right now, this code is in a separate file until I get the foundation of the code solid then I'll migrate the changes to the actual file it is meant for.
To sum up what I am trying to do:
Take a folder with files in it that use this naming structure: "SOP-JV-001-CHL-Letter Lock for Channel Letters-EN"
Split up that filename using the "-" as the delimiter
Filename[2] would go into COL A
Filename[3] would go into COL B
Filename[4] would go into COL C as a Hyperlink to the physical file
Filename[5] would go into COL D
Here's my code so far that is giving me the error: "Object variable or With block variable not set"
Sub GenerateFileLinks()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\jbishop\Desktop\SOPs With New Names")
i = 1
Dim rngSOPID As Range
Dim rngDeptCode As Range
Dim rngURL As Range
Dim rngLang As Range
'Loop through each file in the directory
For Each objFile In objFolder.Files
'SOP ID Range
rngSOPID = Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
'DeptCode Range
rngDeptCode = Range(Cells(i + 1, 2), Cells(i + 1, 2)).Select
'URL Range
rngURL = Range(Cells(i + 1, 3), Cells(i + 1, 3)).Select
'Lang Range
rngLang = Range(Cells(i + 1, 4), Cells(i + 1, 4)).Select
'Create hyperlink in each cell
ActiveSheet.Hyperlinks.Add Anchor:=rngURL, Address:=objFile.Path, TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
I know it's the "...Anchor:=rngURL" but I just don't know why.
Thanks for all of your help.