create a macro to export to an excel file using the current date as a name

[Lostsamoan]

pretty much the title says it all. i want to create a macro or VB that would export to an excel file and name the file the current date. if anyone can point me in the right direction i would greatly appreciate it


Mahalo Nui Loa

 

[pbaldy]

One way:

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\Whatever\FileName" & Format(Date(), "yyyymmdd") & ".xls"

 

[Lostsamoan]

thanks i am gonna try it and let you know

 

[pbaldy]

No problem; post back if you get stuck.

 

[Lostsamoan]

works perfectly, Thank you

 

[pbaldy]

Excellent! Happy to help.

 

[pcrody]

I have a macro in Access that exports a table created. I am using a module to add the date on the filename. The macro runs, but the module does not run. It just opens up in VB, and the macro finishes. I can then run the module and it will change the filename and save it, but it will not run automatically.

What am I doing wrong?

 

[pbaldy]

What am I doing wrong?

Hard to say without seeing what you're doing.

What are the macro actions?

 

[pcrody]

Sales Macro:
ImportExportSpreadsheet (Import, Excel Workbook,Sales)
OpenQuery (2013 Sales Make Table Query)
OpenQuery (Sept Sales Append Table Query)
OpenVisualBasicModule (Module2, ExportToExcel)
MessageBox (Macro Complete)
StopAllMacros

Is that what you need? I've tried various suggestions to export the file with the date at the end. They all work, but they all continue on without executing the module. The module does open in VB when the macro gets to that point, but I have to click on the Run to manually run the module!

Here is one of the modules I've tried:
____________
Private Sub ExportToExcel()
Const FileNameBase As String = "c:\users\me\my documents\Sales [CurrentDate].xlsx"

Dim strFileName As String

strFileName = Replace(FileNameBase, "[CurrentDate]", Format$(Date(), "yyyymmdd"))

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Sales
Compilation Table", strFileName, True
End Sub
__________

It works fine, but wont run in the macro, only opens up, and the macro continues on.

 

[pbaldy]

I don't use macros, but you want RunCode rather than OpenVisualBasicModule (it's doing exactly that). The sub should be a function, and public, and can't have the same name as the module. The RunCode argument would be the name of the function.

 

[pcrody]

Ok, I used RunCode, function: ExportToExcel, and removed the "private" from module2 (Sub ExportToExcel()).

But when I run the macro, it says it cannot find the function "ExportToExcel".

OH, how do I made the module a function?
OK, I changed module2 to: Function ExportToExcel().... End Function.

But the macro still says cannot find "ExportToExcel".

 

[pbaldy]

Are the name of the module and the name of the function different? They have to be.

 

[pcrody]

The name of the module is "module2" the name of the function is "ExportToExcel".

 

[pbaldy]

Can you post the db here?

 

[pcrody]

Im not sure what you mean. Its a very large db. Besides being confidential.

I found an option to convert the entire macro to VB.

Now how do I run a module from the switchboard?

Ok, nvm. I got it. I would still like to know how to run a module in a macro.

But, as long as I found that convertion option, I guess that will do for now.

 

[pbaldy]

Again, you run the function, not the module. See attached.

 

[pcrody]

again and again and again... I did. It didnt work.

 

[rneaul]

One way:

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\Whatever\FileName" & Format(Date(), "yyyymmdd") & ".xls"

I attempted to use the above modified as:
Private Sub Command42_Click()
DoCmd.OutputTo acOutputQuery,"complete",acFormatXLS,"C:\Users\roland.neault\My Documents\PSA complete" & Format(Date(),yyyymmdd") & ".xls"
Exit_Command42_Click:
End Sub

But I get a syntax error... can you point out where the error is please.

 

[pbaldy]

Not sure, but on my Win 7 machine while it displays "My Documents" it appears when I click in the path window that it's actually "Documents":

C:\Users\MyName\Documents

So I guess try that.

 

[rneaul]

The format error was the () after the word Date... should just read Format(Date, "yyyymmdd")