By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,484 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,484 IT Pros & Developers. It's quick & easy.

Extract Query from Access to Excel with abillity to Select Folder and Filename

P: 7
Hi,

I am about a week into VB/VBA. I am building an Access database that multiple users will use to import a report into, and then I have a macro re-format it and save it to a file.

However, many users will be using the DB - so I was wondering how to export giving the user the ability to either select a Specific Path, a Specific File Name, or both. I have code to do extract - and to save to path, but I can't seem to get them to work together. This is my last iteration:

Expand|Select|Wrap|Line Numbers
  1. Function Extract_L2()
  2. On Error GoTo Extract_L2_Err
  3.  
  4.     DoCmd.SetWarnings False
  5.     DoCmd.DeleteObject acTable, "Level2_Extract"
  6.     DoCmd.OpenQuery "Month over Month w Formula Step 3", acViewNormal, acEdit
  7.     DoCmd.TransferText acExportDelim, "", "Level2_Extract", 
  8.  
  9. Dim SA As Object, f As Object
  10. Set SA = CreateObject("Shell.Application")
  11. Set f = SA.BrowseForFolder(0, "Choose a folder", _
  12. 16 + 32 + 64)
  13. If (Not f Is Nothing) Then
  14. PickFolder2 = f.Items.Item.Path
  15. End If
  16. Set f = Nothing
  17. Set SA = Nothing
  18.  
  19.     Beep
  20.     MsgBox "Your Level 2 File has successfully been extracted", vbInformation, "Level 2 Data"
  21.     DoCmd.SetWarnings True
  22. End Function
Nov 3 '11 #1
Share this Question
Share on Google+
13 Replies


P: 7
Is this possible, or is there another way of achieving same results?
Nov 3 '11 #2

ADezii
Expert 5K+
P: 8,679
You would build the String for the Qualified Path to the File, such as:
Expand|Select|Wrap|Line Numbers
  1. Dim SA As Object
  2. Dim f As Object
  3. Dim strFolderPath As String
  4.  
  5. Set SA = CreateObject("Shell.Application")
  6. Set f = SA.BrowseForFolder(0, "Choose a folder", _
  7.         16 + 32 + 64)
  8.  
  9. If (Not f Is Nothing) Then
  10.   strFolderPath = f.Items.Item.Path
  11.     DoCmd.TransferText acExportDelim, "", "Level2_Extract", strFolderPath & _
  12.                        "\Level2_Extract" & ".txt"
  13. End If
  14.  
  15. Set f = Nothing
  16. Set SA = Nothing
Nov 3 '11 #3

P: 7
ADezii - Thanks so much, this is great!

I updated the .txt to .csv and it is getting the results I am looking for. However, I have two questions:
1.)Is there a way to prompt the user to Save the File so they can name it?
2.)It seems to work when extracted to folders, but not desktop - is this correct?

Thanks so much!
Mike
Nov 3 '11 #4

ADezii
Expert 5K+
P: 8,679
Is there a way to prompt the user to Save the File so they can name it?
Yes, you are now looking at the traditional Save As Dialog Box where the User selects a Folder, then enters a File Name. This is a totally different scenario, but I can show you how it can be done.
It seems to work when extracted to folders, but not desktop - is this correct?
No, you would simply select the Desktop for the intended User(s) as in:
Expand|Select|Wrap|Line Numbers
  1. C:\Documents and Settings\All Users\Desktop
Nov 3 '11 #5

P: 7
I would totally appreciate it - if its not too much trouble. I've been struggling for a few days with this. I apologize if not completely clear on that. If not, this is helpful anyway. Thanks!
Nov 3 '11 #6

ADezii
Expert 5K+
P: 8,679
@Mike:
Just give me some time, and I'll show you how it can be done.
Nov 3 '11 #7

ADezii
Expert 5K+
P: 8,679
The following Demo will:
  1. Open the Standard Windows Save As Dialog Box.
  2. The User can now select a specific PATH, then enter a File name in the space allotted. The Default Extension will be *.csv.
  3. The Table Level2_Extract will then be Exported to the Folder and File name specified by the User.
  4. The User will receive a Confirmation that this process has been completed.
  5. The Code is a little too much to Post, so I'll simply make the Demo available as an Attachment, and should you have any questions, please feel free to ask.
Attached Files
File Type: zip Export.zip (136.3 KB, 336 views)
Nov 3 '11 #8

P: 7
Thanks so much! This is great - I actually have a use to implement both methods. However, I have one question - going back to the original solution - where you build the String for the Qualified Path to the File. When the file is extracted, no header records are included. Is there a way to include the header record in the "Level2_Extract"? Thanks again so much!
Nov 4 '11 #9

ADezii
Expert 5K+
P: 8,679
@Mike James:
What, exactly, do you mean by Header Records?
Nov 4 '11 #10

P: 7
Meaning Headings for the Columns - Customer, Address, etc. Example:
Customer Address City State
John C 5 Main St Boston MA
Sally Y 4 Maple St Austin TX

For some reason, they are not extracting using the code above. But they do when extracting from original macro. Kind of odd.

Again, thanks so much.
Nov 4 '11 #11

ADezii
Expert 5K+
P: 8,679
@Mike James:
Make 1 small adjustment in the TransferText() Method to include Field Headers (notice the addition of True as the Last Argument):
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acExportDelim, "", conTABLE_NAME, cDlg.GetName, True
Nov 4 '11 #12

P: 7
ADezii - Thanks so much, you have been tremendously helpful - this works!
Nov 7 '11 #13

ADezii
Expert 5K+
P: 8,679
You are quite welcome, Mike.
Nov 7 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.