exporting data by ID, getting in an error

[benjamin.grimm]

hello guys,

i Export data by the following code.

It works actually but when i go back to Access i get the following error message

runntime error 424, object is necessary.

Private Sub Befehl1_Click()
    Dim xlApp As Object         'Excel.Application
    Dim xlBook As Object        'Excel.Workbook
    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 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("C:\Users\GRIMBEN\Desktop\SuW\S-Laufwerk\Tabellen\Try-alle.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 WHERE SuWID = " & rstID.Fields("SuWID"))
            
            
           [COLOR=yellow] xlSheet.Range("A6").CopyFromRecordset rstGr
[/COLOR]            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

 

[pr2-eugin]

What do you mean when you go back to Access?

 

[benjamin.grimm]

the Excel gets automatically opened, then i am in the Excel sheet.

The data gets exported by the ID.

Then i save the Excel sheet.

Then i go back to Access and then there is the following error message

 

[pr2-eugin]

So where is the Code to Save the Excel sheet from the Access code? You are not manually closing the File whilst the Code is copying data are you?

 

[benjamin.grimm]

no i am not closing it.

Now it doesnt Export all the datas anymore.

I´ll get the following run time error:

run time error 50290.

application or object defined error in this line

xlSheet.Name = "ID" & rstID.Fields("SuWID")




hmm

 

[pr2-eugin]

Are you the only person using the File when this code is run? Does the sheets you are referring to exist before trying to manipulate them?

 

[benjamin.grimm]

i am the only one, which is using this file.

 

[benjamin.grimm]

if i just let it run, it works. Then there is no runtime error. This is strange or?

 

[benjamin.grimm]

but one question more.

Well i have in the Excel file 500 tables.

The ID´s go from 1 to 200.

I just Export data from contracts which run just still one year.

This is around 25 contracts.

No i want that it just Shows the Special sheet, with the ID names.

All the Sheets which are Name by table should get deleted.

How can i do that?