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

Use a button to open a File dialog box to import a Excel spreadsheet

P: 16
I need to create a Form that has a button and when you click the button it opens a box that lets you navigate through the computer's files and select a Excel spreadsheet to import that data to specified tables.

Note: I need to do this for 2 seperate Excel spreadsheets and 1 Excel Workbook (which contains 2 spreadsheets I need to import). and each import to a seperate table so in total there are 4 tables.

I don't know where to start.
Mar 11 '10 #1
Share this Question
Share on Google+
21 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
This code will open a filepicker dialog, and store the full path to the file, in a textbox on the form.
To use the filedialog you need ot set a reference to the:
"Windows Script Host Object Model"

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_GetFileName_Click()
  2. '************************************************************************
  3. 'Lets get the file name
  4.     Debug.Print "Getting File Name"
  5.     'Declare a variable as a FileDialog object.
  6.     Dim fd As FileDialog
  7.  
  8.     'Set the starting look location
  9.     Dim strComPath As String
  10.     strComPath = "G:\DATA\METRO-TS\02 Phase 2\01 Project management\Tdo Tender docs\2009-07-03_intcrossandfinal_reviewcomments\"
  11.  
  12.     Dim strFilePath As String
  13.     'Create a FileDialog object as a File Picker dialog box.
  14.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  15.  
  16.     'Declare a variable to contain the path
  17.     'of each selected item. Even though the path is a String,
  18.     'the variable must be a Variant because For Each...Next
  19.     'routines only work with Variants and Objects.
  20.     Dim vrtSelectedItem As Variant
  21.  
  22.     'Use a With...End With block to reference the FileDialog object.
  23.     With fd
  24.         .InitialFileName = strComPath
  25.         .AllowMultiSelect = False
  26.         .Filters.Clear
  27.         'Add filter to only show excel files.
  28.         .Filters.Add "Excel files", "*.xls", 1
  29.         'Use the Show method to display the File Picker dialog box and return the user's action.
  30.         'The user pressed the action button.
  31.         If .Show = -1 Then
  32.                 strFilePath = .SelectedItems(1)
  33.             'Step through each string in the FileDialogSelectedItems collection.
  34.             'For Each vrtSelectedItem In .SelectedItems
  35.  
  36.                 'vrtSelectedItem is a String that contains the path of each selected item.
  37.                 'You can use any file I/O functions that you want to work with this path.
  38.                 'This example simply displays the path in a message box.
  39.              '   strFilePath: " & vrtSelectedItem
  40.  
  41.             'Next vrtSelectedItem
  42.  
  43.         Else
  44.             'The user pressed Cancel.
  45.             DoCmd.Hourglass (False)
  46.             MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
  47.             Set fd = Nothing
  48.             Exit Sub
  49.         End If
  50.     End With
  51.  
  52.  
  53.  
  54.     Me.tb_FileName = strFilePath
  55.  
  56.     Set fd = Nothing
  57. End Sub
This line will import the FIRST sheet of the excel workbook into a table:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ReturnComments", Me.tb_FileName, True
You have to set the Excel version (acSpreadsheetTypeExcel9 in my case) correctly, but maybe there is a way of "reading" that out of the file, or you can try around.

The TransferSpreadsheet has an option to specify the range, so I guess using that you can make it import something other then the FIRST sheet in the workbook. Haven't tried.
Mar 11 '10 #2

P: 16
I changed the location of where the file picker starts but, is there anything else I need to change? It didn't work for me. Sorry i'm a noob.


where would I include the DoCmd.TransferSpreadsheet into my code. I used that before for my macro so How could I be able to select a spreadsheet in a workbook?
Mar 11 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Well for me, I used the import in a form, where I also had to add other information, which would then be processed along with the import. Therefore I used a command button to place the filepath in a textbox tb_FileName.

If you want to proceed straight to importing, replace:
Expand|Select|Wrap|Line Numbers
  1. Me.tb_FileName = strFilePath 
with
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ReturnComments", strFilePath , True
You have to replace "tbl_ReturnComments" with the name of the table you want to import into.

The TransferSpreadsheet will take additional arguments, in which you can specify the range (and im guessing that it defaults to sheet1). I haven't tried anything besides importing the first sheet, so I can't help you there, but im sure someone else here might be able to.
Mar 11 '10 #4

