none
How to create a open file dialog box and to import multiple xl files one at a time on selection

    Question

  • Hi i have following code to open file dialog box as of windows which will open a desired folder from my desktop and i want a code which will allow user to select xl files one file at a time from that folder to import in an access table. following code works fine to open the file dialog box but when i select a file to import is gives me "error 3051 the miscrosoft office access database engine cannot open or write to the file from givne path, you need permission to read or write data"

    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
        Dim tb_FileName As String
       
     
        'Set the starting look location
        Dim strComPath As String
        strComPath = "C:\Documents and Settings\appl814\Desktop\PLMXL\"
      Const strcTableName As String = "CapBuyDetail"
     
        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 = True
            .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
     
     
     
        tb_FileName = strFilePath
     
        Set fd = Nothing
        ' Import into Access:
      DoCmd.TransferSpreadsheet acImport, _
       acSpreadsheetTypeExcel97, strcTableName, _
       strComPath, True
      

    End Sub

    Saturday, October 30, 2010 1:33 AM

Answers

  • I got it to work.  the spreadsheet type was still wrong.  You didn't put the "ac" into the type

     

    DoCmd.TransferSpreadsheet TransferType:=acImport, _
       SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:=strcTableName, _
       FileName:=tb_FileName, HasFieldNames:=True


    jdweng
    • Marked as answer by Bessie Zhao Friday, November 05, 2010 9:00 AM
    Saturday, October 30, 2010 10:53 PM

All replies

  • acSpreadsheetTypeExcel97 is not a valid option.  The choices are

    acSpreadsheetTypeExcel12
    acSpreadsheetTypeExcel12Xml
    acSpreadsheetTypeExcel3
    acSpreadsheetTypeExcel4
    acSpreadsheetTypeExcel5
    acSpreadsheetTypeExcel7
    acSpreadsheetTypeExcel8
    acSpreadsheetTypeExcel9
    acSpreadsheetTypeLotusWJ2
    acSpreadsheetTypeLotusWJ1
    acSpreadsheetTypeLotusWJ3
    acSpreadsheetTypeLotusWJ4


    jdweng
    Saturday, October 30, 2010 9:43 PM
  • after changing acspreadsheettype excel to 9 it still gives same error.
    Saturday, October 30, 2010 9:52 PM
  • The function is defined as

     

    Sub TransferSpreadsheet([TransferType As AcDataTransferType = acImport], [SpreadsheetType As AcSpreadSheetType = acSpreadsheetTypeExcel12Xml], [TableName], [FileName], [HasFieldNames], [Range], [UseOA])

    So I normal use the format below.  I got the same error you got until I corrected the Filename.  the I got a 2nd error becuawe the table name wasn't correct.  I now get a new error "Run time Error 3170" could not find installable ISAM.  Not sure how to fix this problem.

    From :

    DoCmd.TransferSpreadsheet TransferType:=acImport, _
       SpreadsheetType:=SpreadsheetTypeExcel9, TableName:=strcTableName, _
       FileName:=tb_FileName, HasFieldNames:=True

    To :

    DoCmd.TransferSpreadsheet TransferType:=acImport, _
       SpreadsheetType:=SpreadsheetTypeExcel9, TableName:=rcTableName, _
       FileName:=ComPath, HasFieldNames:=True

     

     


    jdweng
    Saturday, October 30, 2010 10:37 PM
  • I got it to work.  the spreadsheet type was still wrong.  You didn't put the "ac" into the type

     

    DoCmd.TransferSpreadsheet TransferType:=acImport, _
       SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:=strcTableName, _
       FileName:=tb_FileName, HasFieldNames:=True


    jdweng
    • Marked as answer by Bessie Zhao Friday, November 05, 2010 9:00 AM
    Saturday, October 30, 2010 10:53 PM
  • Hi Joel, Thanks a lot it worked. can you please suggest that after file is imported how to copy that file to backup foler and than to delete it from existing folder
    Sunday, October 31, 2010 2:40 AM
  • Set fs = CreateObject("Scripting.FileSystemObject")
    fs.MoveFile source:="c:\temp1\book1.xls" destination:="c:\temp2\book1.xls"
    jdweng
    Sunday, October 31, 2010 3:02 AM
  • it throws error "wrong number of arguments or invalid property assignment"
    Sunday, October 31, 2010 3:24 PM
  • Dim fso As Object, f As File
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("C:\main\Book1.xls")
    f.Move "C:\backup\Book1.xls"
    Set f = Nothing
    Set fso = Nothing
    

    Andrey V Artemyev | Saint-Petersburg, Russia
    Sunday, October 31, 2010 4:11 PM
  • Why bother with the overhead of FSO when VBA has the same functionality built into it?

    Name "C:\main\Book1.xls" As "C:\backup\Book1.xls"

    "Andrey Artemyev" wrote in message news:8dadb341-248f-4dcd-ba67-741a6e3d18cd@communitybridge.codeplex.com...

    Dim fso As Object, f As File
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile("C:\main\Book1.xls")
    f.Move "C:\backup\Book1.xls"
    Set f = Nothing
    Set fso = Nothing
    

    Andrey V Artemyev | Saint-Petersburg, Russia


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Sunday, October 31, 2010 6:12 PM
  • Hi Douglas,

    Is your code based on to move book1? but user may select any xl file so this code will not work. I want code to copy any xl file that users select to import and than paste it in backup folder and delete that selected file from existsing folder.

    Thanks for your help, looking forward to get reply

    Sunday, October 31, 2010 7:27 PM
  • Use the code from http://www.mvps.org/access/api/api0001.htm to get the file name into a variable. Copy everything between "Code Start" and "Code End" and paste it into a new module (not a class module nor a module associated with a form or report). Use code like

    Dim strFilter As String
    Dim strInputFileName as string

    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    strInputFileName = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)

    to get the full path to the file into strInputFile. Once you've done that, you can determine the file only as Dir(strInputFileName), so that your code to move will be\

    Name strInputFileName As "C:\backup\" & Dir(strInputFileName)

     


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Sunday, October 31, 2010 8:56 PM