import excel file to access 2003 yes, access 2007 no

[stapik]

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.

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

[rpeare]

The last I read or knew it was impossible to use import/export specs in VB code within Access 2007+. Why Microsoft in it's infinite wisdom decided to take this functionality away I do not know.

See this:
http://office-watch.com/t/n.aspx?art...=943&zoneid=30

there may be a patch to fix this functionality if you find one let us know please because this is a common problem

[boblarson]

I'm looking at this code for the table creation and from what I'm seeing, you are missing a quote and an equals sign(in red), and you are creating a table named nevTEST1F and then trying to import to nevTest1 instead. Is your code copied and pasted? If so, then it should be generating some errors for you.

"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.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "nevTest1", "tabla.xls", True

[boblarson]

The last I read or knew it was impossible to use import/export specs in VB code within Access 2007+. Why Microsoft in it's infinite wisdom decided to take this functionality away I do not know.

See this:
http://office-watch.com/t/n.aspx?art...=943&zoneid=30

there may be a patch to fix this functionality if you find one let us know please because this is a common problem

I'm not seeing any use of an import or export spec here. Where are you seeing it?

[rpeare]

You're absolutely correct in my rush to castigate the newer versions of access I lost track that it was a transferspreadsheet. Apologies for the confusion.

[boblarson]

You're absolutely correct in my rush to castigate the newer versions of access I lost track that it was a transferspreadsheet. Apologies for the confusion.

No problem. I was just wondering if I had my glasses on correctly as I have missed things like that in the past.

[stapik]

I'm looking at this code for the table creation and from what I'm seeing, you are missing a quote and an equals sign(in red), and you are creating a table named nevTEST1F and then trying to import to nevTest1 instead. Is your code copied and pasted? If so, then it should be generating some errors for you.

"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.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "nevTest1", "tabla.xls", True

Hello Bob,

I don't use copy and paste, in my code I am creating a table named nevTest1.The TransferSpreadsheet works no error is generating.The problem : The column K of Excel table contains: 23454323467, 5678934567, 98765432112, 456-456789 , 678/9876543 , 56 345 678976 etc. and the contents of the cells with -./, , does not transfer to Access.
If I convert the column K in uppercase with Ucase() function all cells transfer correctly into Access , but only in Accessl 2003 NOT IN Access 2007.

Thanks.

[rpeare]

Why would you use a UCASE function on a telephone number?

Also did you fix the things bob pointed out namely your create table code, you posted this:

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)"

when it should be

strSQL = "CREATE TABLE nevTEST1(F1 SINGLE, F2 DATE, F3 SINGLE, F4 TEXT, F5  TEXT, F6 TEXT, F7 TEXT, F8 SINGLE, F9t TEXT,F10  TEXT, Telefon TEXT,F12  date)"

because your transferspreadsheet code says this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "nevTest1", "tabla.xls", True

you are transferring to table NEVTEST1 with your transferspreadsheet but you were creating table NEVTEST1F with your create table statement.

[stapik]

Why would you use a UCASE function on a telephone number?

Also did you fix the things bob pointed out namely your create table code, you posted this:

when it should be

strSQL = "CREATE TABLE nevTEST1(F1 SINGLE, F2 DATE, F3 SINGLE, F4 TEXT, F5  TEXT, F6 TEXT, F7 TEXT, F8 SINGLE, F9t TEXT,F10  TEXT, Telefon TEXT,F12  date)"

because your transferspreadsheet code says this

you are transferring to table NEVTEST1 with your transferspreadsheet but you were creating table NEVTEST1F with your create table statement.

Hello rpeare,

I am sorry, I made this error , nevTEST1F , when I wrote the message. In my program the CREATE command is with nevTEST1.

All cells (columns) are tranfered correct, only the cells from column K that contents /,-, etc. aren't transfer.

Not NEVTEST1 / NEVTEST1F is the problem. I was wrong when I transcribe the code. Sorry!

[rpeare]

Access usually blanks out a field when it tries to perform a numeric function on a text field or something of that nature. Because whole records from your system are not being dropped from the append I have to believe there is some disconnect in data type between your excel spreadsheet and your access table, every test I've run on data here my data gets imported correctly. Have you tried changing the data type on your excel spreadsheet for the phone number column to text as well just to double check?