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

Prompt user to search for file to import using macro actions

P: 22
I am creating a form to do a variety of things, but the first step is to import a file from Excel into an existing table. I know this can be done using the 'TransferSpreadsheet' action. However, the file name will be different each time.

Is there a command or something that can be entered into the 'file name' argument for this action that will allow the user to browse for the file to be imported?
May 15 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,634
I am creating a form to do a variety of things, but the first step is to import a file from Excel into an existing table. I know this can be done using the 'TransferSpreadsheet' action. However, the file name will be different each time.

Is there a command or something that can be entered into the 'file name' argument for this action that will allow the user to browse for the file to be imported?
Yes, but how to go about this depends on your Access Version. What Version of Access are you running?
May 15 '07 #2

P: 22
Yes, but how to go about this depends on your Access Version. What Version of Access are you running?

I am using Access2000.
May 15 '07 #3

Denburt
Expert 100+
P: 1,356
You will need to set a reference in VBA (tools-References) then find and click on "Microsoft Office" to use this.
Expand|Select|Wrap|Line Numbers
  1. Dim myDialog As FileDialog
  2. Dim strFile As String
  3. Dim strSearchPath as string
  4. Dim vrtSelectedItem As Variant
  5. Set myDialog = Application.FileDialog(msoFileDialogOpen)
  6. 'Use strSearchPath  if you want to start in a particular directory. You can leave it out if you wish (as I have it commented out right now) and it will start with a default directory of Windows choosing.
  7. 'strSearchPath ="C:\SomeDirectory\SomeSubDirectory"
  8. With myDialog
  9.     .AllowMultiSelect = True
  10.      .Filters.Add "Excel Files", "*.xls", 1
  11.     .Title = "Please Locate the Files to Import!"
  12.     .InitialFileName = strSearchPath
  13.      If .Show = -1 Then
  14.  
  15.     For Each vrtSelectedItem In .SelectedItems
  16.       'I pass the file information to another routine that handles the transfer  
  17.       ImportIt (vrtSelectedItem)
  18.     Next vrtSelectedItem
  19.         MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
  20.         Else
  21.         'The user pressed Cancel.
  22.         End If
  23.    Set myDialog = Nothing
  24. End With
May 15 '07 #4

P: 22
You will need to set a reference in VBA (tools-References) then find and click on "Microsoft Office" to use this.
Expand|Select|Wrap|Line Numbers
  1. Dim myDialog As FileDialog
  2. Dim strFile As String
  3. Dim strSearchPath as string
  4. Dim vrtSelectedItem As Variant
  5. Set myDialog = Application.FileDialog(msoFileDialogOpen)
  6. 'Use strSearchPath  if you want to start in a particular directory. You can leave it out if you wish (as I have it commented out right now) and it will start with a default directory of Windows choosing.
  7. 'strSearchPath ="C:\SomeDirectory\SomeSubDirectory"
  8. With myDialog
  9.     .AllowMultiSelect = True
  10.      .Filters.Add "Excel Files", "*.xls", 1
  11.     .Title = "Please Locate the Files to Import!"
  12.     .InitialFileName = strSearchPath
  13.      If .Show = -1 Then
  14.  
  15.     For Each vrtSelectedItem In .SelectedItems
  16.       'I pass the file information to another routine that handles the transfer  
  17.       ImportIt (vrtSelectedItem)
  18.     Next vrtSelectedItem
  19.         MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
  20.         Else
  21.         'The user pressed Cancel.
  22.         End If
  23.    Set myDialog = Nothing
  24. End With
Thank you!
May 15 '07 #5

Denburt
Expert 100+
P: 1,356
Thank you!
You are quite welcome.
May 15 '07 #6

P: 9
Is this process different for Access 97? How do I "use" this once it is created? I would like to use this on a form where a user would click on a button and have it prompt to import a file. Thanks!
May 22 '07 #7

P: 13
i need to do the same thing in Access 2003 (and it must work in 2007 as well)

does anyone know exactly how to link this VB into a macro to enable me to import an excel sheet which could be anywhere on someones system...?

thanks
Jun 7 '07 #8

ADezii
Expert 5K+
P: 8,634
Is this process different for Access 97? How do I "use" this once it is created? I would like to use this on a form where a user would click on a button and have it prompt to import a file. Thanks!
  1. This code would not work in Access 97, since the FileDialog Object does not exist.
  2. To demo how Denburt's code could be used, I've displayed a small segment of code, simply insert it where appropriate. It is listed below:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilePath As String
    2.  
    3. If .Show = -1 Then
    4.   For Each vrtSelectedItem In .SelectedItems
    5.     'There will only be 1 Item in the Collection since
    6.     'MultiSelect should be set to False in this case
    7.     strFilePath = vrtSelectedItem
    8.   Next vrtSelectedItem
    9. Else
    10.   'The user pressed Cancel.
    11.    Exit Sub
    12. End If
    13.  
    14. DoCmd.TransferSpreadsheet acImport, <excel version>, <table name>, strFilePath, <has field names>
Jun 7 '07 #9

Denburt
Expert 100+
P: 1,356
File Dialog using API Functions

Took a little digging but I found an article for Acc97.

Try this and let us know how it turns out for you.

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

If I am not mistaken one of the samples for Acc97 has this or similar code in their database for refreshing table links.
Jun 7 '07 #10

Post your reply

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