Ali has attended:
Excel Intermediate course
Row capacity in Excel
I have a list of all the postcodes in the United Kingdom, and they are longer than the amount of rows in an Excel spreadsheet.
I have convereted the file to Microsoft Access so I can access it there, but to copy the postcodes from Access to differing Excel spreadsheets, will take ages as Access only allows me to copy and paste 65k each time.
Is there a way I can copy and paste from Access to differing Excel sheets in one go?
RE: Row capacity in Excel
Hi Ali,
Thank you for the forum question.
You have many options to do what you want. First of all Excel is not limited to 1048576 rows (Records). In Excel 2010 and newer versions we can handle any number of records by using PowerPivot. This is an amazing new tool which can handle any number of data sources and any number of records. If you want to have a look at this option you will need to download it from Microsoft's website. It is integrated in Excel 2013 and Excel 2016.
Another solution can be to link Excel to the Access database. You can also query the Access database from Excel to tell Access which postcodes you want in the different worksheets.
You will find the tools you need on the Data tab in the Get External Data group.
Copy and paste a huge amount of data is never a solution I would use.
I hope this can help you to do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Row capacity in Excel
Dear Jens,
Thank you for your reply. However, when I attempt to upload the Access spreadsheet onto PowerPivot, it does not allow me to. It does not find the sheet that the postcodes are on.
Furthermore, on Access, the sheet that has all of the postcodes on is under a different icon than the choices i.e. Table 1 or Table 2.
RE: Row capacity in Excel
Hi Ali,
Have you connected the database to the PowerPivot window?
It is difficult for me to say what is wrong I have never had any problems connecting PowerPivot to external databases.
When you from the PowerPivot window click onget external data from Access you need to select the database and a list with all the tables will be shown. Select the tables you want to import to the PowerPivot window and click finish.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Row capacity in Excel
Dear Jens,
The problem I am finding is that it is not finding the table which I wish to select from my access spreadsheet..
RE: Row capacity in Excel
Dear Ali,
I do not know why Excel cannot find the table in Access. If you can forward the database to me I can have a look at the data.
send it to:
info@stl-training.co.uk
Please add my name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
