export data to excel, change range every months

[benjamin.grimm]

Hello together,

i export data via vba to excel.

Private Sub Befehl0_Click()
On Error GoTo fehler

' Übersicht aufrufen
 
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim rst As DAO.Recordset
'Excel-Objekt öbffnen und Workbook hinzufügen
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("Z:\BUS MMM C\20000 Bestandsmanagement & Net Assets\21000 Bestandsführung\21200 Reporting\21240 Bestände Vorratsfzge\2014\Datenbank\Export.xlsx")
'In Tabellenblatt Gesamt schreiben
Set xlSheet = xlBook.Worksheets(1)
Set rst = CurrentDb.OpenRecordset("Vor_MB_Gesamt")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Citaro schreiben
Set xlSheet = xlBook.Worksheets(2)
Set rst = CurrentDb.OpenRecordset("Vor_Citaro")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close

'In Tabellenblatt Conecto schreiben
Set xlSheet = xlBook.Worksheets(3)
Set rst = CurrentDb.OpenRecordset("Vor_Conecto")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Integro schreiben
Set xlSheet = xlBook.Worksheets(4)
Set rst = CurrentDb.OpenRecordset("Vor_Integro")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Intouro schreiben
Set xlSheet = xlBook.Worksheets(5)
Set rst = CurrentDb.OpenRecordset("Vor_Intouro")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Tourino schreiben
Set xlSheet = xlBook.Worksheets(6)
Set rst = CurrentDb.OpenRecordset("Vor_Tourino")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Tourismo schreiben
Set xlSheet = xlBook.Worksheets(7)
Set rst = CurrentDb.OpenRecordset("Vor_Tourismo")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close
'In Tabellenblatt Travego schreiben
Set xlSheet = xlBook.Worksheets(8)
Set rst = CurrentDb.OpenRecordset("Vor_Travego")
xlSheet.Range("R7").CopyFromRecordset rst
rst.Close

'Objekte schließen
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
 
Exit Sub

fehler:
MsgBox Err.Description, vbCritical, "Excel Datei ist bereits geöffnet"

Right now i transfer it to xlsheet.range("r7").

How can i do it that it get changed every months.

No in april 2014 it is R7
in may 2014 it is t7
in june 2014 it its u7
in july 2014 it is v7

and so on.

greetz benjamin

 

[pr2-eugin]

Replace Range("R7") to Range(Chr(78 + Month(Date)) & "7")

 

[benjamin.grimm]

i changed it to

xlSheet.Range(Chr(78) + Month(Date) & "7").CopyFromRecordset rst

but then i get the error message 13 : data types incompatible

What did i do wrong?

greetz benjamin

 

[pr2-eugin]

Sorry my bad. I have edited the code.

 

[benjamin.grimm]

and what can i do now?

greetz benjamin

 

[benjamin.grimm]

i tried to this,

but that didnt work either

xlSheet.Range(xlSheet.Cells((Month(Date) - 1) * 13 + 1, 7)).CopyFromRecordset rst

 

[pr2-eugin]

Just use my code in Post#2.

 

[namliam]

Dont embed the xlSheel.Cells but use it directly....

xlSheet.Cells(row,Col).copyfromrecordset rst

and keep in mind, Cells(row, column) its Row then Column not reverse like in a range

 

[benjamin.grimm]

hmmm

there is no code in post2#


sorry i am confused.

 

[pr2-eugin]

Range(Chr(78 + Month(Date)) & "7")

 

[Brianwarnock]

Wouldn't it be simpler to use

Cells(7,13+Month(Date))

Brian

 

[benjamin.grimm]

great it works. Thanks so much !!!!!