Problems with a code

B

benjamin.grimm

Registered User.
Local time
Yesterday, 20:20
Joined
Sep 3, 2013
Messages
125
Hey guys,

i have the following code:

The code exports data to a fix Excel sheet from a querry.

The querry has the following elements

  • SAP_Nummer
  • SAP
  • Geris
  • Pauschale
  • SuWID
  • Jahr_Y

Here is my code:


Code: 
Private Sub Befehl5_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
    Dim Dateipfad As String
    Dim dlg As FileDialog
    Dim strPfad 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 dlg = Application.FileDialog(msoFileDialogOpen)
     'Titelzeile
     dlg.Title = "Bitte geben Sie den Pfad der Exceldatei!"
     'standardpfad
     dlg.InitialFileName = "C:\"
     'Button text
     dlg.ButtonName = "speichern"
     dlg.Filters.Clear
     dlg.Filters.Add "Excel", "*.xlsm"
     
        If dlg.show Then
         'wenn Exceldatei angegeben wurde
         Dateipfad = dlg.SelectedItems(1)
     
     
     
        Set xlBook = xlApp.Workbooks.Open(Dateipfad)
        Set xlSheet = xlBook.Sheets("Tabelle1")
        xlSheet.Range("A2:E14").ClearContents
        
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))
            
            
            xlSheet.Name = "" & rstID.Fields("SuWID")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SAP_Nummer, Jahr_Y FROM Abfrage_alles WHERE SuWID = " & rstID.Fields("SuWID"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
        
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    End If
    
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

When i name the Excel Sheets by SuWID, the code works perfectly well.

If i want to name the Excel Sheets by SAP_Nummer. It doesnt work. Why.


Here the problem Zone of the code:

This works:

Code: 
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SuWID"))
            
            
            xlSheet.Name = "" & rstID.Fields("SuWID")

This doesnt work:

Code: 
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SAP_Nummer"))
            
            
            xlSheet.Name = "" & rstID.Fields("SAP_Nummer")

There i get a run time-error 3265. (element is not part of the list)

Hmm, but it is part of the querry.

Does someone has an idea?
 
you are only selecting one field from your table and it is not SAP_Nummer

strSQL = "SELECT SuWID FROM Abfrage_alles GROUP BY SuWID;"
Click to expand...
 
hey thanks for the fast reply.

i changed the code but it still doesnt work.

Here is the code:

Code: 
    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
    Dim Dateipfad As String
    Dim dlg As FileDialog
    Dim strPfad As String
    
    strSQL = "SELECT SAP_Nummer FROM Abfrage_alles_SAP GROUP BY SAP_Nummer;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        
             Set dlg = Application.FileDialog(msoFileDialogOpen)
     'Titelzeile
     dlg.Title = "Bitte geben Sie den Pfad der Exceldatei!"
     'standardpfad
     dlg.InitialFileName = "C:\"
     'Button text
     dlg.ButtonName = "speichern"
     dlg.Filters.Clear
     dlg.Filters.Add "Excel", "*.xlsm"
     
        If dlg.show Then
         'wenn Exceldatei angegeben wurde
         Dateipfad = dlg.SelectedItems(1)
     
     
     
        Set xlBook = xlApp.Workbooks.Open(Dateipfad)
        Set xlSheet = xlBook.Sheets("Tabelle1")
        xlSheet.Range("A2:E14").ClearContents
        
        Do While Not rstID.EOF
            Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SAP_Nummer"))
            
            
            xlSheet.Name = "" & rstID.Fields("SAP_Nummer")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SAP_Nummer, Jahr_Y FROM Abfrage_alles WHERE SAP_Nummer = " & rstID.Fields("SAP_Nummer"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
        
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    End If
    
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Is there a other Problem?
 
i mark the line.

