Compile Error: Object Required (VBA)

Question

My code is supposed to take contact information from one workbook, create a new one for each person on the list and transfer their info. When I try to run my macro, it gives me an error at the line Set pathOrig = ActiveWorkbook.path. It says

compile error: object required

Anyone knows what's my error here? (Sorry for the bits of French in my code)

Sub generateFichier()

    Dim orig, dest As Range
    Dim classOrig, classDest As Workbook
    Dim pathOrig As String
    Dim nom, prenom, faitPar As String
    Dim sommeCap, sommeCapActuel As Long

    Set classOrig = ActiveWorkbook
    Set pathOrig = ActiveWorkbook.path

    Set orig = Sheets("clients").Range("A2")

    For i = 1 To 7 Step 1

        nom = orig.Offset(i, 0).Value
        prenom = orig.Offset(i, 1).Value
        sommeCap = orig.Offset(i, 3).Value + orig.Offset(i, 5).Value
        sommeCapActuel = orig.Offset(i, 4).Value + orig.Offset(i, 7).Value
        faitPar = classOrig.Sheets(1).PageSetup.RightFooter

        Dim nomFichier As String
        nomFichier = nom & ".xls"

        Set classDest = Workbooks.Add(pathOrig & "\" & nomFichier)
        classDest.Sheets(1).Range("B1").Value = nom
        classDest.Sheets(1).Range("B2").Value = prenom
        classDest.Sheets(1).Range("A5").Value = sommeCap
        classDest.Sheets(1).Range("B5").Value = sommeCapActuel
        classDest.Sheets(1).Range("B13").Value = faitPar


        For j = 1 To 6 Step 1

            Select Case j

                Case 1 To 3

                    classOrig.Cells(i, j).Value = classDest.Sheets(1).Cells(j + 7, 2).Value

                Case 4 To 6

                    classOrig.Cells(i, j).Value = classDest.Sheets(1).Cells(j + 4, 3).Value

            End Select

        Next j

        classDest.SaveAs Filename:=pathOrig & "\" & nomFichier, FileFormat:=xlNormal

    Next i

End Sub

btw, it should be Dim classOrig As Workbook, classDest As Workbook. You need to define each piece or the undeclared become variants — user4039065
– user4039065, Commented Apr 14, 2018 at 21:35

user4039065 · Accepted Answer · 2018-04-14 21:20:56Z

1

The workbook's path is a string to the folder containing the workbook, not the workbook itself. As such, you do not Set an object var to the .Path; you simple assign the .Path to a string variable.

dim pathOrig as string
pathOrig = activeworkbook.path
debug.print pathOrig

answered Apr 14, 2018 at 21:20

user4039065

Sign up to request clarification or add additional context in comments.

5 Comments

K.Dᴀᴠɪs Over a year ago

Alternatively, pathOrig = ThisWorkbook.Path. But that's just my preference of using ThisWorkbook over ActiveWorkbook unless I have a reason to use the latter.

user4039065 Over a year ago

ThisWorkbook is the workbook containing the code; that is not always the activeworkbook (the workbook foremost in focus).

JuliPX Over a year ago

"The workbook's path is a string to the folder containing the workbook, not the workbook itself." It's exactly what I want tho. The path to the containing folder. Also I removed the "set" part. Now it just gives me an error saying "Variable not defined" but doesn't point to any line. Also tried running the code with "ThisWorkbook", still no luck.

user4039065 Over a year ago

Change this declaration Dim sommeCap As Long, sommeCapActuel As Long, i As Long, j As Long and remember to dim all vars.

Subodh Tiwari sktneer Over a year ago

The error is quite obvious and that means you didn't declare a variable being used in the code. When you get that error, the variable in question will be highlighted in blue. Also, declare the datatype for each variable explicitly. e.g. when you use Dim orig, dest As Range that simply means, dest is declared as a Range and orig is declared as Variant.

InfiniteArray · Accepted Answer · 2018-04-14 22:03:09Z

1

You don't need to "Set" a string and pathOrig is just a string, not a workbook object like classOrig.

Just delete the Set statement from that line and you should be good.

answered Apr 14, 2018 at 22:03

InfiniteArray

111 bronze badge

4 Comments

user4039065 Over a year ago

There's also the issue of misdeclared vars and undeclared vars. While the OP's code gives no indication that he/she is trying to run the code under Option Explicit, from the comments it does appear to be the case.

InfiniteArray Over a year ago

Option Explicit is very helpful self-discipline. The extra minute it takes to declare the variables will often payoff in reduced debugging.

user4039065 Over a year ago

I completely agree. I only brought it up because as soon as the op fixed the Set problem, he/she had an undeclared var issue he/she had problems resolving.

JuliPX Over a year ago

I was actually using Option Explicit and I removed it and removed "set" from my code and it seems to work. Now the compilator managed to continue reading the code and found and error elsewhere unrelated to this issue. Thank you for your help!

Collectives™ on Stack Overflow

Compile Error: Object Required (VBA)

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related