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 ?


"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
.

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" wrote in message
...
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
.

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


--

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


"Indrajit" wrote in message ...
Hi Ron,

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

Any suggestions?

Regards,
Indrajit