Code: 
    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
    Dim Dateipfad As String
    Dim dlg As FileDialog
    Dim strPfad As String
    
    strSQL = "SELECT SAP_Nummer FROM Abfrage_alles_SAP GROUP BY SAP_Nummer;"
    Set rstID = CurrentDb.OpenRecordset(strSQL)
    If rstID.RecordCount > 0 Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        
             Set dlg = Application.FileDialog(msoFileDialogOpen)
     'Titelzeile
     dlg.Title = "Bitte geben Sie den Pfad der Exceldatei!"
     'standardpfad
     dlg.InitialFileName = "C:\"
     'Button text
     dlg.ButtonName = "speichern"
     dlg.Filters.Clear
     dlg.Filters.Add "Excel", "*.xlsm"
     
        If dlg.show Then
         'wenn Exceldatei angegeben wurde
         Dateipfad = dlg.SelectedItems(1)
     
     
     
        Set xlBook = xlApp.Workbooks.Open(Dateipfad)
        Set xlSheet = xlBook.Sheets("Tabelle1")
        xlSheet.Range("A2:E14").ClearContents
        
        Do While Not rstID.EOF
            [B][SIZE="5"]Set xlSheet = xlBook.Sheets("Tabelle" & rstID.Fields("SAP_Nummer"))[/SIZE][/B]
            
            
            xlSheet.Name = "" & rstID.Fields("SAP_Nummer")
            Set rstGr = CurrentDb.OpenRecordset("SELECT SAP, Geris, Pauschale, SAP_Nummer, Jahr_Y FROM Abfrage_alles WHERE SAP_Nummer = " & rstID.Fields("SAP_Nummer"))
            xlSheet.Range("A2").CopyFromRecordset rstGr
            rstGr.Close
            rstID.MoveNext
        Loop
        
    Else
        MsgBox "No information to export", vbInformation, "No data exported"
    End If
    End If
    
    rstID.Close
    Set rstID = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub
 
It looks like that sheet does not exist - you are naming it on the next line, but without "Tabelle" so I'm assuming one or the other is incorrect

I suggest put

debug.print "Tabelle" & rstID.Fields("SAP_Nummer")

just before the problem line and see what you get, then compare with what you have in the spreadsheet
 
hey thanks for all your help,

but i still cant find the mistake.

I put the database in the Appendix.

It Shows allways the same mistake :confused::confused::banghead:



greetz Benjamin
 

Attachments

Tabelle, I believe, is the german version of the default sheet names "Sheet1" in the english version... By default there should be 3 in a new Excel book, trying to find more will result in this error.

You would be better off using index's rahter than named sheets...
Or probably better yet ignore the default sheets / delete them after creating your own blank sheet using xlBook.Sheets.Add
 
hey namliam

thanks for the fast reply.

Yes Tabelle is the German Version for Sheet1.

I´ve created allready a file with 500 Sheets.

It goes like Tabelle1, Tabelle2, Tabelle3, Tabelle4 and so on.


I dont want to Name the sheet by ID´s. I really want to Name by the SAP-Number.

I thinks it´s better.

I didn´t understand what you exactly mean, what i should do right now.

greetz Benjamin
 
If for some reason you end up with 501 sheets needed or if your name becomes "Tabelle 1" or "Tabelle1 " instead of "Tabelle1" your attempt the set the xlSheet will fail

Your SAP_Nummer is 6500660, unlikely you will have "Tabelle6500660", instead you have "Tabelle1", which is your "SuWID"... Trying to select sheet "Tabelle6500660" is what is causing your problem.

Furthermore having an excel book with 500 sheets is nonsence, particular when you can easily add sheets dynamicaly
 
as per my earlier post
namliam said:
Or probably better yet ignore the default sheets / delete them after creating your own blank sheet using xlBook.Sheets.Add
Click to expand...

added bolding and underlining
 
You must log in or register to reply here.

Similar threads

P
Code Execution Skips with No Error
Replies
15
Views
1,633
cheekybuddha
cheekybuddha
C
  • Question Question
Solved VBA code to find and replace values in exported Excel spreadsheet with hyperlinks
Replies
10
Views
2,286
cgala
C
B
Export of all ID´s via VBA
Replies
10
Views
799
JHB
J
B
Export to excel
Replies
11
Views
739
benjamin.grimm
B
B
Export to special table in Excel
Replies
3
Views
473
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom