How to extract data from a daily report and match a unique tag number to a master list?

Hi all,

I have been trying to search on the internet with regards to trying to write a macro for a project of mine but I just can't seem to find it.

I sincerely seek your kind help in assisting me.

Basically, I have a workbook that keys in a daily report of a unique tag number. I would like to run a macro to pull data from that workbook to another workbook (master list) that will match the equipment tag number and record the problem so that I don't have to manually key it in. That way, the master list workbook will be able to track the historical trend of problems for the unique tag number, recording the date of when it happen and the problem that is being reported.

Is it possible to be done?

The file for the report is name 'SBR Oct to Dec 2013', tag number to be matched with master list in another workbook would be column B and data to be copied to master list after identifying the tag number would be column C and D.

The file for the master list is name 'SBR Historical Trend (Master)' in which the raw data has been seperated into different tabs. the column historical trend is meant to record the dates of which the problem occured and record the data for both column C & D of the report in the previous workbook.

Any ideas on how i should create the macros?

I have attached the file for your reference.

Thanks.


Alf

Save the following code to an excel add-in or workbook and run LogNewTags.

Upate constant cWBMasterDataPath for file path of master file as required
Macro will verify if files are accessable and not read-only etc to enable save of updates etc.
To ensure new records only get captured once, a status for each records is added to the daily file in column F.
And invalid equipment tags not matched to master file will be flagged in the daily file accordingly.

Good luck.

Please Login or Register  to view this content.

Hi wotadude,

thank you so so much for responding and assisting with my project!

However, i am quite a beginner with excel so i was wondering can you kindly guide me to how i should input the code and which workbook to input the code at? the master workbook?
Also, i was wondering if it is possible to create a button of which i can run the macro at the end of the day only when the daily report is completely updated. that way, i can put out the latest information required.

Thanks so so much in advance once again and i really appreicate your kind help.

regards,
Alf

Hi wotadude,

When i run the code in the daily report, and i test run it by inputing some data on 29 Oct, there's an error.

run time error '13'

For lMasterRwIdx = LBound(vMasterData, 2) To UBound(vMasterData, 2)

it can't seem to update.

Do you have any idea why?

Alf

As I understand it the daily file is used by various people and going by the file name i expecy you create a new daily file every quarter. And if you are the only user of the master file (or the only one that should transfer daily data to it) I suggest the macro could either go into an add-in that only you have installed and use, or add the macro to the master workbook (in which case) I would suggest some of the macro will need to be changed.
So that when run will retrive the daily data workbook, rather than the master file.
Please advise on the above, and perhaps submit a sample file that you are getting the error with. And I will fix that error and add macro to appropriate workbook with button etc.

Hi Wotadude,

Sorry for the delayed reply because i am currently out station and the connectivity is limited.

Basically, I am the only user of the master file and the daily file is a shared file whereby everyone enters the daily at the end of the day so it is just one file. I guess the macro should go in the master workbook that way it doesn't always create a new file after it has been compiled. Yea, that sounded better where by the run would retrieve the daily data workbook rather than the master file.

I can't upload a sample file because I dont have the data in this laptop but basically, try inputting the equipment tag no, input 'test' in both trouble and work carried out column and run it. Essentially, it should put out the data and have it entered into the master file but for some reason it can't. An error text will pop out and a empty copy of the master file will be created in the same folder where both files are located.

Yea, a button would be great!

Many thanks once again for your kind assistance. I really really appreciate it!

Take care,
Alf

I have added the updated code to the Master workbook attached here.
Added a menu sheet with button to import daily data.
I tested it and could not reproduce the error you got.
See how you go with this.

Dear Wotadude,

Thanks so much for that button. It's really what I had in mind.

I have attached a printscreen for your reference. Basically, i opened the SBR daily report file and tried to key in a data for 1st Nov 2013, please kindly see attached and tried to run it to see if it would appear in the master file but the error message came up. Be it windows or mac, it shows the same error message.

Can you kindly teach me how you managed to import the data over. Am i doing anything wrong here? I saw your sample results in the master file and it was great. Should i save the daily report file first before running the master file?

Thanks once again!


Regards,
Alf

The screenshot does not give detail of the casue for error.
Hit debug to display highlighted portion of code casuing the error.
I have imported your latest daily file with no issues.
There should be no need to save the daily file before macro run. Both files will save automatically on completion as required.

Hi Wotadude,

It works already! I think it's probably different version of EXCEL that causes the problem.
It works perfectly! THANK YOU so much!

A few questions to ask though.
1. Say next year, i would be creating a new workbook for the new quarter, i just have to change the destination of the daily report file or must i do more?
2. As the master file will be importing the data from the daily report, can it be done in a way that it imports the date but transfer the trouble and work carried out into a comments tab? that way the master file look neater with just the date and when i want to see what happens, i can view the comments. Will that be possible?

Many many thanks once again.

Regards,
Alf

as both workbooks are open and saved to where currently saved no chnage in file names of paths is required.

as for trouble and work data stored elsewhere, perhaps this can be just as a comment attached to the cell in the conventional comment way. Just hover over the cell to see the info.

edit code as highlighted in red to add info as a comment

Please Login or Register  to view this content.

Hi Wotadude,

Thank you so much! it works perfectly as how i wanted it to be.

Oh, so you mean if i have a file that is named 'SBR Jan to Mar 2013', as long as it is in the same drive, it will work the same as 'SBR Oct to Dec 2013' without editing the code?


Regards,
Alf

Hi Wotadude,

One more thing, if i want to add a password to it to only allow access to run the macro, how should i go about doing it?

I tried going to 'save as' general option to add a password, it protects the workbook upon opening it but even though i key in the password, the macro wouldn't work unless i remove the password that i have added.

Really appreciate your kind assistance!

Thanks!


Regards,
Alf

Names and loactions of the files is not an issue. The files will save at there current location.
The macro will check that both files are not open read only (in other words not currently open by someone else) that woulkd normally prevent you from saving them.

perhaps ....

Please Login or Register  to view this content.

or add this to beginning of main macro

Please Login or Register  to view this content.

any of the above or password protection will help but never be foolproof.

All the best with your project, got lots to do.

Thanks Wotadude! You're have been really helpful!!