Avatar of PWM16
PWM16
Flag for Australia asked on

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.

Thanks
PWM
CDR.xlsm
UKDR.xlsx
Microsoft ExcelVBA

Avatar of undefined
Last Comment
PWM16

8/22/2022 - Mon
Ejgil Hedegaard

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.

But
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.
CDR.xlsm
PWM16

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

Phil
CDR--1-.xlsm
ASKER CERTIFIED SOLUTION
Ejgil Hedegaard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PWM16

ASKER
Many thanks Ejgil.

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

Cheers
Phil
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy