Solved How to export Excel file to wanted directory? (1 Viewer)

sharonng

New member
Local time
Tomorrow, 01:17
Joined
Jul 7, 2021
Messages
11
My supervisor asked me that whether an access table can be exported to their wanted directory. Currently, I am using
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
what you need is a FileDialog (you can google it).
and you need to add reference to
Microsoft Office XX.X Object Library.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:17
Joined
Oct 29, 2018
Messages
21,358
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
see this demo.
 

Attachments

  • testFileDialog.accdb
    524 KB · Views: 485

sharonng

New member
Local time
Tomorrow, 01:17
Joined
Jul 7, 2021
Messages
11
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
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

New member
Local time
Tomorrow, 01:17
Joined
Jul 7, 2021
Messages
11
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

Registered User.
Local time
Tomorrow, 04:17
Joined
Jul 4, 2013
Messages
2,770
What do you see when you click on 'Tools'?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
2021-07-08_15-20-00.png
2021-07-08_15-22-55.png
 

sharonng

New member
Local time
Tomorrow, 01:17
Joined
Jul 7, 2021
Messages
11
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
press Alt-F11 to go to VBA. you will find it there.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
press Alt-Key + F11-Key (together) to go to VBA.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:17
Joined
May 7, 2009
Messages
19,169
It is the 1st item, under Tool menu. Try it.
 

sharonng

New member
Local time
Tomorrow, 01:17
Joined
Jul 7, 2021
Messages
11
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!
 

Users who are viewing this thread

Top Bottom