Populating Excel in customized order

[davideitaly]

Hi all. I have a complicated issue (as always).


I created a JOIN query listing a number of maritime cadets (name and surname), the department they belong to (Coperta = deck dept; Macchina = engine dept.), the number of embarkation experiences (numbered 1, 2, etc.), the name of the companies they worked for, the period (date Dal (from)... Al (to)...), the resulting number of days (TOT: Al-Dal), and the progressive sum (progSum TOT). Who calls the query must specify the department name, number and tag (called "Etichetta" in Italian), and results are ordered for Surname then for number of embarkation experiences. Here's the query structure (query name: imbarchisqlquery):



Calling the query for dept COP 35 NA (NA means "no tag"), the result is like that (names and surnames omitted for privacy):



Now I have a dedicated form for this query:



Right of the title I put a button called "Estrai in Excel". The goal would be that on click Access will create an excel file populated with the selected data (COP 35 NA), not as simple copy-paste, but distributing them in a different way: every excel row should show dept / name / surname / experience n. / company / From / To / TOT, repeating the red part of data until is NZ (if cadet had 3 different embarkation experiences, they will be inserted subsequently in the same row) and TOTALE as final datum (overall on-board days). This is how the resulting Excel file should look like:



I am trying to manage the populating process through this code I found on the web, modified for my needs:

Option Compare Database
Option Explicit

Private Sub EstraiExcel_Click()

Dim db As DAO.Database
Dim rs As DAO.recordset
Dim table_name As String
Dim Record As String, strSQL As String
table_name = "Estrazione"
Dim excel_application As Excel.Application
Dim workbook As Excel.workbook
Dim sheet As Excel.Worksheet
Dim excel_file_name As String
Dim sheet_name As String
Dim Corso As String
Dim Ed As Integer
Dim ETICHETTA As String
Dim NOME As String
Dim COGNOME As String
Dim NUM As Integer
Dim Armatore As String
Dim DAL As Date
Dim AL As Date
Dim TOT As Integer
Dim TOTALE As Integer


excel_file_name = "E:\Documenti\Access\db\Estrazione_imbarchi_per_corso.xlsx"


Set db = CurrentDb

strSQL = "FROM (Imbarchi_anagrafica INNER JOIN Imbarchi_stage ON Imbarchi_anagrafica.ID_imbarchi_anagrafica = Imbarchi_stage.ID_imbarchi_anagrafica) INNER JOIN Imbarchi_corso ON Imbarchi_anagrafica.ID_imbarchi_anagrafica = Imbarchi_corso.ID_imbarchi_anagrafica" & _
"WHERE (((Imbarchi_corso.Corso) = [Digita il corso]) And ((Imbarchi_corso.Ed) = [Digita l'edizione]) And ((Imbarchi_corso.ETICHETTA) = [Digita l'etichetta]))" & _
"ORDER BY Imbarchi_anagrafica.Cognome, Imbarchi_stage.N;"

Set rs = db.OpenRecordset("select * from imbarchi_estrpercorso WHERE Imbarchi_anagrafica_ID_imbarchi_anagrafica=" & Me.Imbarchi_anagrafica_ID_imbarchi_anagrafica & "Imbarchi_corso.Corso=" & Me!Corso & "Imbarchi_corso.Ed=" & Me!Ed & "Imbarchi_corso.ETICHETTA =" & Me!ETICHETTA)

Set excel_application = New Excel.Application
Set workbook = excel_application.Workbooks.Add
Set sheet = workbook.Sheets.Add

Dim rowIndex As Integer

