Import date from Access to Excel

check the string content of

Me.RecordSource

debug.print Me.RecordSource

copy and paste here

SELECT tb_Client1_Group.idGroupClient1, tb_Client1_Group.Group, tb_Client1_Group.no, IIf([tb_Client1_Group].[idGroupClient1]=1,Null,[tb_Client1_Group].[Group] & "-" & [tb_Client1_Group].[no]) AS instTrans, tb_Client1_Group.instNom, [tb_Client1_Group].[instCodePostal] & ",   " & [tb_Client1_Group].[instAdresse1] & ",   " & [tb_Client1_Group].[instVille] & ",   " & [tb_Client1_Group].[instAdresse2] AS instAdresse, tb_Client1_Group.instAdresse1, tb_Client1_Group.instAdresse2, tb_Client1_Group.instVille, tb_Client1_Group.instCodePostal, tb_Client1_Group.instTelephone, tb_Client1_Group.idREGION, tb_Client1_REGION.REGIONNo, IIf([tb_Client1_REGION].[REGIONNo]=1,Null,[tb_Client1_REGION].[REGIONNo]) AS Region, tb_Client1_VPR.idVPR, tb_Client1_VPR.VPRNo, IIf([tb_Client1_VPR].[VPRNo]=1,Null,[tb_Client1_VPR].[
VPRNo]) AS VPR, tb_Client1_Group.instInactif, IIf([tb_Client1_Group].[instInactif]=True,"Inactive",Null) AS statut, IIf(IsNull([Inst]),"Oui","Non") AS etabCamp, rq_InstAvecInstCamp_02.MaxDeInstCamp AS InstCamp, rq_InstSansEtablissement.Inst, IIf([tb_Client1_Group_1].[idGroupClient1]=1,"",[tb_Client1_Group_1].[Group] & "-" & [tb_Client1_Group_1].[no]) AS fusion, tb_Client1_Group.idGroupFusion FROM ((tb_Client1_Group AS tb_Client1_Group_1 INNER JOIN (tb_Client1_REGION INNER JOIN (tb_Client1_Group LEFT JOIN rq_InstSansEtablissement ON tb_Client1_Group.idGroupClient1 = rq_InstSansEtablissement.idGroupClient1) ON tb_Client1_REGION.idREGION = tb_Client1_Group.idREGION) ON tb_Client1_Group_1.idGroupClient1 = tb_Client1_Group.idGroupFusion) INNER JOIN tb_Client1_VPR ON tb_Client1_REGION.idVPR = tb_
Client1_VPR.idVPR) LEFT JOIN rq_InstAvecInstCamp_02 ON tb_Client1_Group.idGroupClient1 = rq_InstAvecInstCamp_02.idInstC1 WHERE (((tb_Client1_Group.idGroupClient1)<>1)) ORDER BY tb_Client1_Group.Group, tb_Client1_Group.no;

How can export the data on the form when I use a filter on it?

Thanks

One way is to create a make-table query based on your query, run it and then export the resulting table.

<How can export the data on the form when I use a filter on it?>


if you don't want to create a make table query,

you will need more VBA codes to accomplish this.

I tried 2 different ways and it didn't work.

FIRST
I modified the code, to consider the "Where" in the recordsource. ref. Rey Obrero comment.
Here the new code:
Set ExportQuery = CurrentDb.CreateQueryDef("ExportQuery", Replace(Me.RecordSource, ";", "") & " AND " & Me.Filter & ";")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "c:\AccessTest\test.xlsx", False

Result:
WHERE (((tb_Client1_Group.idGroupClient1)<>1)) AND [filter1] = 'Yes' AND [filter2] = 'nd';

filter1 and filter2 it's not recognized. Asking to enter a parameter value for those filters form.
The excel file include all records. The filter was not applied


SECOND WAY
I took a different way. I tried to use the “recorset”.
I used a code from an other question.
https://www.experts-exchange.com/questions/22528539/Export-Access-Recordset-to-Excel-with-Field-Names.html

Dim sSQL As String
Dim objXL As Object
Dim objWB As Object
Dim rstFull As Recordset
Set rstFull = CurrentDb.OpenRecordset("rptRRDataALL")
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Add
objWB.Sheets(1).Range("A1").CopyFromRecordset rstFull
objXL.Visible = True
rstFull.Close
Set rstFull = Nothing


I changed the code
Set rstFull = CurrentDb.OpenRecordset("rptRRDataALL")
To
Set rstFull = me.recordset

I got error message: Error 13 Type mismatch

Which direction should I take?

I found a solution.
I will complete my code and after poste it.

my solution is more complete