Hi guys,
I'm learning a lot from this forum and thanks for everyone willing to help me. I've searched everywhere, but couldn't find a right solution for my case. The situation is so that i have a output excel file from one the company's program. What it gives you is "Working file" with items and quantities needed for the project, along with item names and item numbers that mathes the one in ERP system ( this is "working file" in dropbox link attached).
Other excel files that i need to get into is output from our ERP system with actual item numbers, desbricptions and most importantly- PRICES ( these are "2017.01.02.xlsx","2017.01.03/xlsx", etc., they will be named like this and updated every night).
Steps will be like that:
1. Every night excel with information of prices will be placed on the disc with name like "2017.01.02.xlsx" or 2017.01.03.xlsx" etc...( let's say location will be "C:\pricelist")
2. When project program used it will calculate items and quantities needed, places them all in "Working file" and gives this file to user.
3. AT THE MOMENT WHEN USER OPENS THE FILE, VBA uses Vlookup to go to file "2017.01.02xlsx" in server, searches for item numbers ( column A) and adds prices to all items in column E in "Working file" ( the file with prices named "2017.01.02.xlsx" or similar can not be held open as many users use the same program, hence the same price file).
4. That is it, when other day comes and user uses project program again, he gets new "Working file" with new items and quantities in it and again at the opening phase VBA should go into server find the file that matches the day in "Working file" (let's say one day later "2017.01.03.xlsx") , Vlookup prices and place it again to "Working file" E column, this has to match item number ofcourse.
One more chalange is that "Working file" is the same all the time, i mean formulas do not change in it, only formula "TODAY()" gives the new date that should direct VBA to other excel with prices.
https://www.dropbox.com/sh/kz0eqo1wrmooopv/AACGRjdJUVCGFBA2yBKTOuARa?dl=0
Hope somebody can help my by writing VBA for "Working file". Much appreciated.
Darpo
I'm learning a lot from this forum and thanks for everyone willing to help me. I've searched everywhere, but couldn't find a right solution for my case. The situation is so that i have a output excel file from one the company's program. What it gives you is "Working file" with items and quantities needed for the project, along with item names and item numbers that mathes the one in ERP system ( this is "working file" in dropbox link attached).
Other excel files that i need to get into is output from our ERP system with actual item numbers, desbricptions and most importantly- PRICES ( these are "2017.01.02.xlsx","2017.01.03/xlsx", etc., they will be named like this and updated every night).
Steps will be like that:
1. Every night excel with information of prices will be placed on the disc with name like "2017.01.02.xlsx" or 2017.01.03.xlsx" etc...( let's say location will be "C:\pricelist")
2. When project program used it will calculate items and quantities needed, places them all in "Working file" and gives this file to user.
3. AT THE MOMENT WHEN USER OPENS THE FILE, VBA uses Vlookup to go to file "2017.01.02xlsx" in server, searches for item numbers ( column A) and adds prices to all items in column E in "Working file" ( the file with prices named "2017.01.02.xlsx" or similar can not be held open as many users use the same program, hence the same price file).
4. That is it, when other day comes and user uses project program again, he gets new "Working file" with new items and quantities in it and again at the opening phase VBA should go into server find the file that matches the day in "Working file" (let's say one day later "2017.01.03.xlsx") , Vlookup prices and place it again to "Working file" E column, this has to match item number ofcourse.
One more chalange is that "Working file" is the same all the time, i mean formulas do not change in it, only formula "TODAY()" gives the new date that should direct VBA to other excel with prices.
https://www.dropbox.com/sh/kz0eqo1wrmooopv/AACGRjdJUVCGFBA2yBKTOuARa?dl=0
Hope somebody can help my by writing VBA for "Working file". Much appreciated.
Darpo
