I can't check this , but maybe your extended properties should refer to a
later version of excel than 8.0 ?


"Indrajit" wrote:

Hi All,

I am facing a peculiar problem in Excel 2007. I have sheet with approx
76,000 rows of data and around 30 columns. I am trying to query the
sheet and copy data to another sheet. Strangely every time, the only
10,774 rows of data get copied. Just to check if my code is correct, I
opened a new workbook, created a coloumn with numbers running from 1
to 76000 and entered the following macro:

__________________________________________________ ________________________________
Sub pull()

Application.ScreenUpdating = False
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim Sheetname As String
Set cn = New ADODB.Connection

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" &
_
"Extended Properties=Excel 8.0;"
.Open
End With
Sheetname = "Sheet1"

querystr = "Select * from [" & Sheetname & "$] "

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = querystr
cmd.CommandType = adCmdText
cmd.CommandTimeout = 0

Set rs = cmd.Execute()

Sheets("Sheet2").Range("A2").CopyFromRecordset rs
rs.Close

End Sub
__________________________________________________ ________________________________

When I ran the macro, it only copied 10,463 rows!!! Any idea why this
is happening and what is the workaround for this?

Thanks in advance,
Indrajit
.