Change file name in "TransferSpreadsheet" macro ac

[dmagnus]

I created an Access macro to simply export a table to excel. Each month I want to export the same exact table to excel, except the folder needs to change (based on the current month's name).

For example: when the file is transferred to excel, the folder location is say N\Users\Reports\May Reports and the name of the file is "report_data". The name of the report can stay the same each month, but in June I want it to be transferred to the N\Users\Reports\June Reports folder.

Is there a way to create a prompt in which I enter the month name/abbreviatino and it puts that name into the folder's address automatically? Does that make sense?

Thanks in advance for any help.

 

[VicRauch]

I don't know of any way to do what you are asking within a macro, unless you do a lot of condition statements checking to see what month it is, and then using that month in the prewritten (by you) TransferSpreadsheet action.
The best way to do this would be to do it in VBA.
HTH

 

[Norie]

Dan

Why not convert the macro to 'proper' VBA?

 

[dmagnus]

I'm realtively new to VBA, and have only used it in Excel. I guess I could look into it or get some help from someone in the office. It sounds like that would be the best solution.

Thanks for your replies.