[X]My Assistant

[MrSiezen]

Hi!

With the code below I create an excel file using a draft file. It works fine except when the part below is true, because then then copies a title row with the code below it.

If sGrading <> ![art_grading] Then

It then fails at this line right after the 'End If':

objSheet.Range("A" & iTeller) = ![ticket]

With this error:

1004 application defined or object defined error

But if I hit debug, and then continue the code, it finishes the process without problems and the right results.
I could fetch the fault with the error handler, but I would rather know what the problem is....

CODE

Private Sub cmdCreateUppersPackinglist_Click()

Dim rs As New ADODB.Recordset
Dim factoryname As String

Dim iTeller As Integer

Dim fileSys

Dim crtTicket As String
Dim crtValue As String

Dim navY As Integer
Dim navX As Integer
Dim startY As Integer
Dim startX As Integer
Dim maxnavX As Integer
Dim maxnavY As Integer
Dim highestNavX As Integer
Dim somparen As Long
Dim i As Integer
Dim s As String
Dim SelString As String
Dim sGrading As String


If IsNull(Me.cmbUpperPacklist) Then Exit Sub

Set fileSys = CreateObject("Scripting.FileSystemObject")

strFolder = BrowseForFolderWDef("X:\xxx\" & Nz(ELookup("season & ' ' & year", "seasons", "ID = " & GetDefSeason()), ""))
If strFolder = "False" Then Exit Sub
    
navY = 0
navX = 0
startY = 0
startX = 0

Set objApp = CreateObject("Excel.Application")

sPath = "X:\Apps\ACCESS\blanco uppers packinglist.xlsx"
Set objBook = objApp.Workbooks.open(sPath)
objApp.Visible = False
Set objSheet = objBook.Worksheets("packinglist")

iTeller = 8

rs.open "SELECT .....", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

With rs
    
objSheet.Range("C2") = ![packlist]
objSheet.Range("C3") = ![from_name]
objSheet.Range("C4") = ![date_shipment]
objSheet.Range("C5") = 0
objSheet.Range("I3") = ![to_name]
objSheet.Range("I4") = ![location_adress]
objSheet.Range("I5") = ![location_zipcode] & " " & ![location_city] & " " & ![location_country]
  
iTeller = 7

SetExcelMatenbalk ![art_grading], objSheet, iTeller
sGrading = ![art_grading]
    
iTeller = 8

    Do Until .EOF

        If sGrading <> ![art_grading] Then
            iTeller = iTeller + 1
            SetExcelMatenbalk ![art_grading], objSheet, iTeller
            objSheet.Range("A7:AB7").Copy
            objSheet.Range("A" & iTeller & ":AB" & iTeller).PasteSpecial -4122
            objSheet.Range("A7:H7").Copy
            objSheet.Range("A" & iTeller & ":H" & iTeller).PasteSpecial -4104
            sGrading = ![art_grading]
            iTeller = iTeller + 1
        End If
        objSheet.Range("A" & iTeller) = ![ticket]
        objSheet.Range("B" & iTeller) = ![model]

CODE

Function SetExcelMatenbalk(gradation As String, ByVal objSheet As Object, iRow As Integer)




    'matenbalk vullen op basis gradatie
    If gradation = "EN" Then
        objSheet.Range("I" & iRow) = "3"
        objSheet.Range("J" & iRow) = "3H"
        objSheet.Range("K" & iRow) = "4"
        objSheet.Range("L" & iRow) = "4H"
        objSheet.Range("M" & iRow) = "5"
        objSheet.Range("N" & iRow) = "5H"
        objSheet.Range("O" & iRow) = "6"
        objSheet.Range("P" & iRow) = "6H"
        objSheet.Range("Q" & iRow) = "7"
        objSheet.Range("R" & iRow) = "7H"
        objSheet.Range("S" & iRow) = "8"
        objSheet.Range("T" & iRow) = "8H"
        objSheet.Range("U" & iRow) = "9"
        objSheet.Range("V" & iRow) = "9H"
        objSheet.Range("W" & iRow) = "10"
        objSheet.Range("AB" & iRow) = "total"
    ElseIf gradation = "FR" Then
        objSheet.Range("I" & iRow) = "½"
        objSheet.Range("J" & iRow) = "36"
        objSheet.Range("K" & iRow) = "½"
        objSheet.Range("L" & iRow) = "37"
        objSheet.Range("M" & iRow) = "½"
        objSheet.Range("N" & iRow) = "38"
        objSheet.Range("O" & iRow) = "½"
        objSheet.Range("P" & iRow) = "39"
        objSheet.Range("Q" & iRow) = "½"
        objSheet.Range("R" & iRow) = "40"
        objSheet.Range("S" & iRow) = "½"
        objSheet.Range("T" & iRow) = "41"
        objSheet.Range("U" & iRow) = "½"
        objSheet.Range("V" & iRow) = "42"
        objSheet.Range("W" & iRow) = "½"
        objSheet.Range("X" & iRow) = "43"
        objSheet.Range("Y" & iRow) = "½"
        objSheet.Range("Z" & iRow) = "44"
        objSheet.Range("AA" & iRow) = "½"
        objSheet.Range("AB" & iRow) = "total"
    ElseIf gradation = "FF" Then
        objSheet.Range("I" & iRow) = "35"
        objSheet.Range("J" & iRow) = "36"
        objSheet.Range("K" & iRow) = "37"
        objSheet.Range("L" & iRow) = "38"
        objSheet.Range("M" & iRow) = "39"
        objSheet.Range("N" & iRow) = "40"
        objSheet.Range("O" & iRow) = "41"
        objSheet.Range("P" & iRow) = "42"
        objSheet.Range("Q" & iRow) = "43"
        objSheet.Range("R" & iRow) = "44"
        objSheet.Range("AB" & iRow) = "total"
    End If

End Function

[River59]

I don't see where you are dimming the objSheet? As in Dim objSheet As Excel.Worksheet