sheet.Cells(1, 1) = "CORSO"
sheet.Cells(1, 1).Font.Bold = True
sheet.Cells(1, 2) = "EDIZIONE"
sheet.Cells(1, 2).Font.Bold = True
sheet.Cells(1, 3) = "ETICHETTA"
sheet.Cells(1, 3).Font.Bold = True
sheet.Cells(1, 4) = "NOME"
sheet.Cells(1, 4).Font.Bold = True
sheet.Cells(1, 5) = "COGNOME"
sheet.Cells(1, 5).Font.Bold = True
sheet.Cells(1, 6) = "N."
sheet.Cells(1, 6).Font.Bold = True
sheet.Cells(1, 7) = "ARMATORE"
sheet.Cells(1, 7).Font.Bold = True
sheet.Cells(1, 8) = "DAL"
sheet.Cells(1, 8).Font.Bold = True
sheet.Cells(1, 9) = "AL"
sheet.Cells(1, 9).Font.Bold = True
sheet.Cells(1, 10) = "TOT"
sheet.Cells(1, 10).Font.Bold = True
sheet.Cells(1, 11) = "N."
sheet.Cells(1, 11).Font.Bold = True
sheet.Cells(1, 12) = "ARMATORE"
sheet.Cells(1, 12).Font.Bold = True
sheet.Cells(1, 13) = "DAL"
sheet.Cells(1, 13).Font.Bold = True
sheet.Cells(1, 14) = "AL"
sheet.Cells(1, 14).Font.Bold = True
sheet.Cells(1, 15) = "TOT"
sheet.Cells(1, 15).Font.Bold = True
sheet.Cells(1, 16) = "N."
sheet.Cells(1, 16).Font.Bold = True
sheet.Cells(1, 17) = "ARMATORE"
sheet.Cells(1, 17).Font.Bold = True
sheet.Cells(1, 18) = "DAL"
sheet.Cells(1, 18).Font.Bold = True
sheet.Cells(1, 19) = "AL"
sheet.Cells(1, 19).Font.Bold = True
sheet.Cells(1, 20) = "TOT"
sheet.Cells(1, 20).Font.Bold = True
sheet.Cells(1, 21) = "N."
sheet.Cells(1, 21).Font.Bold = True
sheet.Cells(1, 22) = "ARMATORE"
sheet.Cells(1, 22).Font.Bold = True
sheet.Cells(1, 23) = "DAL"
sheet.Cells(1, 23).Font.Bold = True
sheet.Cells(1, 24) = "AL"
sheet.Cells(1, 24).Font.Bold = True
sheet.Cells(1, 25) = "TOT"
sheet.Cells(1, 25).Font.Bold = True
sheet.Cells(1, 26) = "N."
sheet.Cells(1, 26).Font.Bold = True
sheet.Cells(1, 27) = "ARMATORE"
sheet.Cells(1, 27).Font.Bold = True
sheet.Cells(1, 28) = "DAL"
sheet.Cells(1, 28).Font.Bold = True
sheet.Cells(1, 29) = "AL"
sheet.Cells(1, 29).Font.Bold = True
sheet.Cells(1, 30) = "TOT"
sheet.Cells(1, 30).Font.Bold = True
sheet.Cells(1, 31) = "N."
sheet.Cells(1, 31).Font.Bold = True
sheet.Cells(1, 32) = "ARMATORE"
sheet.Cells(1, 32).Font.Bold = True
sheet.Cells(1, 33) = "DAL"
sheet.Cells(1, 33).Font.Bold = True
sheet.Cells(1, 34) = "AL"
sheet.Cells(1, 34).Font.Bold = True
sheet.Cells(1, 35) = "TOT"
sheet.Cells(1, 35).Font.Bold = True
sheet.Cells(1, 36) = "N."
sheet.Cells(1, 36).Font.Bold = True
sheet.Cells(1, 37) = "ARMATORE"
sheet.Cells(1, 37).Font.Bold = True
sheet.Cells(1, 38) = "DAL"
sheet.Cells(1, 38).Font.Bold = True
sheet.Cells(1, 39) = "AL"
sheet.Cells(1, 39).Font.Bold = True
sheet.Cells(1, 40) = "TOT"
sheet.Cells(1, 40).Font.Bold = True
sheet.Cells(1, 41) = "TOTALE"
sheet.Cells(1, 41).Font.Bold = True

    For rowIndex = 1 To rs.RecordCount

    sheet.Cells(rowIndex + 1, 1) = Me.Corso
    sheet.Cells(rowIndex + 1, 2) = Me.Ed
    sheet.Cells(rowIndex + 1, 3) = Me.ETICHETTA
    sheet.Cells(rowIndex + 1, 4) = Me.NOME
    sheet.Cells(rowIndex + 1, 5) = Me.COGNOME

    rs.MoveNext
