Solved Export to Excel as per the Form Filtered Data (1 Viewer)

[ahmad_rmh]

I want to export data to excel as per the form view filtered data but it is exporting all data available in the query. On the other hand, I want to export in the same workbook with new worksheets. The screenshots are attached herewith.

Kindly suggest.

 

[theDBguy]

Check out the CopyFromRecordset method.

Range.CopyFromRecordset method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[ahmad_rmh]

Check out the CopyFromRecordset method.

Range.CopyFromRecordset method (Excel)

Office VBA reference topic

docs.microsoft.com

Thanks @theDBguy, I want to make criteria in query as per the filtered data in the form

 

[theDBguy]

Thanks @theDBguy, I want to make criteria in query as per the filtered data in the form

Yes, that shouldn't be a problem. Give it a shot and show us what you've tried.

 

[ahmad_rmh]

Yes, that shouldn't be a problem. Give it a shot and show us what you've tried.

I have made export through the following code.

DoCmd.OutputTo acOutputQuery, "qryListExtended", acFormatXLSX, , True

It exports the query data to excel but I want to export data as per the form filtered data.

I have also applied expression in query as : [ Forms]![frmListExtended]![CboEntity]
But it is not giving me the output what i have required.

 

[theDBguy]

I have made export through the following code.

DoCmd.OutputTo acOutputQuery, "qryListExtended", acFormatXLSX, , True

It exports the query data to excel but I want to export data as per the form filtered data.

I have also applied expression in query as : [ Forms]![frmListExtended]![CboEntity]
But it is not giving me the output what i have required.

Did you try using the CopyFromRecordset method?

 

[ahmad_rmh]

I have solved but I want it in simplest way. Is it any simplest way then let me know.

I have applied criteria in query as per the combo box selection in the form. The combo box has been made as a value list. The code is as under

SELECT TranTypePK, TranType, ItemsListName, PackingSize, Sum(Stock_In_Out) AS SumOfStock_In_Out, EntityDetail
FROM qryTransactionsExtended
WHERE (((TranDate) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY TranTypePK, TranType, ItemsListName, PackingSize, EntityDetail, EntityName
HAVING ((([Forms]![frmListExtended]![CboEntity])=0))
OR (((TranTypePK)<=2) AND (([Forms]![frmListExtended]![CboEntity])=1))
OR (((TranTypePK)>=3 And (TranTypePK)<=5) AND (([Forms]![frmListExtended]![CboEntity])=2))
OR (((TranTypePK)=6) AND (([Forms]![frmListExtended]![CboEntity])=3))
OR (((EntityName)="Ameer Sultan Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=4))
OR (((EntityName)="Palestine Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=5))
OR (((EntityName)="Makkah Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=6))
OR (((EntityName)="Obhur Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=7))
OR (((EntityName)="Corniche Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=8))
OR (((EntityName)="Kani & Mani - Ameer Sultan WH") AND (([Forms]![frmListExtended]![CboEntity])=9))
OR (((EntityName)="Kani & Mani - Obhur WH") AND (([Forms]![frmListExtended]![CboEntity])=10))
OR (((EntityName)="Fried Onion Production WH") AND (([Forms]![frmListExtended]![CboEntity])=11))
OR (((EntityName)="Sweets Production WH") AND (([Forms]![frmListExtended]![CboEntity])=12))
OR (((EntityName)="Head Office") AND (([Forms]![frmListExtended]![CboEntity])=13))
OR (((EntityName)="Main Warehouse (Usage)") AND (([Forms]![frmListExtended]![CboEntity])=14))
OR (((EntityName)="Transfer to Egypt Branch") AND (([Forms]![frmListExtended]![CboEntity])=15))
OR (((EntityName)="Expired Inventory WH") AND (([Forms]![frmListExtended]![CboEntity])=16));

 

[theDBguy]

I have solved but I want it in simplest way. Is it any simplest way then let me know.

I have applied criteria in query as per the combo box selection in the form. The combo box has been made as a value list. The code is as under

SELECT TranTypePK, TranType, ItemsListName, PackingSize, Sum(Stock_In_Out) AS SumOfStock_In_Out, EntityDetail
FROM qryTransactionsExtended
WHERE (((TranDate) Between DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY TranTypePK, TranType, ItemsListName, PackingSize, EntityDetail, EntityName
HAVING ((([Forms]![frmListExtended]![CboEntity])=0))
OR (((TranTypePK)<=2) AND (([Forms]![frmListExtended]![CboEntity])=1))
OR (((TranTypePK)>=3 And (TranTypePK)<=5) AND (([Forms]![frmListExtended]![CboEntity])=2))
OR (((TranTypePK)=6) AND (([Forms]![frmListExtended]![CboEntity])=3))
OR (((EntityName)="Ameer Sultan Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=4))
OR (((EntityName)="Palestine Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=5))
OR (((EntityName)="Makkah Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=6))
OR (((EntityName)="Obhur Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=7))
OR (((EntityName)="Corniche Branch WH") AND (([Forms]![frmListExtended]![CboEntity])=8))
OR (((EntityName)="Kani & Mani - Ameer Sultan WH") AND (([Forms]![frmListExtended]![CboEntity])=9))
OR (((EntityName)="Kani & Mani - Obhur WH") AND (([Forms]![frmListExtended]![CboEntity])=10))
OR (((EntityName)="Fried Onion Production WH") AND (([Forms]![frmListExtended]![CboEntity])=11))
OR (((EntityName)="Sweets Production WH") AND (([Forms]![frmListExtended]![CboEntity])=12))
OR (((EntityName)="Head Office") AND (([Forms]![frmListExtended]![CboEntity])=13))
OR (((EntityName)="Main Warehouse (Usage)") AND (([Forms]![frmListExtended]![CboEntity])=14))
OR (((EntityName)="Transfer to Egypt Branch") AND (([Forms]![frmListExtended]![CboEntity])=15))
OR (((EntityName)="Expired Inventory WH") AND (([Forms]![frmListExtended]![CboEntity])=16));

Hi. Glad to hear you got it to work. Not sure if it's simpler, but I would just use the CopyFromRecordset method, if I have to do it.

 

[ahmad_rmh]

Hi. Glad to hear you got it to work. Not sure if it's simpler, but I would just use the CopyFromRecordset method, if I have to do it.

Thanks @theDBguy , As I am beginner and learner so I don't have any idea how to work through recordset. Anyhow, I am studying how to work through recordset as i will understand how to perform tasks through recordset then i will try again through recordset.

 

[theDBguy]

Thanks @theDBguy , As I am beginner and learner so I don't have any idea how to work through recordset. Anyhow, I am studying how to work through recordset as i will understand how to perform tasks through recordset then i will try again through recordset.

Of course, you'll have to walk before you can run. I was the same when I first started out using VBA. Good luck with your journey!

 

[ahmad_rmh]

Of course, you'll have to walk before you can run. I was the same when I first started out using VBA. Good luck with your journey!

Thanks,