How to write VBA for monthly exporting of queries?

[BigMike]

I can export reports to excel with no problem. However, every time before I export the monthly reports, I have to go through all my filters in my queries and change them to the previous month so all of the data filters accordingly and then export the queries again.

How can I get my code to automatically filter my data so I can export the queries for the previous month?

 

[MSAccessRookie]

I can export reports to excel with no problem. However, every time before I export the monthly reports, I have to go through all my filters in my queries and change them to the previous month so all of the data filters accordingly and then export the queries again.

How can I get my code to automatically filter my data so I can export the queries for the previous month?

The Function Datepart() could be the way to go, if you choose the Month Part as the part to get. For instance:

The Function Now() returns "6/04/2013"
The Function Datepart("m", Now()) returns "6" (For June)
The Function Datepart("m", Now())-1 returns "5" (For May)

Is this the direction that you are headed?

-- Rookie

 

[BigMike]

Maybe. I have it set up under a command button, so all I do is click the button and all of the queries are exported to excel sheets. I want to set up my command button code to automatically filter my queries for the previous month as it exports instead of me going back into each query, filtering it, and then exporting it. I hope this helps clear up what I am asking.

Also, I am very new to VBA, so as much explaination as possible would be greatly appreciated! Here is my code:

Private Sub Command11_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Block Count (Filtered)", "C:\FileName", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Block Sendbacks (Filtered)", "C:\FileName", True, "Sheet2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Proposal Count (Filtered)", "C:\FileName", True, "Sheet3"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Proposal Sendbacks (Filtered)", "C:\FileName", True, "Sheet4"
End Sub

how exactly would I incorporate the function datepart?

 

[BigMike]

Also since these are monthly reports, the data being exported from access will need to overwrite the old data that is already in the existing excell spreadsheet cells. How would I go about doing this?

Thanks for your help.

 

[MSAccessRookie]

Maybe. I have it set up under a command button, so all I do is click the button and all of the queries are exported to excel sheets. I want to set up my command button code to automatically filter my queries for the previous month as it exports instead of me going back into each query, filtering it, and then exporting it. I hope this helps clear up what I am asking.

Also, I am very new to VBA, so as much explaination as possible would be greatly appreciated! Here is my code:

Private Sub Command11_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Block Count (Filtered)", "C:\FileName", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Block Sendbacks (Filtered)", "C:\FileName", True, "Sheet2"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Proposal Count (Filtered)", "C:\FileName", True, "Sheet3"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Proposal Sendbacks (Filtered)", "C:\FileName", True, "Sheet4"
End Sub

how exactly would I incorporate the function datepart?

I assume that "Block Count (Filtered)" and the others are Queries, so you would need to modify each of them in the appropriate manner. Without viewing the contents of the Queries, it is difficult to give you a more complete answer.

 

[BigMike]

My queries all basically work the same way. They give me all the data that is in the database for every month that has ever been entered, so I filter them for the previous month. They are set up like so: this isn't code, it is the best way to display the layout)

[U]Employee[/U]    [U]Month of created product[/U]    [U]Products Submitted[/U]  [U]Count[/U]
Name            May 2013                    True/False       # of True

Every month I export my queries, I have to go into each individual query and filter it for the previous month and I would like my code to do this for me. I hope this helps.

 

[MSAccessRookie]

In order to better answer your question, we need to see a Screen shot of the Query Design View for the Query, or the SQL Code from the SQL View of the Query

 

[BigMike]

Okay, sorry. It is attached in a zip file. Thanks again for your help!

 

[MSAccessRookie]

Okay, sorry. It is attached in a zip file. Thanks again for your help!

Work does not allow downloading of Zipped Data Files. Perhaps someone else can assist, unless you have another way.

 

[BigMike]

I'll find another way to figure it out. Thank you for giving it an attempt though and I'm sorry I couldn't make it easier for you to view.

 

[MSAccessRookie]

Keep your eyes open for other responses. Someone else might jump in at any time