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

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

Sub GenerateFileLinks()
    ActiveSheet.Cells.Clear

    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 = 0

    'Loop through each file in the directory
    For Each objFile In objFolder.Files
        'SOP ID Range
        Set rngSOPID = Range(Cells(i + 1, 1), Cells(i + 1, 1))
        'DeptCode Range
        Set rngDeptCode = Range(Cells(i + 1, 2), Cells(i + 1, 2))
        'URL Range
        Set rngURL = Range(Cells(i + 1, 3), Cells(i + 1, 3))
        'Lang Range
        Set rngLang = Range(Cells(i + 1, 4), Cells(i + 1, 4))

        Set Filename = Split(objFile.Name, "-")

        'Create hyperlink in each cell
        ActiveSheet.Hyperlinks.Add Anchor:=rngURL, Address:=objFile.Path, TextToDisplay:=Filename(4)

        i = i + 1
    Next objFile
End Sub

Okay, getting an error: Type Mismatch...I've used a split statement like that before. It's so close!

You should remove your `.Select` and read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • If then the error is still there please tell in which line you get the error. — Pᴇʜ, Jul 16 '19 at 13:41
You need `Set` with objects, and remove the `Select` - for example: `Set rngSOPID = Range(Cells(i + 1, 1), Cells(i + 1, 1))` — Rory, Jul 16 '19 at 13:41
Why not, you are selecting the Cell with `i + 1` that is 2 if i = 1 — Mikku, Jul 16 '19 at 13:53

score 2 · Accepted Answer · answered Jul 16 '19 at 13:57

Set rngSOPID = Range(Cells(i + 1, 1), Cells(i + 1, 1)) is not exactly needed, if it is not used later. And in general, VBA provides quite nice way to refer to a cell with Worksheets(Cells(row,column).

The code below loops through the files of a folder and writes their names in the first column. Then, if the name consists of more than 4 -, these are written down in the next columns:

Sub GenerateFileLinks()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("C:\Users\putSomePath\")

    Dim i As Long: i = 1

    For Each objFile In objFolder.Files
        With Worksheets(1)
            .Cells(i, 1) = objFile
            If UBound(Split(objFile, "-")) > 3 Then
                .Cells(i, 2) = Split(objFile, "-")(0)
                .Cells(i, 3) = Split(objFile, "-")(1)
                .Cells(i, 4) = Split(objFile, "-")(2)
                .Cells(i, 5) = Split(objFile, "-")(3)
            End If
        End With
        i = i + 1
    Next objFile

End Sub

@mongoose00318 - updating your question with my code actually changes the question, which is not a good idea. If you have further questions, concerning my code, it is a good idea to ask a new question. Do not forget to include [mcve] there. — Vityata, Jul 16 '19 at 14:31
Do you have a suggestion as to how I should minimize the code? — mongoose00318, Jul 16 '19 at 14:44
@mongoose00318 - then you have about 30 minutes to try to manage to do something, using the answer of z32a7ul and `ActiveSheet.Hyperlinks.Add Anchor:=rngURL, Address:=objFile.Path, TextToDisplay:=objFile.Name` ... — Vityata, Jul 16 '19 at 14:50

score 1 · Answer 2 · answered Jul 16 '19 at 13:57

Your code is missing the part to split the filename.
You don't have to use Range because the Cells(x, y) is a Range.
You don't need .Select but should change the .Value of the Cell.
I think your comments are superfluous because the variable names are quite descriptive.

Try this:

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
        Dim varParts As Variant: varParts = Split(objFile.Name, "-")

        Set rngSOPID = Cells(i + 1, 1)
        rngSOPID.Value = varParts(2)

        Set rngDeptCode = Cells(i + 1, 2)
        rngDeptCode.Value = varParts(3)

        Set rngURL = Cells(i + 1, 3)
        rngURL.Value = varParts(4)
        ActiveSheet.Hyperlinks.Add Anchor:=rngURL, Address:=objFile.Path, TextToDisplay:=objFile.Name

        Set rngLang = Cells(i + 1, 4)
        rngLang.Value = varParts(5)

        i = i + 1
    Next objFile
End Sub

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

2 Answers2