Excel 2007: Copy from recordset not working properly

[Indrajit]

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

 

[Patrick Molloy]

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

 

[Ron de Bruin]

There is another connection string for 2007

I use this

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"

In the code on this page
http://www.rondebruin.nl/ado.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

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

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
.

 

[Indrajit]

Hi Ron,

I tried using your string, but it throws up an error saying "Could not
find installable ISAM"!

Any suggestions?

Regards,
Indrajit

 

[Ron de Bruin]

Hi Indrajit

Is the code in my example workbook working Correct ?

Do you use Late or Early binding ?
Maybe you must set a reference in the VBA editor