TransferSpreadsheet not working

[mari_hitz]

Hi everybody, hope you are all good. I am having an issue with the TransferSpreadsheet method. Currently I have an access database with a table named "PymtElc" and would like to import data to this table from a spreadsheet named the same way. I am using a code I found on the internet and made some changes, but is not working.It browses the file but does not add the information on the spreadsheet on the table of the database. What I would like to achieve here is to browse the file, choose it, and after that import it to my access database. Since the code needed a form named just like the table I had created it on a spreadsheet format, but after the code runs it is set as a normal form and not datasheet. I believe it is important that the Excel file and the database are located in a shared folder of a shared drive. Can you please help me with this?

Code below:

Private Sub Command0_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim CustomerFile As String
    
' Set up the File Dialog.
On Error GoTo Import_Posting_Dates_Error
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
' Allow only one file to be selected
            .AllowMultiSelect = False

' Set the title of the dialog box.
            .title = "Please select your customer file."
    
' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "Excel Spreadsheets", "*.xls"
            .Filters.Add "Excel Spreadsheets", "*.xlsx"
                        
' Set the default location to the current user's desktop
            .InitialFileName = "P:\USBA"
  
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
       If .Show = True Then
' Loop through each file selected and add it to the list box.
          For Each varFile In .SelectedItems
             CustomerFile = varFile
          Next
       End If
    End With

' See error handler, what to do if operation is cancelled (2522)
    DoCmd.TransferSpreadsheet acImport, 10, _
    "PymtElc", CustomerFile, True

DoCmd.OpenForm "PymtElc"
   On Error GoTo 0
   Exit Sub

Import_Posting_Dates_Error:
    If Err.Number = 2522 Then ' 2522 missing argument (File Name) - i.e no file selected
        MsgBox "No file selected, operation cancelled " & "( " & Err.Number & " ).", vbInformation, "APP NAME"
    Else
        MsgBox Err.Number & " (" & Err.Description & ")", vbInformation, "APP NAME"
    End If
    
End Sub

 

[arnelgp]

does your excel file have the same structure (header against table field) as with your table?

 

[CJ_London]

I note you can choose a .xls or .xlsx file. Your transferspreadsheet is set to import spreadsheet type 10 - Microsoft Excel 2010 XML format.

Is this what you are importing?

Here is a list of the spreadsheet types

https://msdn.microsoft.com/en-us/library/office/ff196017.aspx

 

[Libre]

It doesn't look to me that you have the correct specification for the Transfer Spreadsheet method.
You have:

DoCmd.TransferSpreadsheet acImport, 10, _
"PymtElc", CustomerFile, True

It seems to me that you're missing a required argument - the path to the spreadsheet. I don't believe that just specifying "CustomerFile" is sufficient, even if it's in the same folder as your db. Just try it with the entire spec to the spreadsheet. And the path should include the server IP address, not a letter drive like D:/ for example.

Here is a snippet of code that works for me:

 Dim sPath, sFileName, sTableName As String
    sPath = "[URL="file://\\123.4.5.6 \share\DOCUMENTATION\xfer\up\"]\\123.4.5.6 \share\DOCUMENTATION\xfer\up\[/URL]"
    sFileName = "qryEcoPrt.xls"
    sTableName = "qryEcoSinglePrt"
 
    DoCmd.TransferSpreadsheet acExport, , sTableName, sPath & sFileName, True, ""

 

[mari_hitz]

Hi, thanks all for your quick replies! I will try to answer each one of them:

1) does your excel file have the same structure (header against table field) as with your table? - Yes, it does. Actually what I did is to import the format of the table of excel as to make sure it has the same structure.
2) I note you can choose a .xls or .xlsx file. Your transferspreadsheet is set to import spreadsheet type 10 - Microsoft Excel 2010 XML format.

Is this what you are importing? - Yes, I am trying to import that. The thing is that when I save the files on Microsoft 2010 format the browser that pop ups does not recognize the files saved under that format, only the ones saved by Excel 97-2003 format. I find this pretty odd but I don't know how to fix it.
I have tried to change what you have mentioned in my code and it worked only once and only imported 41 rows when the file has 193.

3) It doesn't look to me that you have the correct specification for the Transfer Spreadsheet method. - I had tried to change with your suggestion and it does not work. In addition it says "DoCmd.TransferSpreadsheet acExport" shouldn't say "acImport"? I've tried on both ways and did not work.

Any ideas on why the suggestions have not worked?

Thanks again!

 

[Libre]

Hi-
The main thing I was going on about was correctly specifying the spreadsheet's location. As far as acImport and acExport - I just picked an example from my own code - I should have found an Import example but it works either way.
Can you tell us exactly the error message you're getting? If it can't find the spreadsheet, that's one problem, indicating the incomplete specification I was talking about. If it does find it but can't import the data - that's an entirely different problem, sounding more like your column schema is different between the Access table and the spreadsheet. They have to have the same number of columns and same data types.
I saw what you wrote about importing the format from Excel - but I am used to this - it's a tricky method and I've worked with it for some years and experienced many many errors. But it would help if we had the exact error message.

EDIT
after reading your posts much more carefully, I realize you did answer some of what I and the others are asking.
It should work (obviously).
But let's get the exact error message - maybe it will ring a bell.