Macro to import data from another workbook

[Nirmall]

Hi all,

i am creating data workbooks for each product with its technical specifications like weight of the product and different materials used, and based on the specifications, the price of the product changes.

Now if i put all the product details in one workbook, the workbook becomes more than 100 mb, so i have to create new workbooks.

Now since the rates of product material keeps on changing i need to update all the sheets to get the value of each product right.

Is it possible where i create a master worksheet and can call for product details from other workbooks, based on the name of the product, without opening the source workbook.

Please note the technical details of the product dont change and all products have unique codes.

Any help on this to get me started will be a huge time saver.

Regards

[Tinbendr]

1. You can use vlookup to do that. Vlookup can look in a separate workbook. Just remember that if you separate the two after that, you'll have broken links. (You can copy and paste before the separation to preserve values.)

2. You can write a macro to import the values.

[Nirmall]

Can u please help with the macro??

[Tinbendr]

Please upload a sample workbook WITH sample data.

[Nirmall]

Have attached the master workbook, where i want the data based on the product code, and the data sheet containing details of the product.
For ease of understanding i have colored the data and the place in master where i want the data.Please ignore cells with yellow color as they are not to be changed

[Tinbendr]

Ok, explain how you want this to work.

You select a value in B3. (I assume the product codes are already loaded.) Once you exit the cell, the colored cells (non Yellow) are populated from the data sheet?

And do you want to bring the whole group in, or just a single entry?

[Nirmall]

Hi,apologies for the delay in reply, my net was down,

want to get the whole group based on the product code, also will i have to save all data sheets in one folder for the data to be fetched ??

For your reference am sending u an output master, which shows the data imported from the data file.

Regards

[Tinbendr]

OK Here is the code. I'll upload the master file as well.

You'll need to edit the path of the data.xlsx location.

In the Master!Data sheet code page.

Please Login or Register  to view this content.

Add a module and insert this.

Please Login or Register  to view this content.

[Nirmall]

I dont want my data in the yellow cells to clear as they have formulas

[Tinbendr]

Hmm. So they do.
Change to this.

Please Login or Register  to view this content.

[Nirmall]

Thanks for the update, i will make the changes and revert back,

one more thing is it not possible to have the path set for the data using a macro.

Meaning i need not go to the vba code, but by running the macro i can specify the path of the data file,

this should be done whenever i want to change the path of the data folder, if i dont change it, then the path i lasted selected will remain.

[Tinbendr]

I can change it to where it ASKS where the file is. But it will do this every time.

Maybe we could set the path the first time the macro runs. We can store the path/filename somewhere. Then whenever the macros runs after that, we just check the location to see if the file is still there. If it is, we continue on, else it stops and asks for it.

[Nirmall]

Yup, asking for the file every time does not make sense,

your second option should do the trick, where first up it will ask for a path, and next time when the macro is run

and it does not get the path, it will ask for the path.

But again would want the macro to allow us to decide the path without going into the vba code mode.

[Tinbendr]

Ok, try this.

If you NEED to edit the path for some reason, I store the path in the Subject field of the document properties. (File->Info-(scroll to bottom -Show All Properties) Just clear out the field and rerun the macro. It should ask again the the path to the file.

The only thing about this is that it doesn't prevent someone from selecting the wrong file. In that case, the macro will likely halt on an error.

If you want to add that protection, I suggest we add a phrase to the subject field of the dataset file. We can check for that value to discern if the correct dataset was selected.

Please Login or Register  to view this content.

[Nirmall]

Your present code has that protection??

[Tinbendr]

It does not. We'll have to add that if you like.

[Nirmall]

It would be great if u can add that too.

[Tinbendr]

OK, first, are you the author of the dataset? If not, can you get the author to put a specific word in the Subject field of the file?

What keyword/token/password do you want to use for the dataset.xlsx file? It doesn't have to be anything sophisticated. But it will be hard coded in the code. So, if you decide to change it in the dataset, you'll have to update it in the code as well. And anyone who has a little code experience will be able to change it. But then, this is not like password protection. This is just a general test for the correct file.

[Nirmall]

i am the author, u can put in %NirmALL99

[Tinbendr]

Be sure to add the keyword to the dataset subject field.

Please Login or Register  to view this content.

[Nirmall]

I will be having 1 workbook for each product code, so i have to specify the folder where the data workbooks are stored????

[Nirmall]

Hi, ur old code works fine in your masterdata, but cant figure out where i put my path for data in the new vba code??

[Tinbendr]

You will just have to navigate each one the first time.

[Tinbendr]

Hi, ur old code works fine in your masterdata, but cant figure out where i put my path for data in the new vba code??

If you're talking about the data file keyword, that's all you have to key in manually the first time.

Open the data file. Select File-Info (scroll to bottom on right) select all Properties- Type in the keyword (%NirmALL99) in the Subject field. Save.

Redistribute the data file along with new Master. When the new Master is opened, the user will have to navigate to the data file, which will include the keyword.

[Nirmall]

I did, have attached a screen shot of whats happening

[Nirmall]

Still not working, can u please help

[Tinbendr]

OK, you will have to delete the path from the Master file Subject line until you can read the data file for the first time after adding keyword.

[Nirmall]

not working can u please edit the code and send it for the master file?

[Tinbendr]

Here ya go.

[Nirmall]

Sorry but still am getting the same message cant we do away with the password?

[Tinbendr]

It's Post #14.

[Nirmall]

You have been very patient, thanks a ton,

1 last request,

can u send me a screenshot for this

If you NEED to edit the path for some reason, I store the path in the Subject field of the document properties. (File->Info-(scroll to bottom -Show All Properties) Just clear out the field and rerun the macro. It should ask again the the path to the file.

[Nirmall]

Got it amillion thanks, u are jus too good

[Tinbendr]

Here's a snapshot for anyone else.
!Untitled.png