Export to special table in Excel

[benjamin.grimm]

hello togehter,

the access database is about contracts.

Each contract has an ID.

So starting from ID1 to ID250.

Right now i export in via VBA to excel.

I have to create before in the excel the 250 tables.

If the ID10 is not existing anymore i still have the table 10 left and then i have to delete this table.

How can i change that?

 Dim xlSheet As Object       'Excel.Worksheet
    Dim rstID As DAO.Recordset, tmpStr As String
    Dim rstGr As DAO.Recordset, strSQL As String
 
    strSQL = "SELECT SuWID FROM Abfrage_alles_laufend GROUP BY SuWID;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        Set xlBook = xlApp.Workbooks.Open("E:\SuW\S-Laufwerk\Tabellen\Auswertung-laufend.xlsm")
        Do While Not rstID.EOF
  
           Set xlSheet = xlBook.Sheets("Tabelle " & rstID.Fields("SuWID"))
            xlSheet.Name = "ID" & rstID.Fields("SuWID")
 
 
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SuWID, Jahr_Y, BT_Name, SAP_Nummer, Vertragsbeginn, Vertragsende FROM Abfrage_alles_laufend WHERE SuWID = " & rstID.Fields("SuWID"))
            
            
            xlSheet.Range("A6").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

greetz benjamin

 

[pbaldy]

If I'm understanding correctly, you could open the Gr recordset before creating the sheet and only continue if it has records (EOF not True).

 

[benjamin.grimm]

and how can i do that?

greetz benjamin

 

[pbaldy]

If Not rstGr.EOF Then