upload to excell

[mujahid]

hai there,



im new to thid forum as well to Access.

i needed to upload 75k records to excell but 65k only done.

how we can select the remaining 15k records quickly coz this is something i need to repeat.

need a help ASAP....plz...

[alansidman]

What version of Excel are you using? How are you uploading to Excel? vba? Wizard? Cut and Paste? More details needed to supply correct solution.

[Dal Jeanis]

65K sounds suspiciously like the pre-2007 Excel 64K limit for rows (65535).

If you have Excel 2007 or later, then it may be that your export routine is using obsolete references to earlier versions of the Office products.

[mujahid]

Hai there... many thanks for the quick reply.

To: alansidman;



Im using the wizard to export the records. If possible let me know the codes and instruction to do the same using vba. I'll try my self first and practice it in the DB.

To: Dal Jeanis

You are correct and to be exact it is 65534 records copied. remaining 15k records to be copied/exported to excel.
At least let me know a shot cut to selecting them quick rather drag & select.

"it may be that your export routine is using obsolete references to earlier versions of the office products"
Referring above; how to change the settings for my export routine to use new references; i hope which allow to upload more than 75k.

[mujahid]

i found below info in another thread but i worried whether i may loose any data that any cell contains???

TheReb
Novice Windows 7 32bit Access 2010 32bit

Join DateAug 2013Posts3

Thanks for the reply. I actually found my answer on another similar thread. The problem was caused by checking the "Export Data with Formatting and Layout" box. Once I unchecked that, everything exported fine. Nice to have a simple solution.

[Dal Jeanis]

Basically, the data would get there, but there wouldn't be any formatting and layout data. But then, Access isn't usually storing any format or layout info, that I know of, so you should be good.

[mujahid]

Hai Dal Jeanis,

I could upload all 75k Access records at once and i've to do some formatting to make the table look nice. That's solves my major issue.

But i'm much happy to know the answer for my query which i mentioned again below.

"it may be that your export routine is using obsolete references to earlier versions of the office products"

Referring above; how to change the settings for my export routine to use new references; i hope which allow to upload more than 75k.

[Dal Jeanis]

If you are using the Access 2010 export wizard or the Excel 2010 import wizard, then that wasn't the problem. That problem would only have been if you were using an Access VBA routine that had been around from a prior version.

... unless the database is an .mdb file, in which case I'm not sure what would happen. If so, try converting it to .accdb and then see if the wizard still has the problem.

[mujahid]

Using Access 2010 export wizard; i exported 75K records from .accdb file at once to Excel 2010 successfully but ofcourse without any formatting.

I think the Office 2010 package has set the limit max 65534 can only be exported at once with Formatting.

Am i correct above? If im wrong pls correct me.

[Dal Jeanis]

Yes. That's what I think happened.

[mujahid]

Many thanks for sharing your expertise knowledge to solve my problem...

Bye.

[alansidman]

Look at this link if you are interested in adding formatting to your excel sheet after exporting. It demos some VBA code to get you started.

http://www.btabdevelopment.com/ts/de...aspx?PageId=49