Solved

Query or code problem ? (exporting recordset to excel)

Posted on 2003-12-08
15
834 Views
Last Modified: 2013-11-26
Hi !

I have this code that exports a recordset to an excel workbook in an Access Database (Access 2002).
My procedure is exporting the whole thing and is dynamic (depending on fields selected and number of records).

The problem is that i use an inner join query and it only returns
me the first result, even though the same query in Access returns two results !!

I have the source here (it's a little easy code) :
http://pages.infinit.net/rouxjean/bd1.zip

Open the export form Export and click on the Excel button.
Compare the result of the export with the Query Requête 1.
I use the same SQL query for both...

Please help this is important !!!
0
Comment
Question by:rouxjean
  • 8
  • 6
15 Comments
 
LVL 8

Expert Comment

by:ampapa
ID: 9900876
You count was off on your for next routine:

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst
0
 
LVL 39

Expert Comment

by:appari
ID: 9901118
change

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i

to

' Exporter les données
rstSource.MoveFirst
while not     rstSource.EOF
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
wend

0
 

Author Comment

by:rouxjean
ID: 9901216
Thx for your replies.
Unfortunately, the solution you provide is not dynamic so i changed the 3 for rstSource.RecordCount.
When i used my sql string, it didn't work either, i had the same result as before.
Any idea ??
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9901310
You count is dictated by the "i" variable. Replacing the "3" with rstsource.recordcount wil surely error.

What kind of error are you recieving?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9901337
The output that you recieve when running the for next routine is exactly tied to the number of records in your table.

This is what I get when I run the export routine:
Nom      Prenom      Solde
Roux      Jean-Sébastien      $500.00
Milot      Valérie      $1,000.00
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9901356
Her is the full source:

Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"

Set rstSource = CurrentDb.OpenRecordset(strSQL)

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 0 To rstSource.RecordCount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value           <=================
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0
 

Author Comment

by:rouxjean
ID: 9903256
What i'm trying to say is what if there is a third record, the code won't work anymore !!! It will show only the first two !!!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:rouxjean
ID: 9903294
What is strange though is if my recordset is only the content of a table, it'll work...
0
 
LVL 8

Accepted Solution

by:
ampapa earned 500 total points
ID: 9903498
O.K here is the scoop. In the help file it states "The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object."

So because you were starting at the first record "rstSource.MoveFirst" your count is off. I just added a variable to get the count. Hope this helps.

ampapa,



Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer                     <=====================
Dim strSQL As String

'strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE (([soldes]![IDClient]=[clients]![ID]));"
strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.ID WHERE ((([clients]![ID])=[soldes]![IDClient]));"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast                                <=====================
rstcount = rstSource.RecordCount           <=====================

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount 'rstSource.RecordCount               <=====================
    For j = 0 To rstSource.Fields.count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la première cellule pour déselectionner
ExcelWSheet.Cells(i + 1, j - 1).Select

'ActiveCell.FormulaR1C1 = "=SUM(B1:B2)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
0
 

Author Comment

by:rouxjean
ID: 9908182
Thanks :-)
I have this so far :
======================================================================
Option Compare Database
Option Explicit

Private Sub Commande0_Click()
Dim rstSource As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim ExcelWBook As Excel.Workbook
Dim ExcelWSheet As Excel.Worksheet
Dim i, j, rstcount As Integer
Dim strSQL As String

strSQL = "SELECT clients.Nom, clients.Prenom, soldes.Solde FROM clients INNER JOIN soldes ON clients.ID = soldes.IDClient;"
Set rstSource = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rstSource.MoveLast
'Compter le nombre d'enregistrements dans le recordset
rstcount = rstSource.RecordCount

'Avoir ou créer l'objet Excel
On Error Resume Next
Set ExcelApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set ExcelApp = New Excel.Application
        Err.Clear
End If

' Créer le classeur Excel
Set ExcelWBook = ExcelApp.Workbooks.Add
Set ExcelWSheet = ExcelWBook.ActiveSheet

' Exporter le nom des champs
For j = 0 To rstSource.Fields.Count
    ExcelWSheet.Cells(2, j + 1) = rstSource.Fields(j).Name
Next j

' Exporter les données
rstSource.MoveFirst
For i = 1 To rstcount
    For j = 0 To rstSource.Fields.Count
        ExcelWSheet.Cells(i + 3, j + 1) = rstSource.Fields(j).Value
    Next j
    rstSource.MoveNext
Next i
rstSource.MoveFirst

' Ajuster la largeur des colonnes automatiquement
For i = 1 To rstSource.Fields.Count
    ExcelWSheet.Columns(i).AutoFit
Next i

' Sélection de la dernière cellule pour calculer
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).Select

'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

' Afficher Excel pour modifications ultérieures
ExcelApp.Visible = True

'On flushe l'objet
Set ExcelApp = Nothing
Set ExcelWBook = Nothing
Set ExcelWSheet = Nothing
End Sub
======================================================================
The problem is when i click on the button to export once, close Excel without saving and click on the button again, Excel seems to freeze.

Any idea ?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9908459
You should "quit" Excel before setting the object to nothing that may help.

ExcelApp.quit

You might try using  -  "CreateObject("Excel.Application")" instead of "GetObject(, "Excel.Application")"
0
 

Author Comment

by:rouxjean
ID: 9908538
Using create object fixed the problem :-D
However, this line :
===========================================
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
===========================================
executes only once even when i click on the button to export twice !!!
So i guess there is still problems with the object closing.
I have to close and re-load the database in order for it to work again.
I have no error at all, the focus is on the good cell but the sum is just ignored.

Any idea ?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9908866
Replace:
'Calculer le total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"

with:
ExcelWSheet.Cells(rstcount + 4, rstSource.Fields.Count).FormulaR1C1 = "=SUM(R[-" & rstcount & "]C:R[-1]C)"
0
 

Author Comment

by:rouxjean
ID: 9911072
Once again, that worked great :-)
Many thanks for your help !!!
0
 
LVL 8

Expert Comment

by:ampapa
ID: 9911533
Your very welcome....
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB6 Compile Compatibility Issue 4 90
Copy a row 12 59
How to Add / Edit Windows Menu 4 52
Child Form in front 4 39
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now