Reading data from Excel spreadsheet

[Madiba1]

Hi,

I am writing a function to read data from excel spreadsheet (Ms excel 2003) and write the values to access table. I am using DoCmd.TransferSpreadsheet.

The statement is able to identify the columns(table and spreadsheet) but the data is not getting transferred to the table.

Any help will be appreciated.

Thanks

Madiba

 

[kjv1611]

Can you post your exact DoCmd.Transfer... as you have it in your code?

 

[NvrSaDi]

'This is for Access 2000
'TransferSpreadhsheet function appends data to table with default F1, F2, F3, etc. field names.

Dim strsql As String
'Delete data in old table
strsql = "DELETE IBSImport.* " _
& "FROM IBSImport;"

DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'Function appends data from range name myexport to IBSImport table in Access
DoCmd.TransferSpreadsheet acImport, 8, "IBSImport", Me.STKSFSME, False, "myexport"

'Maketable query then changes field names
strsql = "SELECT IBSImport.F1 AS PropID, IBSImport.F2 AS PrAddr, IBSImport.F3 AS [Gross SF], IBSImport.F4 AS [Total SF], IBSImport.F5 AS [Leased SF], IBSImport.F6 AS [Vacant SF], IBSImport.F7 AS [Vacant %], IBSImport.F8 AS [Non OIR SF], IBSImport.F9 AS [Calc Total], IBSImport.F10 AS Variance " _
& "INTO stksfsme " _
& "FROM IBSImport;"

DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

Hope this helps

 

[Madiba1]

thanks, I used part of code below and it worked

'Open connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\test.xls;" & _
"Extended Properties=Excel 8.0"
Set cn = New ADODB.Connection
cn.Open strCn

Have a good day

Madiba