Code to Import Data from Workbook to Workbook- Excel 2010

Hi Folks
The attached Workbook 'CDR' requires Code to import data from another Workbook titled 'UKDR'.

I have placed notes in CDR to show my requirement.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
VBA can of course do it, but it is also possible to do with formulas.
See workbook.
The used path should automatically adjust, as long as both workbooks are in the same folder.
It will also work with the UKDR workbook closed, but faster when open.
I have set it to use 200 rows in UKDR.
If there are many rows in UKDR, calculation speed can be improved by making a column for the row specification, so the row search in UKDR is only done once per row in CDR.

The data in UKDR is initially copied and pasted from a csv file.
Then it would be much better to import the csv file into a sheet in the CDR workbook, and then reference the imported range in the formulas.
Excel automatically adjust ranges in formulas when new data is imported.

Upload a csv file and I will set up the import and the formulas, if you want that.
PWM16Author Commented:
Hi Ejgil
Thanks for the reply.
Given the data in CDR Cols A : BN is updated daily ie cleared and new data brought in I think it will be okay to use formulas as you have done here.

I doubt the maximum number of rows required would ever exceed 1400 so it is not a huge workload for the Workbook.

I like your idea of placing the UKDR sheet in the CDR file itself so I will place a sheet and the data in the CDR file and upload it again. The data in the UKDR sheet will also be cleared and updated daily.

Ejgil HedegaardCommented:
The data on the UKDR sheet is not a csv import.
Use the text file import wizard to import the data from the csv file.
To update, select a cell somewhere in the data area, right click and select update.
If the csv file name is always the same, you can update on file open, right click and use settings to set when to update, ask for file name etc.
Change the range references in the formulas on the AAA ALL sheet to UKDR data from row 2 to the last row in the data.
Then Excel will automatically change the reference to the actual data range at update.

If CDR data A:BN also comes from a csv file, then import that the same way, and change the settings to update formulas in columns to the right.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PWM16Author Commented:
Many thanks Ejgil.

The advice and formulas provided will enable me to continue with my project.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.