Auto Updating from excell book to book

[mts0829]

Hello Everyone

This is my question I have a excel book that I have created for like a master inventory list and as I download the inventory excel books from my vendors I wanted to know is there a way for my excel book to check the vendor's file and match the SKU for a product and then automatically pull the number in stock without me opening the vendor's file. the vendor file will be automatically downloaded and will be simply overwritten on a set schedule and then my master inventory would be uploaded to my website on a set schedule for a plugin to update the quantity on hand on a set schedule. I would love for this process to be automated so I can always be up to date on how many my vendors have on hand. I have tried to use Index-Match and Vlookup to do this but both require me to open the source file to have the data update. Could anyone give me an idea I have never worked with VBA so I would need help with that if that is the way I would have to do this

Thank you in advance for all the help.

 

[JazzSP8]

Hi

Welcome to the Forum

Your Master Inventory List should prompt to update the link values when you open the Workbook?

Or if it's something you just want to keep open then you can refresh the links from the Data Tab on > Edit Links > Refresh.

Or, if you'd rather make it a little easier then you can simply use this code and assign it to a button;

Sub UpdateInventory()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
End Sub

Does that get you any closer to your end goal?

 

[mts0829]

will this code auto update without me opening the file and i have never messed with VBA how would i make it a button?

 

[JazzSP8]

It'll update the links to the other workbooks without needing to open them, yeah.

First of all you'll need access to the Developer Tab;

Click "File" tab.

Choose Options.

Choose Customize Ribbon.

Under Customize the Ribbon and under Main Tabs, tick the Developer check box.

You should now see the Developer Tab with the rest of them.

Click "Developer" and then "Visual Basic", this will open the VBE (Visual Basic Editor)

Once there you should see a Project Explorer window on the left hand side. At the top level you'll see the name of your workbook, you may need to press the "+" button to expand it.

Once expanded you'll see your worksheets listed, double click on the main one you want to use for the button.

You can then copy and paste that code into the right hand window.

(At this point you can close the VBE if you like.)

Select the sheet where you want the button.

Click "Insert" - The first option should be for "Form Controls", the first row on the left should be "Button", click this.

Draw your button by pressing the left mouse button and dragging.

Once you have your button then it'll ask you to assign the Macro, select the one you just pasted in.

And there you have your button

 

[JazzSP8]

^^ Those instructions are for Excel 2013, I think it'll be the same for other versions past 2003 but if not post back

 

[mts0829]

i have excel 365 would this be the same

 

[JazzSP8]

I don't know unfortunately, I've no access to 365 - However if you follow the instructions and it's not then you can just Google how to add the Developer Tab.

The rest I would imagine is the same.