benjamin.grimm
Registered User.
- Local time
- Today, 02:17
- Joined
- Sep 3, 2013
- Messages
- 125
hello,
i export contracts via VBA from access to excel.
In total i have around 200 contracts.
In Excel i create a diagram by the exported datas.
The diagram have to be in excel.
The vba code export all the data, but i have to create the sheets before.
So for ID 1 i have to create the Tabelle1 and for ID 140 i have to create the table 140.
So i usually create 200 same sheets.
If a contract gets deleted then the sheet with the special ID is empty.
How can i do it without creating the sheets before?
Here the complete code:
Greetz Benjamin
i export contracts via VBA from access to excel.
In total i have around 200 contracts.
In Excel i create a diagram by the exported datas.
The diagram have to be in excel.
The vba code export all the data, but i have to create the sheets before.
Code:
[FONT=Courier New] Set xlSheet = xlBook.Sheets("Tabelle " & rstID.Fields("SuWID"))
xlSheet.Name = "ID" & rstID.Fields("SuWID")[/FONT]
So for ID 1 i have to create the Tabelle1 and for ID 140 i have to create the table 140.
So i usually create 200 same sheets.
If a contract gets deleted then the sheet with the special ID is empty.
How can i do it without creating the sheets before?
Here the complete code:
Code:
[FONT=Courier New]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 Not rstID.EOF Then
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.WorkBooks.Open("S:\xyz\Auswertung-komplett.xlsm")
Do While Not rstID.EOF
Set xlSheet = xlBook.Sheets("Tabelle " & rstID.Fields("SuWID"))
xlSheet.Name = "ID" & rstID.Fields("SuWID")
strSQL = "SELECT SAP, Geris, Pauschale, SuWID, Jahr_Y, BT_Name" _
& ", SAP_Nummer, Vertragsbeginn, Vertragsende" _
& " FROM Abfrage_alles" _
& " WHERE SuWID = " & rstID.Fields("SuWID")
Set rstGr = CurrentDb.OpenRecordset(strSQL)
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[/FONT]
Greetz Benjamin