Solved

Query or code problem ? (exporting recordset to excel)

Posted on 2003-12-08
15
820 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rouxjean
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Once again, that worked great :-)
Many thanks for your help !!!
0
 
LVL 8

Expert Comment

by:ampapa
Comment Utility
Your very welcome....
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

16 Experts available now in Live!

Get 1:1 Help Now