Hallo Friends,

I have a problem with Access VBA. I don't have a lot of experince in VBA. It might be a sily question :)

I want to copy datas from access tabels (queries) to Excel sheet. But when I want to move throught the Recordset. It doesn't work. It shows me the number of recondset -1, but I know there are some records in it.

This is the problem. It doesn't go into the loop. Here is the cutout of the code:
For j = 2 To rst.RecordCount
For l = 1 To rst.Fields.Count
MsgBox rst.Fields(l - 1)
objMappe.WorkSheets("Sheet1").Cells(j, l) = rst.Fields(l - 1)
Next l
rst.MoveNext
Next j

Here is the whole code what I wrote. My question is what do I wrong here.

Thank your for your help.

Greetings,
Tester

Sub DataFromAccessToExcel()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim objXlApp As Object
Dim objMappe As Object
Dim objWorkSheet As Object
Dim objSheet As Object
Dim i As Integer
Dim j As Integer
Dim l As Integer

On Error GoTo Fehler
'Excel-Objekt erzeugen (Exce starten)
Set objXlApp = CreateObject("Excel.Application")
'Excel sichtbar machen, weil das Besipiel dann mehr Spaß macht
objXlApp.Visible = True

'Excel-Datei aus Verzeichnis der Datenbank öffnen
Set objMappe = objXlApp.Workbooks.Open(CurrentProject.Path & "\Test.xls")
Set objSheet = objMappe.WorkSheets("Sheet1")
objSheet.Activate

'Set fs = CreateObject("Scripting.FileSystemObject")
'Set textFile = fs.CreateTextFile("D:\TiM\Testumgebung\AccessDB\te st.txt", True)

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

'Tabelle qryRelApplTAL öffnen (editierbar)
rst.Open "Domestic", conn, adOpenDynamic, adLockOptimistic

'MsgBox rst.MaxRecords

'rst.Open "qryRelApplTAL", conn, adOpenForwardOnly, adLockReadOnly

For i = 1 To rst.Fields.Count
objMappe.WorkSheets("Sheet1").Cells(1, i) = rst.Fields(i - 1).Name
Next i

MsgBox rst.RecordCount

For j = 2 To rst.RecordCount
For l = 1 To rst.Fields.Count
MsgBox rst.Fields(l - 1)
objMappe.WorkSheets("Sheet1").Cells(j, l) = rst.Fields(l - 1)
Next l
rst.MoveNext
Next j

rst.Close

'Excel-Datei schließen
'objXlApp.Quit

Exit Sub
Fehler:
MsgBox Err.Number & " " & Err.Description
End Sub

It seems to me that you are going the long way around just to dump data in a spreadsheet. Why not try this:

Just replace the TableOrQueryName with the name of a table or query in your database, and replace the SpreadsheetName.xls with whatever name you want.

This will dump a copy of the table or query on your desktop in a spreadsheet with the name you designate, with a worksheet that has the same name as your table or query.

Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com