Solved How to export Excel file to wanted directory?

[sharonng]

My supervisor asked me that whether an access table can be exported to their wanted directory. Currently, I am using

outputFileName = CurrentProject.Path & "\Report002_" & tb_Year.Value & cmb_Quarter.Value & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo_RP_SAMPLE_002", outputFileName, True
Application.FollowHyperlink outputFileName
MsgBox "Exported!", vbOKOnly

to export as an Excel file. I suppose it has to do with CurrentProject.Path but I just copied and pasted from a source so I don't really understand how it works. Will someone clarify it and tell me how to export to the wanted destination?

 

[arnelgp]

what you need is a FileDialog (you can google it).
and you need to add reference to
Microsoft Office XX.X Object Library.

 

[theDBguy]

Hi. Welcome to AWF!

What do you mean by "their wanted directory?" Is it a specific directory or do they want to pick it each time?

 

[arnelgp]

see this demo.

 

[sharonng]

Hi. Welcome to AWF!

What do you mean by "their wanted directory?" Is it a specific directory or do they want to pick it each time?

Hello, I meant they pick it each time.

 

[sharonng]

see this demo.

Hello, I have tried your code and it worked. However, that is not the case in my file. Compile error: User-defined type not defined appeared on the line Dim fd As Office.FileDialog. How to resolve it?

 

[arnelgp]

goto VBA (Alt-F11).
on the menu->Tools->Reference, add reference to:

Microsoft Office XX.X Object Library (XX.X is the version number you have).

 

[sharonng]

goto VBA (Alt-F11).
on the menu->Tools->Reference, add reference to:

Microsoft Office XX.X Object Library (XX.X is the version number you have).

I cannot find the Reference option. Do I need to click something beforehand?

 

[Cronk]

What do you see when you click on 'Tools'?

 

[arnelgp]

 

[sharonng]

What do you see when you click on 'Tools'?

I see Macro, Access Options, Database1 Utilities, Digital Signature. (Sorry if there is not an exact match of the wordings - I translated them from another language.)

 

[arnelgp]

press Alt-F11 to go to VBA. you will find it there.

 

[sharonng]

View attachment 92872View attachment 92874

Thank you for your help but there is not a Reference option in my Tools Menu in the VBA.

 

[arnelgp]

not a Reference option in my Tools Menu.

what Access Version do you use?

 

[sharonng]

what Access Version do you use?

Access 2016

 

[arnelgp]

press Alt-Key + F11-Key (together) to go to VBA.

 

[sharonng]

press Alt-Key + F11-Key (together) to go to VBA.

I am already in the VBA but the reference option is just not in the Tools section.

 

[arnelgp]

It is the 1st item, under Tool menu. Try it.

 

[sharonng]

A

It is the 1st item, under Tool menu. Try it

Ahhh, I finally found it. It has a different name other than the one in Microsoft's documentation, that's why I couldn't find it. Thanks a lot!