Prompt user to search for file to import using macro actions

[kickergirl]

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?

[ADezii]

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?

[kickergirl]

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

I am using Access2000.

[Denburt]

You will need to set a reference in VBA (tools-References) then find and click on "Microsoft Office" to use this.

Dim myDialog As FileDialog
Dim strFile As String
Dim strSearchPath as string
Dim vrtSelectedItem As Variant
Set myDialog = Application.FileDialog(msoFileDialogOpen)
'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.
'strSearchPath ="C:\SomeDirectory\SomeSubDirectory"
With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
     If .Show = -1 Then
 
    For Each vrtSelectedItem In .SelectedItems
      'I pass the file information to another routine that handles the transfer  
      ImportIt (vrtSelectedItem)
    Next vrtSelectedItem
        MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
        Else
        'The user pressed Cancel.
        End If
   Set myDialog = Nothing
End With

[kickergirl]

You will need to set a reference in VBA (tools-References) then find and click on "Microsoft Office" to use this.

Dim myDialog As FileDialog
Dim strFile As String
Dim strSearchPath as string
Dim vrtSelectedItem As Variant
Set myDialog = Application.FileDialog(msoFileDialogOpen)
'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.
'strSearchPath ="C:\SomeDirectory\SomeSubDirectory"
With myDialog
    .AllowMultiSelect = True
     .Filters.Add "Excel Files", "*.xls", 1
    .Title = "Please Locate the Files to Import!"
    .InitialFileName = strSearchPath
     If .Show = -1 Then
 
    For Each vrtSelectedItem In .SelectedItems
      'I pass the file information to another routine that handles the transfer  
      ImportIt (vrtSelectedItem)
    Next vrtSelectedItem
        MsgBox "All data imported into the NewData Table." & vbCrLf & vbCrLf & "We will now post any new data into the data table!"
        Else
        'The user pressed Cancel.
        End If
   Set myDialog = Nothing
End With

Thank you!

[Denburt]

Thank you!

You are quite welcome.

[bae14]

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!

[webcat]

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

[ADezii]

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:
   

   Dim strFilePath As String
    
   If .Show = -1 Then
     For Each vrtSelectedItem In .SelectedItems
       'There will only be 1 Item in the Collection since
       'MultiSelect should be set to False in this case
       strFilePath = vrtSelectedItem
     Next vrtSelectedItem
   Else
     'The user pressed Cancel.
      Exit Sub
   End If
    
   DoCmd.TransferSpreadsheet acImport, <excel version>, <table name>, strFilePath, <has field names>

[Denburt]

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.