Next
rs.Close

workbook.SaveAs excel_file_name
workbook.Close
excel_application.Quit

Set sheet = Nothing
Set workbook = Nothing
Set excel_application = Nothing
Set rs = Nothing
Set db = Nothing
End Sub

PROBLEM: once I click the button, I have a debug error on set rs saying "Runtime error n.3075 - Syntax error (missing operator) in the query expression

'Imbarchi_anagrafica_ID_imbarchi_anagrafica=25Imba rchi_corso.Corso=COPImbarchi_corso.Ed=35Imbarchi_E TICHETTA=NA'.

So it seems it recognizes the data (ID 25, Corso COP, Ed 35, ETICHETTA NA), but some syntax error blocks everything. I can't got what...

Any suggestion?

Thanks.

[CJ_London]

looks like you are missing AND's or OR's

'Imbarchi_anagrafica_ID_imbarchi_anagrafica=25Imba AND rchi_corso.Corso=COP AND Imbarchi_corso.Ed=35Imbarchi_E AND TICHETTA=NA'.

and NA will be text so this

TICHETTA=NA'

should be

TICHETTA='NA'

update: just looked at your strSQL and you are missing spaces before WHERE and ORDER BY

[davideitaly]

looks like you are missing AND's or OR's

'Imbarchi_anagrafica_ID_imbarchi_anagrafica=25Imba AND rchi_corso.Corso=COP AND Imbarchi_corso.Ed=35Imbarchi_E AND TICHETTA=NA'.

and NA will be text so this

TICHETTA=NA'

should be

TICHETTA='NA'

Thanks Ajax. Actually I tried to put AND where due, like that:

Set rs = db.OpenRecordset("select * from imbarchi_estrpercorso WHERE Imbarchi_anagrafica_ID_imbarchi_anagrafica=" & Me.Imbarchi_anagrafica_ID_imbarchi_anagrafica & " AND Imbarchi_corso.Corso=" & Me!Corso & " AND Imbarchi_corso.Ed=" & Me!Ed & " AND Imbarchi_corso.ETICHETTA =" & Me!ETICHETTA)

In that case the debug error message says: "Runtime error '3061' - Insufficient parameters. Foreseen 6".

And I don't know what it means...

PS: I can't put "ETICHETTA=NA" because NA is not always the date. Depending on the department it could be also A, or B, or C...

[CJ_London]

PS: I can't put "ETICHETTA=NA" because NA is not always the date. Depending on the department it could be also A, or B, or C...

OK, but it is text so you need to include single quotes

Imbarchi_corso.ETICHETTA ='" & Me!ETICHETTA & "'"

same goes for any other text fields

[davideitaly]

OK, but it is text so you need to include single quotes

Imbarchi_corso.ETICHETTA ='" & Me!ETICHETTA & "'"

same goes for any other text fields

Ok, did it, but nothing changed.
If I put them, debug says the same "syntax error".
If I don't, debug says 6 parameters are missing...

[CJ_London]

try setting your code to a sql string before executing it and debug.print it to see what you get, then copy and paste to the sql window of a new query to see what errors you get.

Not sure if your code is losing something when you paste it to the thread but this

("select * from imbarchi_estrpercorso WHERE Imbarchi_anagrafica_ID_imbarchi_anagrafica="

looks like you are looking for a field called Imbarchi_anagrafica_ID_imbarchi_anagrafica in a table called imbarchi_estrpercorso