Exporting to excel using TransferSpreadsheet

[gareth001]

Hi,

I'm running a macro to transfer out of access into excel spreadsheets. I want to use this macro more than once but each time to make a spreadsheet with a new name. What I don't want to have to do is either change the macro's File Name each time or rename the file once created.

Is there something I can add to the File Name of the TransferSpreadsheet action that will prompt the user to specify the file name to be used?

Thanks for your help in advance

Gareth

 

[DoubleD]

Give them a text box on a form to fill in. Then check whether they've entered a file name before you allow the export to happen.

Or have a dialog box pop-up to ask for a file name before each export.

I am what I am based on the decisions I have made.

DoubleD

 

[gareth001]

Thanks DoubleD, a dialog box is exactly what I was thinking of using, but how to link it to the File Name of the TransferSpreadsheet action without using VBA would be great.

Thanks

Gareth

 

[DoubleD]

I'm not sure how you would do it without VBA. With VBA it's a snap.

I am what I am based on the decisions I have made.

DoubleD

 

[gareth001]

Sorry DoubleD, I guess I should have made it clearer, the database is for a non VBA user and it needs to be easy enough for them to change when and if need be.

Thanks

 

[AppStaff]

I believe DoubleD's initial suggestion meets your need.

1. Make a form and on the form put a button.
2. Attach the macro to the buttons on click event.
3. Place an unbound control on the form for entering the path and file name.
4. In the macro for file name reference the unbound control on your form.. =Forms!MyForm!MyControl


You'll need to add your own specifics to 4. and verify my syntax but that should send you in the right direction.

if your sending the file to the same directory but with a different name based on user input you could use something like this for 4.

= "MyPath" & Forms!MyForm!MyControl

MyPath is the Directory structure the file is going to each time.

HTH

 

[DoubleD]

Feeding of AppStaff's suggestion:
You could have a second unbound control in the form. This would contain the directory path. Set the default value to your default directory path. It should be easy to teach the person how to change the default value. Then your reference in your macro would look like this:

= Forms!MyForm!MyPathControl & Forms!MyForm!MyFileControl


I am what I am based on the decisions I have made.

DoubleD

 

[gareth001]

Thanks, this helps alot.

 

[jimmythegeek]

It would be better to let them browse to where they want to save it rather than make them type the path in, but none-the-less, this should eliminate the need for a macro.

Don't attach the macro to the button. On the on_click event of the button, put the following line (change the red accordingly):

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableOrQueryToExport", me.textBoxName

Just know that if they type an incorrect path in, you will get an error.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?