468,790 Members | 1,887 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,790 developers. It's quick & easy.

Import an Excel Sheet with File Dialog in Access into the table

Hi!
I have a splitform where I want to have the button to allow the user to choose and import the file into the table. I have something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command287_Click()
  2.  
  3. With Application.FileDialog(msoFileDialogOpen)
  4.  .Filters.Clear
  5.    .Filters.Add "Excel Files", "*.xlsx,*.xls"
  6.    .Filters.Add "Access Databases", "*.accdb"
  7.  .FilterIndex = 1      'Excel Files
  8.  
  9.  .AllowMultiSelect = False
  10.  .ButtonName = "Open"
  11.  .InitialFileName = "C:\"
  12.  .Title = "Select a File to Import"
  13.  .InitialView = msoFileDialogViewDetails
  14.  
  15.    If .Show Then
  16.      DoCmd.TransferSpreadsheet acImport, , "Data", .SelectedItems(1), True, "Data!"
  17.    End If
  18. End With
  19. End Sub
  20.  
I also set the references to Microsoft Office 16.0 Object Library. When I tried using this button there was this error: Method 'FileDialog' of object '_Application' failed. I'm pretty much new to VBA, so I don't know how to fix it.
Jun 21 '21 #1
7 2851
NeoPa
32,132 Expert Mod 16PB
Hi Barbara. Welcome to Bytes.com.

I'm just going to check for you, but the addition of the library reference rather implies that FileDialog() is NOT a Property of the Application object, but rather of the MS Office object - but I'm going to check anyway.

HAH!!! Shows what I know :-(

FileDialog() IS a Property of the Application object. You need the Microsoft Office 16.0 Object Library (or equivalent) as the definition of the Property is found there. I can't see what the problem is here. There are things you need to do to get this right but it seems clear you've done them all properly.

I think maybe we have to go back to first principles. Does the project compile? Before we can rely on any other messages we need to ensure that all compile errors are resolved first.

Please start by commenting out this code and compiling the project as many times as you need for it to work fully. At that point come back and uncomment these lines and then we can see where we're really at reliably.

Nice work for a newbie BTW ;-) It doesn't look like newbie code.
Jun 21 '21 #2
isladogs
277 Expert 256MB
Actually I believe FileDialog is part of the default Microsoft Access 16.0 Object Library so you shouldn't need to add the Office library for this.
But msoFileDialogOpen isn't supported in that library.
Instead use msoFileDialogFilePicker
See Application.File Dialog property Access
Jun 21 '21 #3
ADezii
8,800 Expert 8TB
  1. Your Base Code is fine, but needs adjustments.
  2. You 'will' need a Reference to the Microsoft Office XX.X Object Library.
  3. You must apply the appropriate Method depending on the Filter.
  4. Hopefully, the following Code will illustrate these points.
Expand|Select|Wrap|Line Numbers
  1. With Application.FileDialog(msoFileDialogOpen)
  2.  .Filters.Clear
  3.    .Filters.Add "Excel Files", "*.xlsx,*.xls"
  4.    .Filters.Add "Access Databases", "*.accdb"
  5.  .FilterIndex = 1      'Excel Files
  6.  
  7.  .AllowMultiSelect = False
  8.  .ButtonName = "Open"
  9.  .InitialFileName = "C:\"
  10.  .Title = "Select a File to Import"
  11.  .InitialView = msoFileDialogViewDetails
  12.  
  13.    If .Show Then
  14.      'Analyze the File's Extension and use the appropriate Method
  15.      Select Case Mid(.SelectedItems(1), InStrRev(.SelectedItems(1), "."))
  16.        Case ".xlsx", ".xls"
  17.          DoCmd.TransferSpreadsheet acImport, , "Data", .SelectedItems(1), True, "Data!"
  18.        Case ".accdb"
  19.          DoCmd.TransferDatabase , "Microsoft Access", .SelectedItems(1), acTable, _
  20.                                   "tblLogins", "tblLogins"
  21.        Case Else
  22.          'will never happen
  23.      End Select
  24.    End If
  25. End With
  26.  
@NeoPa:
Nice to see an old friend (not speaking literally of course!)
Jun 22 '21 #4
isladogs
277 Expert 256MB
Apologies. I was wrong.
You DO need the Microsoft Office XX.X Object Library

I normally use code like this with the msoFileDialogFilePicker option.
To my mind there is no point including accdb files in the file type options if you are then going to ignore them using .FilterIndex = 1:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command287_Click()
  2.  
  3. Dim strFilePath As String
  4.  
  5. ' Set options for the dialog box.
  6.     With Application.FileDialog(msoFileDialogFilePicker)
  7.         .Title = "Locate the spreadsheet folder and click on 'Open'"
  8.  
  9.     ' Clear out the current filters, and add our own.
  10.         .Filters.Clear
  11.         .Filters.Add "Spreadsheet files", "*.xlsx;*.xls"
  12.  
  13.     ' Set the start folder
  14.         .InitialFileName = "C:\" 'change to whichever folder you want
  15.  
  16.     ' Call the Open dialog routine.
  17.         .Show
  18.  
  19.     ' Return the path and file name.
  20.         strFilePath = .SelectedItems(1)
  21.  
  22.         DoCmd.TransferSpreadsheet acImport, , "Data", strFilePath, True, "Data!"
  23.  
  24.     End With
  25.  
  26. End Sub
  27.  
The strFilePath variable could also be omitted of course.
I often use that to save the path in a textbox
Jun 22 '21 #5
SioSio
253 128KB
Barbara1999 wrote
>I also set the references to Microsoft Office 16.0 Object Library.

If you get an error even if it is set to refer to the library, it is possible that the library referenced in the environment where the macro was developed does not exist in the environment in which it is running, or the path is different.
Check if there are any "unreferenced libraries" in the [Browse] dialog.
If the reference is marked as "MISSING", uncheck it and reset it.
Jun 23 '21 #6
NeoPa
32,132 Expert Mod 16PB
Hi Barbara.

Notwithstanding other advice and suggestions, I still feel the issue on your system is yet to be determined. How you code your usage of these objects doesn't really start to become an issue until you can get the system to recognise the classes & objects you're using. I see nothing fundamentally wrong with your code as you already have it. It may not be the best code in the world (though frankly it doesn't look bad at all), but it should certainly work according to what you've shared with us.

As such, I suspect my earlier advice is still your best way forward. You have the reference you need so why doesn't it behave as one could reasonably expect it to? That's for you to find out by getting it to compile first and then building from there. See my earlier post (#2).

ADezii (Hi my friend :-) ) & IslaDogs certainly have experience writing good code so feel free to pick up tips from them once you get the fundamentals working.
Jun 23 '21 #7
NeoPa
32,132 Expert Mod 16PB
I don't mean to leave SioSio out. Their advice is also good. It's always worth checking that all the references added are actually working using the approach they suggest.

Probably do that first. If it makes the problem go away then great. Otherwise - back to instructions from post #2.
Jun 23 '21 #8

Post your reply

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

Similar topics

1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.