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 (acSpreadsheetT ypeExcel9 in my case) correctly, but maybe there is a way of "reading" that out of the file, or you can try around.

The TransferSpreads heet 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.TransferS preadsheet 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_ReturnComm ents" with the name of the table you want to import into.

The TransferSpreads heet 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.TransferS preadsheet 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_GetFileNam e" and made the text box "tb_FileNam e" 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.