CODEWITHSUNDEEP
excelvba
JuliPX
JuliPX

Reputation: 11

Compile Error: Object Required (VBA)

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

Upvotes: 1

Views: 1588

Answers (2)

InfiniteArray
InfiniteArray

Reputation: 11

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.

Upvotes: 1

user4039065
user4039065

Reputation:

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

Upvotes: 1

Related Questions