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

[jerelp]

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.

[TheSmileyCoder]

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"

Private Sub btn_GetFileName_Click()
'************************************************************************
'Lets get the file name
    Debug.Print "Getting File Name"
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
 
    'Set the starting look location
    Dim strComPath As String
    strComPath = "G:\DATA\METRO-TS\02 Phase 2\01 Project management\Tdo Tender docs\2009-07-03_intcrossandfinal_reviewcomments\"
 
    Dim strFilePath As String
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
 
    'Use a With...End With block to reference the FileDialog object.
    With fd
        .InitialFileName = strComPath
        .AllowMultiSelect = False
        .Filters.Clear
        'Add filter to only show excel files.
        .Filters.Add "Excel files", "*.xls", 1
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
                strFilePath = .SelectedItems(1)
            'Step through each string in the FileDialogSelectedItems collection.
            'For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
             '   strFilePath: " & vrtSelectedItem
 
            'Next vrtSelectedItem
 
        Else
            'The user pressed Cancel.
            DoCmd.Hourglass (False)
            MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
            Set fd = Nothing
            Exit Sub
        End If
    End With
 
 
 
    Me.tb_FileName = strFilePath
 
    Set fd = Nothing
End Sub

This line will import the FIRST sheet of the excel workbook into a table:

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.

[jerelp]

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?

[TheSmileyCoder]

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:

Me.tb_FileName = strFilePath 

with

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.

[jerelp]

it says that strFilePath is Empty

[TheSmileyCoder]

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.

[jerelp]

i got a compile error

fd As FileDialog

it stated "User defined-type not defined"

[TheSmileyCoder]

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.

[jerelp]

references is shaded gray

[jerelp]

It is now referenced and I'm still running into the same problem

[TheSmileyCoder]

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?

[NeoPa]

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!

[jerelp]

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.

[jerelp]

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
 
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0) & _
      "Excel Files (*.XLS)" & Chr(0) & "*.XLS" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "I:\MIS\coop files\Austell Fuel Consumption\AFC Data\"
    OpenFile.lpstrTitle = "Select a file using the Common Dialog DLL"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file using the Common Dialog DLL"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function
 
 

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

Private Sub Command1_Click()
 
    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

Private Sub Import_LPMH_Click()
 
Me!Text1 = File
 
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.

[jerelp]

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

Dim fd As FileDialog

Here is the code that is on my button i've only change the location path start.

Private Sub btn_GetFileName_Click()
'Lets get the file name
    Debug.Print "Getting File Name"
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
 
    'Set the starting look location
    Dim strComPath As String
    strComPath = "I:\"
 
    Dim strFilePath As String
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
 
    'Use a With...End With block to reference the FileDialog object.
    With fd
        .InitialFileName = strComPath
        .AllowMultiSelect = False
        .Filters.Clear
        'Add filter to only show excel files.
        .Filters.Add "Excel files", "*.xls", 1
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
                strFilePath = .SelectedItems(1)
            'Step through each string in the FileDialogSelectedItems collection.
            'For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
             '   strFilePath: " & vrtSelectedItem
 
            'Next vrtSelectedItem
 
        Else
            'The user pressed Cancel.
            DoCmd.Hourglass (False)
            MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
            Set fd = Nothing
            Exit Sub
        End If
    End With
 
 
 
    Me.tb_FileName = strFilePath
 
    Set fd = Nothing
 
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.

[NeoPa]

@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.

[TheSmileyCoder]

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)

[jerelp]

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

Private Sub LPMH_Bttn_Click()
 
Me.tb_FileName = strFilePath
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.

[TheSmileyCoder]

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

Me.tb_FileName = strFilePath 

at the end of the procedure used to open the filedialog, i would suggest modifying your button procedure accordingly:

Private Sub LPMH_Bttn_Click() 
  If IsNull(Me.tb_FileName) Then
    Msgbox "You need to select a file to import first"
    Exit Sub
  End If  
 
DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1,"March 09$"
End Sub

[NeoPa]

@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.

[NeoPa]

A new question, which is exactly the same but..., has now been moved to File DialogBox to Import Spreadsheet.

[ksmith247]

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.

Private Sub cmdOpenFile_Click()
'************************************************************************
'Lets get the file name
    Debug.Print "Getting File Name"
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
 
    'Set the starting look location
    Dim strComPath As String
    strComPath = "C:\"
 
    Dim strFilePath As String
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
 
    'Use a With...End With block to reference the FileDialog object.
    With fd
        .InitialFileName = strComPath
        .AllowMultiSelect = False
        .Filters.Clear
        'Add filter to only show excel files.
        .Filters.Add "Excel files", "*.xls*", 1
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
                strFilePath = .SelectedItems(1)
            'Step through each string in the FileDialogSelectedItems collection.
            'For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
             '   strFilePath: " & vrtSelectedItem
 
            'Next vrtSelectedItem
 
        Else
            'The user pressed Cancel.
            DoCmd.Hourglass (False)
            MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
            Set fd = Nothing
            Exit Sub
        End If
    End With
 
 
    Me.tb_FileName = strFilePath
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_ItmInfo", strFilePath, True
 
 
    Set fd = Nothing
End Sub