I want to transfer with TransferSpreadsheet an Excel table into Access table. The value of the column K of Excel table is formating from 6-12 digits and it may contain - or / or blank (456-456789 , 678/9876543 , 56 345 678976) . The contents of the cells with -./, , does not transfer to Access.If convert the column K in uppercase with Ucase() function all cells transfer correctly into Access , but only in Access/Excel 2003 NOT IN Access/Excel 2007.
Thanks.
Code:
Dim strSQL As String
Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim dbs As DAO.Database
Dim tel As String
Dim ii As Integer
Dim index As String
Dim K As String
Dim rng As Double
Set dbs = CurrentDb
Set xlApp = New Excel.Application
Set xlBk = xlApp.Workbooks.Open(tabla.xls)
Set xlSht = xlBk.Sheets(1)
xlApp.ScreenUpdating = False
xlApp.EnableEvents = False
xlApp.DisplayAlerts = False
rng= xlSht.UsedRange.Rows.Count
For ii = 2 To rng
index = CStr(ii)
K = "K" & index
xlSht.Range(K).Select
tel = xlSht.Range(K).value
xlSht.Range(K).value = UCase(tel)
Next
Set xlSht = Nothing
xlBk.Close
Set xlBk = Nothing
xlApp.Quit
Set xlApp = Nothing
strSQL CREATE TABLE nevTEST1F(F1 SINGLE, F2 DATE, F3 SINGLE, F4 TEXT, F5 TEXT, F6 TEXT, F7 TEXT, F8 SINGLE, F9t TEXT,F10 TEXT, Telefon TEXT,F12 date)"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "nevTest1", "tabla.xls", True