P: 16
it says that strFilePath is Empty
Mar 11 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Try taking a look at this article, it will save you alot of pain in the long run, to take the time to read it.
Debugging VBA

Im guessing you must have changed some code somewhere, so if you can't find the error yourself, after having read the article, post your revised code here.
Mar 11 '10 #6

P: 16
i got a compile error

fd As FileDialog

it stated "User defined-type not defined"
Mar 11 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
Did you remember to set the reference?
To use the filedialog you need ot set a reference to the:
"Windows Script Host Object Model"
In VBA window, press Tools, References, then find "Windows Script Host Object Model" and check the box.
Mar 11 '10 #8

P: 16
references is shaded gray
Mar 11 '10 #9

P: 16
It is now referenced and I'm still running into the same problem
Mar 11 '10 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
You do realise I cannot see your screen right?

What does same problem mean?
That your getting the "User defined-type not defined" error, or strFilePath is empty?
Mar 11 '10 #11

NeoPa
Expert Mod 15k+
P: 31,768
Jerelp,

I expect this all seems a bit confusing to you. You say you don't know where to start so I assume you have quite limited experience both with coding and using a forum like this.

Remember that we're here to help you, but we need you to explain your situation as clearly as possible. That way, and only that way, can we be of any use to you. The most important thing to remember is, as Smiley says, that we only know what you tell us. We have no sight of your project, nor even of your face to get extra clues. That's why it's so important that you take real care to explain clearly.

I hope this helps you to get your issue resolved and Welcome to Bytes!
Mar 11 '10 #12

P: 16
alright i understand. I am very sorry. Can we start this over again from the beginning and I'll try to be as specific as possible this time. I really do appreciate your help.
Mar 12 '10 #13

P: 16
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  2. "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
  3.  
  4. Private Type OPENFILENAME
  5.     lStructSize As Long
  6.     hwndOwner As Long
  7.     hInstance As Long
  8.     lpstrFilter As String
  9.     lpstrCustomFilter As String
  10.     nMaxCustFilter As Long
  11.     nFilterIndex As Long
  12.     lpstrFile As String
  13.     nMaxFile As Long
  14.     lpstrFileTitle As String
  15.     nMaxFileTitle As Long
  16.     lpstrInitialDir As String
  17.     lpstrTitle As String
  18.     flags As Long
  19.     nFileOffset As Integer
  20.     nFileExtension As Integer
  21.     lpstrDefExt As String
  22.     lCustData As Long
  23.     lpfnHook As Long
  24.     lpTemplateName As String
  25. End Type
  26.  
  27. Function LaunchCD(strform As Form) As String
  28.     Dim OpenFile As OPENFILENAME
  29.     Dim lReturn As Long
  30.     Dim sFilter As String
  31.     OpenFile.lStructSize = Len(OpenFile)
  32.     OpenFile.hwndOwner = strform.Hwnd
  33.     sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
  34.       "Excel Files (*.XLS)" & Chr(0) & "*.XLS" & Chr(0)
  35.     OpenFile.lpstrFilter = sFilter
  36.     OpenFile.nFilterIndex = 1
  37.     OpenFile.lpstrFile = String(257, 0)
  38.     OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
  39.     OpenFile.lpstrFileTitle = OpenFile.lpstrFile
  40.     OpenFile.nMaxFileTitle = OpenFile.nMaxFile
  41.     OpenFile.lpstrInitialDir = "I:\MIS\coop files\Austell Fuel Consumption\AFC Data\"
  42.     OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
  43.     OpenFile.flags = 0
  44.     lReturn = GetOpenFileName(OpenFile)
  45.         If lReturn = 0 Then
  46.             MsgBox "A file was not selected!", vbInformation, _
  47.               "Select a file using the Common Dialog DLL"
  48.          Else
  49.             LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
  50.          End If
  51. End Function
  52.  
  53.  
This is the code that I have used in a module to open the file looker and I call the function from my button with this code

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.  
  3.     Me!Text1 = LaunchCD(Me)
It then shows the location in a text box. I've tried to call the location in my DoCMD.TransferSpreadsheet but, it hasn't worked out for me

Expand|Select|Wrap|Line Numbers
  1. Private Sub Import_LPMH_Click()
  2.  
  3. Me!Text1 = File
  4.  
  5. DoCmd.TransferSpreadsheet ([acImport], 8, "LPMH NEWDATA", File , 1,"March 09$")
and I've tried different variations of this but, I get syntax errors.

I hope this helps. Thank you.
Mar 12 '10 #14

P: 16
I've also tried to use your code and made the button name: "btn_GetFileName" and made the text box "tb_FileName" I've also referenced to the:
"Windows Script Host Object Model"
and I'm still getting a compile error because "User defined-type not defined"
and it is highlighting
Expand|Select|Wrap|Line Numbers
  1. Dim fd As FileDialog
Here is the code that is on my button i've only change the location path start.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_GetFileName_Click()
  2. 'Lets get the file name
  3.     Debug.Print "Getting File Name"
  4.     'Declare a variable as a FileDialog object.
  5.     Dim fd As FileDialog
  6.  
  7.     'Set the starting look location
  8.     Dim strComPath As String
  9.     strComPath = "I:\"
  10.  
  11.     Dim strFilePath As String
  12.     'Create a FileDialog object as a File Picker dialog box.
  13.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  14.  
  15.     'Declare a variable to contain the path
  16.     'of each selected item. Even though the path is a String,
  17.     'the variable must be a Variant because For Each...Next
  18.     'routines only work with Variants and Objects.
  19.     Dim vrtSelectedItem As Variant
  20.  
  21.     'Use a With...End With block to reference the FileDialog object.
  22.     With fd
  23.         .InitialFileName = strComPath
  24.         .AllowMultiSelect = False
  25.         .Filters.Clear
  26.         'Add filter to only show excel files.
  27.         .Filters.Add "Excel files", "*.xls", 1
  28.         'Use the Show method to display the File Picker dialog box and return the user's action.
  29.         'The user pressed the action button.
  30.         If .Show = -1 Then
  31.                 strFilePath = .SelectedItems(1)
  32.             'Step through each string in the FileDialogSelectedItems collection.
  33.             'For Each vrtSelectedItem In .SelectedItems
  34.  
  35.                 'vrtSelectedItem is a String that contains the path of each selected item.
  36.                 'You can use any file I/O functions that you want to work with this path.
  37.                 'This example simply displays the path in a message box.
  38.              '   strFilePath: " & vrtSelectedItem
  39.  
  40.             'Next vrtSelectedItem
  41.  
  42.         Else
  43.             'The user pressed Cancel.
  44.             DoCmd.Hourglass (False)
  45.             MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
  46.             Set fd = Nothing
  47.             Exit Sub
  48.         End If
  49.     End With
  50.  
  51.  
  52.  
  53.     Me.tb_FileName = strFilePath
  54.  
  55.     Set fd = Nothing
  56.  
  57. End Sub
Again thank you for your help and I am sorry about yesterday. I've tried both solutions and neither of them work. Thank you for your help.
Mar 12 '10 #15

NeoPa
Expert Mod 15k+
P: 31,768
@jerelp
No problem. We like that type of attitude. It allows for progress to be made.

@Smiley, I'll leave this with you for now, but holler if I can help. I just jumped in to moderate.
Mar 12 '10 #16

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im very very sorry......I was giving you the wrong reference, its not "Windows Script Host Object Model"

It should have Been:
"Microsoft Office XX Object Library"
The XX is the version of office, you have installed, in my case its 12.0 (which is office 2003)
Mar 12 '10 #17

P: 16
Thanks! the file picker opens and populate the text box.

Now I've been trying to work on importing the data using a seperate button that gets the location of the file from the text box which the button uses and this code

Expand|Select|Wrap|Line Numbers
  1. Private Sub LPMH_Bttn_Click()
  2.  
  3. Me.tb_FileName = strFilePath
  4. DoCmd.TransferSpreadsheet acImport], 8, "LPMH NEWDATA", strFilePath, 1,"March 09$"
