Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Import excel to access table via command button

    Hi,

    I'm struggling to import a spreadsheet into access via a command button.

    The idea is to give the user an option to select a spreadsheet and that will then be imported into a already defined table called TestData.

    I currently have the below but its just giving errors.

    DoCmd.TransferSpreadsheet acExport, TestData

    Can anyone provide any assistance please?

    Thanks,
    Ian.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You want to import data from Excel:
    Quote Originally Posted by marleyuk View Post
    I'm struggling to import a spreadsheet into access via a command button.
    However, you use:
    Quote Originally Posted by marleyuk View Post
    DoCmd.TransferSpreadsheet acExport, TestData
    Ian.
    Moreover, several parameters are missing. Even if they have default values, you must incluse the comma for them. Search for TransferSpreadsheet method in Access help.
    Have a nice day!

  3. #3
    Join Date
    Feb 2006
    Posts
    213
    I have had a look and come up with this:

    DoCmd.TransferSpreadsheet acImport, 5, "TestData", strInputFileName, True

    however get an error saying "this action or method requires a file name arguement."

    Can anyone point me in the right direction please?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the value of strInputFileName?
    Have a nice day!

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    I picked that up from snippets of code I've looked at. I thought that was how I prompted the code to ask the user what spreadsheet to import?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by marleyuk View Post
    I picked that up from snippets of code I've looked at. I thought that was how I prompted the code to ask the user what spreadsheet to import?
    No, you have to supply a string value, i.e. the name (and full path) of the Excel file you want to import.
    Have a nice day!

  7. #7
    Join Date
    Feb 2006
    Posts
    213
    sorry I don't think I've explained it properly. The idea is to let the user choose one, not to default to the same spreadsheet.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then you have to create an interface (or use an existing one, such as FileDialog) that will return the name of the file. That name is then passed to the TransferSpreadsheet method:
    1. in a Module:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function GetExcelFileName(Optional ByVal Path As String) As String
    
        If Path = "" Then Path = CurrentProject.Path
        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Add "Excel files", "*.xls", 1
            .AllowMultiSelect = False
            .InitialFileName = Path
            .Title = "Select the Excel file to import"
            If .Show = -1 Then GetExcelFileName = .SelectedItems(1)
        End With
    
    End Function
    2. In the procedure where you want to import the file (here named ImportExcelFile):
    Code:
    Sub ImportExcelFile()
    
       Dim strInputFileName As String
       
       ' Use GetExcelFileName(<Path>) to specify another path than 
       ' the one of the current database. Ex. GetExcelFileName(C:\MyExcelFiles)
        strInputFileName = GetExcelFileName
        If Len(strInputFileName) > 0 Then
            DoCmd.TransferSpreadsheet acImport, 5, "TestData", strInputFileName, True
        Else
            MsgBox "You did not select any file.", vbInformation, "Cancel Import"
        End If
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Feb 2006
    Posts
    213
    I'm getting a compile error: variable not defined

    and this line is highlighted:

    Public Function GetExcelFileName(Optional ByVal Path As String) As String

    What would this be?

    Thanks for your help.
    Ian

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where did you create the function (GetExcelFileName) and how do you call it?
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •