get data from an excel and import in access

Question

I'm tring to import some excel rows in a recordset using vba. I use the following connection string

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\Luca\Desktop\barcode ean.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

with a ADODB.Command and I've used this query to get data

Select * from [Foglio1$]

now I should see 1500 rows in my recordset but it contains only 88 rows

How I can get all records??

score 0 · Accepted Answer · answered Nov 22 '13 at 11:19

Instead of ADODB.Command, rather using an ADODB.Connection to test as this:

Sub sofMain20141472Access()

  Dim cnn, rst

  On Error GoTo ErrHandler

  Set cnn = CreateObject("ADODB.Connection")
  cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" _
    & ";Data Source=\\vmware-host\Shared Folders\Luca\Desktop\barcode ean.xlsx" _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"""
  cnn.Open

'
  Set rst = cnn.Execute("SELECT * FROM [Foglio1$];")

'
' do stuffs on Recordset rst...
'
' ...
'
' close ADO objects:
'
  rst.Close
  Set rst = Nothing

  cnn.Close
  Set cnn = Nothing

  Exit Sub
ErrHandler:
  MsgBox Err.Description
  Set cnn = Nothing

End Sub

get data from an excel and import in access

1 Answers1