Every time I "click" i get
"Run-Time Error '2522' The action or method requires a File Name
and I'm also not sure if 8 is the correct reference to which spreadsheet type. The Excel file I am using is Microsoft Office Excel 97-2003 Worksheet.
Mar 12 '10 #18

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im guessing thats because strFilePath is not defined within the code you have posted. I recommend you read: Variable scope in vba for ms access

The variable strFilePath is used in another module, and as such is not visible to this module, unless declared with a public scope.

Access can work with variables even if they have not been declared, which to the new programmer can (in my opinion atleast) be quite confusing. To prevent stuff like this, I recommend at the top of your modules right beneath the Option Compare Database add a Option Explicit, requiring all variables to be declared.


If you still have the line of code
Expand|Select|Wrap|Line Numbers
  1. Me.tb_FileName = strFilePath 
at the end of the procedure used to open the filedialog, i would suggest modifying your button procedure accordingly:

Expand|Select|Wrap|Line Numbers
  1. Private Sub LPMH_Bttn_Click() 
  2.   If IsNull(Me.tb_FileName) Then
  3.     Msgbox "You need to select a file to import first"
  4.     Exit Sub
  5.   End If  
  6.  
  7. DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1,"March 09$"
  8. End Sub
Mar 12 '10 #19

NeoPa
Expert Mod 15k+
P: 31,768
@jerelp
I noticed a stray "]" in your code on line #4. This may be due to a number of things, but all of these possible things should be avoided, as they waste time and effort, for you as well as us.

Here's something I put together earlier that you may find very helpful. It repeats a point Smiley has made already, but that's no bad thing. It's a good point :D
When posting any code on here please :
  1. Ensure you have Option Explicit set (See Require Variable Declaration).
  2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
  4. Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
Mar 13 '10 #20

NeoPa
Expert Mod 15k+
P: 31,768
A new question, which is exactly the same but..., has now been moved to File DialogBox to Import Spreadsheet.
Jul 13 '10 #21

P: 1
It looks like this thread is pretty old, but I hope someone can help. I was able to get the button and code to work correctly except when it creates the table it is deleting all the data that is contained on the worksheet except the column header.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenFile_Click()
  2. '************************************************************************
  3. 'Lets get the file name
  4.     Debug.Print "Getting File Name"
  5.     'Declare a variable as a FileDialog object.
  6.     Dim fd As FileDialog
  7.  
  8.     'Set the starting look location
  9.     Dim strComPath As String
  10.     strComPath = "C:\"
  11.  
  12.     Dim strFilePath As String
  13.     'Create a FileDialog object as a File Picker dialog box.
  14.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  15.  
  16.     'Declare a variable to contain the path
  17.     'of each selected item. Even though the path is a String,
  18.     'the variable must be a Variant because For Each...Next
  19.     'routines only work with Variants and Objects.
  20.     Dim vrtSelectedItem As Variant
  21.  
  22.     'Use a With...End With block to reference the FileDialog object.
  23.     With fd
  24.         .InitialFileName = strComPath
  25.         .AllowMultiSelect = False
  26.         .Filters.Clear
  27.         'Add filter to only show excel files.
  28.         .Filters.Add "Excel files", "*.xls*", 1
  29.         'Use the Show method to display the File Picker dialog box and return the user's action.
  30.         'The user pressed the action button.
  31.         If .Show = -1 Then
  32.                 strFilePath = .SelectedItems(1)
  33.             'Step through each string in the FileDialogSelectedItems collection.
  34.             'For Each vrtSelectedItem In .SelectedItems
  35.  
  36.                 'vrtSelectedItem is a String that contains the path of each selected item.
  37.                 'You can use any file I/O functions that you want to work with this path.
  38.                 'This example simply displays the path in a message box.
  39.              '   strFilePath: " & vrtSelectedItem
  40.  
  41.             'Next vrtSelectedItem
  42.  
  43.         Else
  44.             'The user pressed Cancel.
  45.             DoCmd.Hourglass (False)
  46.             MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
  47.             Set fd = Nothing
  48.             Exit Sub
  49.         End If
  50.     End With
  51.  
  52.  
  53.     Me.tb_FileName = strFilePath
  54.  
  55.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ItmInfo", strFilePath, True
  56.  
  57.  
  58.     Set fd = Nothing
  59. End Sub
Feb 6 '15 #22

Post your reply

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