Set up Criteria in Macro

[accessfever]

I have a big table to hold 12 locations' sales data. There are 4 different products in the table. Now, I need to transfer the table to an excel file by plant by product type for monthly review. In order words, I will have 48 excel files in total after the data transfers. What is the most time saving way to do the transfer?

Any idea?

 

[vbaInet]

Macro wise, I don't think it's going to be easy.

Code-wise:

1. Create a query grouped Locations then Products, sorted by Locations followed by Products. Let's call this query qryExport
2. Create a query that includes Locations and Products ONLY. Sort by Products followed by Locations. Let's call this one qryDistinctProds
3. Create a function that uses a recordset to iterate through each record in qryDistinctProds.
4. For each pair of Location and Product you would alter the WHERE clause of the query definition of qryExport to be the Location and Product of the recordset
5. For that altered definition, you use the Docmd.TransferSpreadsheet method to export the query to Excel with a unique file name in a specific folder.

 

[accessfever]

I know very little about the code but will give it a try. I understand your step 1 & step 2 but not sure how the code should be for step 3. Would you give me an example so I can use it?

Thanks for your help.

 

[vbaInet]

Here's some air code:

Public Function ExportMyProds(locations As [COLOR=Red][B]String[/B][/COLOR], products As [COLOR=Red][B]String[/B][/COLOR]) As String
    Dim db As DAO.Database, strSQL As String, strWHERE As String
    
    ' Set and Open Recordset here. The recordset will use qryDistinctProds
    
    Set db = CurrentDb
    ' strSQL will be the sql string (i.e. in SQL view) from qryExport but without the WHERE clause.
    ' Notice the space after table1
    strSQL = "SELECT [Field1], [Field2] FROM table1 "
    
    ' Loop through Recordset here
    
        strWHERE = "WHERE [Field1] = [B][COLOR=Red]'[/COLOR][/B]" & param1 & "[COLOR=Red][B]'[/B][/COLOR] AND [Field2] = [COLOR=Red][B]'[/B][/COLOR]" & param2 & "[COLOR=Red][B]'[/B][/COLOR];"
        ' You can also have the ORDER BY clause as well, but remember the semi-colon at the end above
        db.QueryDefs("userfilter").SQL = strSQL & strWHERE ' & strORDERBY
        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "qryExport", "Name_Of_File", False
        ' Or use Docmd.TransferText
        
    ' Get Next value in Recordset
    
    Set qdf = Nothing
    Set db = Nothing
    
End Function

If products or locations is not a Text data type but a Number, then change the paramters to Integer and remove the quotes in red accordingly.

Here's a link on DAO recordset:

http://allenbrowne.com/ser-29.html

 

[accessfever]

Thanks for your codes! I will tackle the codes if I can make it work in my database.

 

[vbaInet]

Just let us know if you get stuck.

 

[accessfever]

I got some lucks that the lines I revised yours are green color in the vba module. But now I have a few questions:

a) I tried to execute the module but I got an message to ask me what the macro name is. How I can attach this module in an marco?
b) I think I still miss the codes to set and open recordset. Would you kindly to write me the codes? I am very brand new to access VBA
c) In your codes, it looks like the output's filename must be hardcoded in the code. Is it a way to have a dynamic filename so I don't need to go in and change the output's filename?

Below is my codes after I revised yours:
Public Function ExportMyProds(Plant As String, PSPK2 As String) As String
Dim db As DAO.Database, strSQL As String, strWHERE As String

' Set and Open Recordset here. The recordset will use qryDistinctProds (=T01_TransferMaster)

????


Set db = CurrentDb
' strSQL will be the sql string (i.e. in SQL view) from qryExport but without the WHERE clause.
' Notice the space after table1
strSQL = "SELECT Plant, Part, Desc, [Mat Tye], val_class, PSPK, PSPK2, NewMat, CurMat, [Matl Diff], [Mat Diff%], NewLbrBurd, CurLbrBurd, [Lbr Burd Diff], [Lbr Burd Diff%], NewFreight, CurFreight, [Freight Diff], [Freight Diff%], NewCostUnit, CurCostUnit, [Tot Cost Diff], [Tot Cost Diff%], MatStatus, PP1, PP1DTE, UoM, CK40N_OH, [CK40NReval$], MM03_OH, [MM03_Reval$], UsageQty, [UsageReval$], ShipQty, [ShipReval$], Note, Note2, [Profit Ctr], BU, BU2 FROM TABLE [Final COST CHG YOY] "
' Loop through Recordset here

strWHERE = "WHERE [Plant] = '" & param1 & "' AND [PSPK2] = '" & param2 & "';"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "T02Export", "C:\JE\YOY.xls", False
' Or use Docmd.TransferText

' Get Next value in Recordset

Set qdf = Nothing
Set db = Nothing

End Function

 

[vbaInet]

I gave you a link on recordsets but I don't think you've read it. Have a read and all will be clear. Search the forum as well, there are plenty of examples. The comments I put on the code regarding the recordsets is a guide as to where the relevant recordset code should go.

Have a play with that first and if you get stuck just post back.

You can concatenate the filename based on the recordset.

Once you've read that link, have a look at this thread (post #5) just to see what a recordset could look like. Post your own code consisting of the recordset and what I had already given and we will make necessary adjustments.