Excel 2007: Copy from recordset not working properly

  • Thread starter Thread starter Indrajit
  • Start date Start date
I

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
 
I can't check this , but maybe your extended properties should refer to a
later version of excel than 8.0 ?
 
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


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


Indrajit said:
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
.
Click to expand...
Click to expand...
 
Hi Ron,

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

Any suggestions?

Regards,
Indrajit
 
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
 
Back
Top