Karl001
asked on
Import date from Access to Excel
Hi,
I would like to export data from an Access filter form to excel.
I want to use "RecordSource" instruction.
Here my code:
Set ExportQuery = CurrentDb.CreateQueryDef(" ExportQuer y", Me.RecordSource & " WHERE " & Me.Filter)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "c:\Report\test.xlsx", False
Any idea why that doesn't work?
Thanks
I would like to export data from an Access filter form to excel.
I want to use "RecordSource" instruction.
Here my code:
Set ExportQuery = CurrentDb.CreateQueryDef("
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "c:\Report\test.xlsx", False
Any idea why that doesn't work?
Thanks
ASKER
SELECT tb_Client1_Group.idGroupCl ient1, tb_Client1_Group.Group, tb_Client1_Group.no, IIf([tb_Client1_Group].[id GroupClien t1]=1,Null ,[tb_Clien t1_Group]. [Group] & "-" & [tb_Client1_Group].[no]) AS instTrans, tb_Client1_Group.instNom, [tb_Client1_Group].[instCo dePostal] & ", " & [tb_Client1_Group].[instAd resse1] & ", " & [tb_Client1_Group].[instVi lle] & ", " & [tb_Client1_Group].[instAd resse2] AS instAdresse, tb_Client1_Group.instAdres se1, tb_Client1_Group.instAdres se2, tb_Client1_Group.instVille , tb_Client1_Group.instCodeP ostal, tb_Client1_Group.instTelep hone, tb_Client1_Group.idREGION, tb_Client1_REGION.REGIONNo , IIf([tb_Client1_REGION].[R EGIONNo]=1 ,Null,[tb_ Client1_RE GION].[REG IONNo]) AS Region, tb_Client1_VPR.idVPR, tb_Client1_VPR.VPRNo, IIf([tb_Client1_VPR].[VPRN o]=1,Null, [tb_Client 1_VPR].[
VPRNo]) AS VPR, tb_Client1_Group.instInact if, IIf([tb_Client1_Group].[in stInactif] =True,"Ina ctive",Nul l) AS statut, IIf(IsNull([Inst]),"Oui"," Non") AS etabCamp, rq_InstAvecInstCamp_02.Max DeInstCamp AS InstCamp, rq_InstSansEtablissement.I nst, IIf([tb_Client1_Group_1].[ idGroupCli ent1]=1,"" ,[tb_Clien t1_Group_1 ].[Group] & "-" & [tb_Client1_Group_1].[no]) AS fusion, tb_Client1_Group.idGroupFu sion 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.idGroupCl ient1 = rq_InstSansEtablissement.i dGroupClie nt1) ON tb_Client1_REGION.idREGION = tb_Client1_Group.idREGION) ON tb_Client1_Group_1.idGroup Client1 = tb_Client1_Group.idGroupFu sion) INNER JOIN tb_Client1_VPR ON tb_Client1_REGION.idVPR = tb_
Client1_VPR.idVPR) LEFT JOIN rq_InstAvecInstCamp_02 ON tb_Client1_Group.idGroupCl ient1 = rq_InstAvecInstCamp_02.idI nstC1 WHERE (((tb_Client1_Group.idGrou pClient1)< >1)) ORDER BY tb_Client1_Group.Group, tb_Client1_Group.no;
VPRNo]) AS VPR, tb_Client1_Group.instInact
Client1_VPR.idVPR) LEFT JOIN rq_InstAvecInstCamp_02 ON tb_Client1_Group.idGroupCl
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How can export the data on the form when I use a filter on it?
Thanks
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.
if you don't want to create a make table query,
you will need more VBA codes to accomplish this.
ASKER
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(" ExportQuer y", Replace(Me.RecordSource, ";", "") & " AND " & Me.Filter & ";")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "c:\AccessTest\test.xlsx", False
Result:
WHERE (((tb_Client1_Group.idGrou pClient1)< >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("r ptRRDataAL L")
Set objXL = CreateObject("Excel.Applic ation")
Set objWB = objXL.Workbooks.Add
objWB.Sheets(1).Range("A1" ).CopyFrom Recordset rstFull
objXL.Visible = True
rstFull.Close
Set rstFull = Nothing
I changed the code
Set rstFull = CurrentDb.OpenRecordset("r ptRRDataAL L")
To
Set rstFull = me.recordset
I got error message: Error 13 Type mismatch
Which direction should I take?
FIRST
I modified the code, to consider the "Where" in the recordsource. ref. Rey Obrero comment.
Here the new code:
Set ExportQuery = CurrentDb.CreateQueryDef("
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExportQuery", "c:\AccessTest\test.xlsx",
Result:
WHERE (((tb_Client1_Group.idGrou
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("r
Set objXL = CreateObject("Excel.Applic
Set objWB = objXL.Workbooks.Add
objWB.Sheets(1).Range("A1"
objXL.Visible = True
rstFull.Close
Set rstFull = Nothing
I changed the code
Set rstFull = CurrentDb.OpenRecordset("r
To
Set rstFull = me.recordset
I got error message: Error 13 Type mismatch
Which direction should I take?
ASKER
I found a solution.
I will complete my code and after poste it.
I will complete my code and after poste it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
my solution is more complete
Me.RecordSource
debug.print Me.RecordSource
copy and paste here