November 8th, 2012, 09:59 AM
Naming Function in a Export using VBA
I have created my first tool using macros and then had to delve into a little VBA for a file naming function in an export. Sooo... here is my code thusfar....
On Error GoTo Mcr_90104_Data_Err
DoCmd.OpenQuery "01 Create 90104 Data", acViewNormal, acEdit
DoCmd.OutputTo acOutputTable, "90104 Data", "ExcelWorkbook(*.xlsx)", "\\CAE\Customer Records\Customer Reporting\90104 Requests\Requests\New 90104 Data " & Format(Date, "mm-dd-yyyy") & ".xlsx", False, "", , acExportQualityPrint
As a part of the naming function I need to reference a query and retrieve a name that is manually entered during the process. For example, this is a report that is run all the time and I have simple automated it. User opens & runs a Macro and is prompted to enter 3 pieces of info. 1. Station Name (ex. CLT) 2. Starting Date 3. Ending Date. The Station name is entered there and stored in the query. I need that station name, CLT, to appear in the file name when exported. Now it exports as "New 90104 Data 11/08/12" I need it to export as "New 90104 CLT Data 11/08/12". So the date and the Station (CLT) will be the unique parts of the name. Any ideas?
Thanks in advance