VBA Excel with SQL

[VMacedo]

Hi,
I have a worksheet in Excel with more of 65000 rows and I wanna export to the other worksheet using the VBA and SQL codes, but, when come to the row number 65656, the SQL stop the consult!
I'm using the version 2007 of Excel, theoretically, this version have more of 1 million of rows, so why stop the consult?
Someone can answer my question please?

And, if answer someone, can tell how make?

The code I used is:
Dim meubd As Database
Dim minhatabela As Recordset
Set meubd = OpenDatabase(ThisWorkbook.Path & "/" & ThisWorkbook.Name, False, False, "Excel 8.0")
Set minhatabela = meubd.OpenRecordset("SELECT Empresas, E5_TIPO, E5_NATUREZ, E5_BANCO, E5_AGENCIA, E5_CONTA, E5_BENEF, E5_TIPODOC, ValorReal, E5_CLIENTE, mês, Mês2 FROM [SE05$] WHERE (Empresas='B5 - SAFIRA') AND (E5_TIPO='NF ' OR E5_TIPO='RA ') AND (E5_NATUREZ>='1001001 ' AND E5_NATUREZ<='1001013 ' or E5_NATUREZ='1004015 ') AND (E5_TIPODOC='VL' OR E5_TIPODOC='ES' OR E5_TIPODOC='RA') AND (Mês2>=40695 AND Mês2<=40695 ) ORDER BY mês;")
Plan7.Cells.ClearContents
Plan7.Range("A2").CopyFromRecordset minhatabela
meubd.Close

(I'm brazilian, so somethings are in portuguese :])
Thanks!

 

[SkipVought]

hi,

Ignore the message. As long as you actually have 1M+ rows on your sheet, it will import all.

But is it really necessary to import all those rows?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VMacedo]

Man, my problem persist!
I don't know why, but, I did a little test and yet don't is correct.

 

[judgeh59]

I had sort of the same issue and I had to go back to basics. I'm using excel 2007 but I have defaulted excel so when I open a new file it is .xls, which is not 2007 format and therefore I don't get the 1M+ rows. So, I had to open a new file and then save my file as a .xlsx or .xlsm for macro files and this gave me my 1M+ rows. I actually needed the columns increased but it worked for both...

I hope this helps

Ernesto

Be Alert, America needs more lerts

 

[VMacedo]

Unfortunately has not yet.
The extension of my worksheet is .xlsm and the version of my Excel is 2007, don't have sense.

 

[judgeh59]

Just curious, you never really answered Skips' question. Do you actually have 1M+ rows when you do a Ctrl+down arrow on your new worksheet. Inquiring minds want to know...

Ernest

Be Alert, America needs more lerts

 

[VMacedo]

Judgeh59,
Answering the Skips question: Yes, I have 1048576 rows in a new sheet.
And, after some tests, I realized that when the SQL comes in the row number 65656, he stop, descards all the consult and restart from the row 65657.

 

[SkipVought]

If you have 1048576 rows of DATA in your resultset, then chances are very high that you are missing data from your query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VMacedo]

My problem happen when I update my consult. The consult filter datas of a base sheet. Initially this base sheet have 54700 rows, and the consult 528 rows. When I duplicate the rows of the base sheet (109400 rows) the consult teorically duplicate too, right? But the number of rows drops to 397.

 

[SkipVought]

I have no idea what that means! HOW is this 'duplication' occurring, or what are you doing to cause this occurrence?

What query(ies) are you using for each instance?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VMacedo]

Did you can see the worksheet and analyze? How I can send to you?