Second run of VBA not working (error 91) (1 Viewer)

[fvdb]

I have a button that exports some data to an excel file and works fine the first time

Second time i want to do this i get an error 91 on the line

Selection.Insert Shift:=xlDown

I think i forget to close or finish something after the first time but can not figured it out.

If close the DB and do it again it works normally but i can not do it 2 times after each other.

 Private Sub Knop20_Click()
    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Dim Qdef As DAO.QueryDef
    Dim accapp As Access.Application
    
  
    'On Error GoTo Errorhandling
    
        Set objXL = CreateObject("Excel.Application")
        objXL.Visible = True
         
        Set xlWB = objXL.Workbooks.Open("c:\TD GBS\Standaardisatie\io_lijst.xltx")
        
        'Prijzen
        Set xlWS = xlWB.Worksheets("Prijzen")
        Set rst = CurrentDb.OpenRecordset("qry_Excel_Export_Prijzen")
         lngCount = 1
         
        Do Until rst.EOF
         
            With xlWS
                 .Cells(lngCount, 1).Value = rst!ART_ID
                 .Cells(lngCount, 2).Value = rst!PRIJS
            End With
            lngCount = lngCount + 1
            rst.MoveNext
        Loop
 
rst.Close
Set rst = Nothing
Set xlWS = Nothing
        
        'Datapunten
        Set xlWS = xlWB.Worksheets("Datapunten")
        
        Set Qdef = CurrentDb.QueryDefs("qry_Excel_Export")
         With Qdef
         .Parameters(0) = Me.Keuzelijst7
         Set rst = .OpenRecordset
         End With
 
        aantal_rijen = DCount("*", "qry_Excel_Export") - 1
         '   Open it
         
        With xlWS
            For i = 0 To aantal_rijen
            .Cells(1, 9).Select
            .Rows("9:9").Select
            Selection.Insert Shift:=xlDown
            Next i
        End With
 
        lngCount = 9
         
        Do Until rst.EOF
        
            With xlWS
                 .Cells(lngCount, 1).Value = rst!Component
                 .Cells(lngCount, 2).Value = rst!OMSCH
                 .Cells(lngCount, 3).Value = rst!AI
                 .Cells(lngCount, 4).Value = rst!AO
                 .Cells(lngCount, 5).Value = rst!DI
                 .Cells(lngCount, 6).Value = rst!DO
                 .Cells(lngCount, 7).Value = rst!BUS
                 .Cells(lngCount, 8).Value = rst!Veldregelaar
                 .Cells(lngCount, 9).Value = rst!OPMERKING
                 .Cells(lngCount, 11).Value = rst!datapunten
                 .Cells(lngCount, 12).Value = rst!Indienstname
                 .Cells(lngCount, 13).Value = rst!Totaal
            End With
            lngCount = lngCount + 1
            rst.MoveNext
        Loop
 
rst.Close
Set rst = Nothing
Set xlWS = Nothing
Set Qdef = Nothing
Set objXL = Nothing
 Exit Sub
 Errorhandling:
     MsgBox Err.Number & Err.Description
 
End Sub

 

[kevlray]

Not sure that it matters, but you do not close or clear the value of xlWB. Maybe Access is retaining that value somewhere.

 

[JHB]

You're missing the . in front of it!

 [B][COLOR=Red].[/COLOR][/B]Selection.Insert Shift:=xlDown

 

[fvdb]

You're missing the . in front of it!

 [B][COLOR=red].[/COLOR][/B]Selection.Insert Shift:=xlDown

Then it wont run at all


Tried an other syntax wich works

         With xlWS
            For i = 0 To aantal_rijen
            .Rows("9:9").Insert Shift:=xlDown
            Next i
        End With