473,287 Members | 1,708 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,287 software developers and data experts.

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 5242
NeoPa
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
isladogs
454 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
ADezii
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
  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
454 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()
  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
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
NeoPa
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
NeoPa
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

6
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...
4
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...
4
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...
3
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...
1
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...
0
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...
7
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
2
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...
6
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...
5
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...
0
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"....
0
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
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 =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.