none
Adding links of files, seperating filenames with Split() - Error: Object variable or With block variable not set RRS feed

  • Question

  • 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.

    Tuesday, July 16, 2019 1:28 PM

All replies

  • Maybe you should do something like this (in several places):

       Set rngSOPID = Range(Cells(i + 1, 1), Cells(i + 1, 1))

    If you also want to select the range, then add this:

       rngSOPID.Select

     

    Wednesday, July 17, 2019 6:43 AM