Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

Query or code problem ? (exporting recordset to excel)

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
rouxjean
Asked:
rouxjean
  • 8
  • 6
1 Solution
 
ampapaCommented:
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
 
appariCommented:
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
 
rouxjeanAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ampapaCommented:
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
 
ampapaCommented:
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
 
ampapaCommented:
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
 
rouxjeanAuthor Commented:
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
 
rouxjeanAuthor Commented:
What is strange though is if my recordset is only the content of a table, it'll work...
0
 
ampapaCommented:
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
 
rouxjeanAuthor Commented:
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
 
ampapaCommented:
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
 
rouxjeanAuthor Commented:
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
 
ampapaCommented:
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
 
rouxjeanAuthor Commented:
Once again, that worked great :-)
Many thanks for your help !!!
0
 
ampapaCommented:
Your very welcome....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now