UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Export Ezxcel 1004 Error But Continues Without Problems, Access 2016    
 
   
MrSiezen
post Dec 7 2017, 06:18 AM
Post#1



Posts: 2,380
Joined: 15-February 05
From: Den Bosch - Netherlands


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

--------------------
UA Rulez!
Go to the top of the page
 

Posts in this topic



Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 09:50 AM