DoCmd.TransferSpreadsheet error

[raykon]

When i use DoCmd.TransferSpreadsheet command for getting data from an excel file (downloaded from internet and didn't open yet) to the my table in access file, it takes every field in the excel file except the field TALEP_NEDEN_ADI. Only this column is empty.

But when i opened th excel file once and close then try to execute the command, it works well.

What is the problem about this column?
Sample files attached.

 

[marlan]

Hi,

I've found Excel has some kind of default definition of the range containing the data. It is not definitely accurate, I've sometimes received additional field F1 to F20! ...

Use the [Range] parameter to define the range you need to import, in your code it is the next one after [HasFieldNames = true], the Value should be something like "A1:AT7".

Good luck!

 

[raykon]

Hi,

I've found Excel has some kind of default definition of the range containing the data. It is not definitely accurate, I've sometimes received additional field F1 to F20! ...

Use the [Range] parameter to define the range you need to import, in your code it is the next one after [HasFieldNames = true], the Value should be something like "A1:AT7".

Good luck!

Thank you for your suggestion but it doesn't work.

 

[Fuse3k]

Try using acSpreadsheetTypeExcel12Xml instead of acSpreadsheetTypeExcel9?

 

[marlan]

Thank you for your suggestion but it doesn't work.

Hi,

Do you want to be more specific, what doesn't work?
Could you paste here the line of code?

 

[marlan]

Hi,

Do you want to be more specific, what doesn't work?
Could you paste here the line of code?

Well, I tested it, it doesn't work. Sorry...
I opened the Excel and saved it ("Save AS") in that same format (xls 2003), and it worked! I've seen that before with Excel files coming from the internet.

Before running TransferSpreadsheet(), use Excel.Application object to open the Excel WorkBook (file), SaveAs the current name and format, and close it.

Please let me know it work...

ATB!

 

[marlan]

Here is code I once wrote for this:

'Must have a reference to Microsoft excel 11.0 object library
'    (Tools->Refernces->Microsoft excel 11.0 object library )

Private Sub ConvertExcel(strFilePath As String, Optional strSheetName As String = "")
On Error GoTo Err_ConvertExcel
    
    Dim ErrNum As Long, FileConverted As Boolean
    ErrNum = 0
    FileConverted = False
    
    'ApXL is of type of "Excel.Application"
    Dim ApXL As Object, xlWBk As Object
    'Get Excel file (WorkBook)
    Set ApXL = GetExcelObject
    ApXL.Workbooks.Open (strFilePath)
    
    If CInt(ApXL.Version) > 11 Then 
        ApXL.Application.ActiveWorkbook.SaveAs strFilePath & "Temp", 56
    Else
        ApXL.Application.ActiveWorkbook.SaveAs strFilePath & "Temp", xlNormal
    End If
    FileConverted = True
    
Exit_ConvertExcel:
On Error GoTo 0
    If Not ApXL.Application.ActiveWorkbook Is Nothing Then _
        ApXL.Application.ActiveWorkbook.Close
    Set ApXL = Nothing
    If FileConverted Then _
        CopyFile strFilePath & "Temp", strFilePath, True
    DeleteFile strFilePath & "Temp"
    If ErrNum <> 0 Then Err.Raise ErrNum
    
    Exit Sub
    
Err_ConvertExcel:
    ErrNum = Err.Number
    Resume Exit_ConvertExcel
End Sub

Public Function GetExcelObject() As Object
    On Error Resume Next
    Set GetExcelObject = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set GetExcelObject = CreateObject("Excel.Application")
    End If
End Function

Public Function FileExists(FileName As String) As Boolean
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    FileExists = fso.FileExists(FileName)
End Function

Public Sub DeleteFile(FileName As String)
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If FileExists(FileName) Then fso.DeleteFile FileName, True
End Sub

Public Sub CopyFile(Source As String, Destination As String, Optional force As Boolean = False)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    If FileExists(Source) Then
        'If force Then DeleteFile Destination
        fso.CopyFile Source, Destination, force
    End If
End Sub

 

[raykon]

Fuse3k acSpreadsheetTypeExcel12Xml doesn't work too.

Marlan, yes when you open and save it again, it work. Your solution will work but i want learn why this is happening actually and maybe find a simplier solution.

 

[marlan]

Hi,

A web system doesn't use an Excel - COM object to create an excel file, it 'mocks' one, knowing the desired structure. When you have Excel open and save the file, it is recreated by Excel - COM object.

this code I've pasted here actually works on a product, with the same problem: bank statement is exported to excel and downloaded by the user from bank website, and Access uses the attached code to fix the format before importing.

As for the solution being simple: it is simple, I've just added:
1. A condition, so code supports versions Office later than 2003, still converting to xls file.
2. Some file system functions I have, for cleaner work...

this code work in client system.

ATB!

 

[raykon]

I understand, thank you!

 

[marlan]

Glad I could help