473,226 Members | 1,601 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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

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()
  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
  9.  .AllowMultiSelect = False
  10.  .ButtonName = "Open"
  11.  .InitialFileName = "C:\"
  12.  .Title = "Select a File to Import"
  13.  .InitialView = msoFileDialogViewDetails
  15.    If .Show Then
  16.      DoCmd.TransferSpreadsheet acImport, , "Data", .SelectedItems(1), True, "Data!"
  17.    End If
  18. End With
  19. End Sub
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 5206
32,554 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
451 Expert Mod 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
8,834 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
  7.  .AllowMultiSelect = False
  8.  .ButtonName = "Open"
  9.  .InitialFileName = "C:\"
  10.  .Title = "Select a File to Import"
  11.  .InitialView = msoFileDialogViewDetails
  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
Nice to see an old friend (not speaking literally of course!)
Jun 22 '21 #4
451 Expert Mod 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()
  3. Dim strFilePath As String
  5. ' Set options for the dialog box.
  6.     With Application.FileDialog(msoFileDialogFilePicker)
  7.         .Title = "Locate the spreadsheet folder and click on 'Open'"
  9.     ' Clear out the current filters, and add our own.
  10.         .Filters.Clear
  11.         .Filters.Add "Spreadsheet files", "*.xlsx;*.xls"
  13.     ' Set the start folder
  14.         .InitialFileName = "C:\" 'change to whichever folder you want
  16.     ' Call the Open dialog routine.
  17.         .Show
  19.     ' Return the path and file name.
  20.         strFilePath = .SelectedItems(1)
  22.         DoCmd.TransferSpreadsheet acImport, , "Data", strFilePath, True, "Data!"
  24.     End With
  26. End Sub
The strFilePath variable could also be omitted of course.
I often use that to save the path in a textbox
Jun 22 '21 #5
272 256MB
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
32,554 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
32,554 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

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

Similar topics

by: Sport Girl | last post by:
Hi all , i need to write into an excel sheet file the data retreived from the database. i have the script but the problem is that i can't get the data written in the excel file. Can anybody help...
by: Aniasp | last post by:
I am new to ASP. Request you to tell me how I could save .gif/.doc file in Access table from ASP. Please let me know complete solution including Datatype required to set in Access table & ASP code...
by: Elavarasi | last post by:
hi I want to import data from excel sheet to ms access database in asp. sample code if possible regards ela...
by: inepu | last post by:
I have some excel sheets that I would like to manage in access. I've created the access tables and relationships; the excel sheet is only one table and with duplicate data, I'd like to make sure that...
by: presario | last post by:
Hello to all, I have a problem "to convert Excel files to MS Access table and load the data to the application " I would really appreciate the work If anybody can help me in this...
by: satenova | last post by:
Hello Friends, I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works...
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
by: vikassawant | last post by:
hi, It's very crazy problem.I want to import excel sheet data in to JTable but I can't find any solution. Actually what I want,suppose there are 7 rows and 5 columns in a table.If I...
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
by: rajeevs | last post by:
Hi All I have a text file which has records in a multi line structure. Each record is separated with a blank line in between. Some records will have 9 fields (in text file it is 9 lines) and some...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.