Results 1 to 8 of 8
  1. #1
    Fannes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    5

    Button in form to open Import Excel Spreadsheet dialog box is not working

    Dear all,

    Hopefully you can help me out. Just like my colleagues I'm not familiar with access and only starting to work with basic things.

    I would like to have a button in my form that opens the Import excel spreadsheet dialog box. Because we have to upload different files with different names as new tables. So that we can manual select in which folder, which file we are going to upload and save as table X. (x to choose at upload)

    I searched the web already some hours now and I always get stuck on the same error.
    Command1 is my button, Caption is: Upload file Week xx Country File Adjustment Report and Event procedure here below:
    Code:
    Private Sub Command1_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
     
    Set fd = Nothing
    End Sub
    When I execute this button then I get the following error: Compile error: User-defined type not defined. (References database: Microsoft access 14.0 object library is on and Microsoft Office 14.0 access database engine object also)



    Thy upfront if you know a possible solution for a noob in access
    brgds

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need the Microsoft Office 14.0 (or whatever number) Object Library reference

  3. #3
    Fannes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    5
    That's active

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Provide a screen print of your references

  5. #5
    Fannes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    5
    Please find herewith the print screen
    Attached Thumbnails Attached Thumbnails References.png   References.jpg  

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See post 2!

  7. #7
    Fannes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    5
    Quote Originally Posted by aytee111 View Post
    See post 2!
    Hi Aytee,

    See line 2 in the print screen.

    ---
    sorry, now I see it, it's not access but microsoft.
    I'll try it.
    Thy

  8. #8
    Fannes is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    5
    Thy Aytee, it works fine.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import From Excel Spreadsheet on Sharepoint Site.
    By Robeen in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2016, 09:55 AM
  2. Replies: 2
    Last Post: 03-26-2013, 10:59 AM
  3. How to import excel spreadsheet into an Access subform
    By upfish in forum Import/Export Data
    Replies: 1
    Last Post: 10-03-2012, 01:53 PM
  4. Replies: 10
    Last Post: 11-09-2011, 01:56 PM
  5. import excel spreadsheet though outlook
    By bopsgtir in forum Import/Export Data
    Replies: 0
    Last Post: 03-18-2011, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums