Excel 2007 copy images from one file to another automatically or with macro

Attached are 3 files. The ppt tries to explain - but I'm sure it's not enough.

In essence, I have 8 field agent who report the status of their projects in their own excel 2007 file.  We have a master file with a consolidation worksheet "PI - Read Only" that have automatic links turned on and reads (equals) text from the agents reports using a vlookup.  Only the manger has access to this file.  This updates the consolidation worksheet on the fly.  The consolidation worksheet essentially pools the information into a master repository from the field agents files which reside in separate sub-folders on the same server.  The master consolidation file also has two template reports "TFM" and "QA" that read the consolidation worksheet and they populate the "TFM" and "QA" template report cells accordingly.  This all works, no problem.  Now, upper management would like to see updated status pictures on the "QA" report. We can include place-holder cells on the fields agents reports where they can place their images, but do not have the know-how to copy images from their report onto the "QA" report template.  We would like it to be an automatic link like the text, but somehow I don't think that's possible without a macro that can be executed from either the master file or from each of the agents files.

I've included the master consolidation file as well as an agent's file.

Thank you for showing us how this can be done.
Hank IsaacsEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
Your attachments beginning with the file name Weekly-, and Copying-, do not have file extensions.  Can you reattach with the extensions.  this may be a limitation on the file name field EE has, so you may need to rename with less characters, then just use the description to specify what it is for
Hank IsaacsEngineerAuthor Commented:
okay, updated names on files. Copying is *.ppt and Weekly is *.xlsm
Ejgil HedegaardCommented:
Try attached files

I put the pictures in the agent file in column O, P and Q, and set the header to "Picture 1", "Picture 2" and "Picture 3".
The header "Picture 1" is used in the program to find first column next to the data columns, so any agent file must have that.

Check the folder path that holds the agents folders.
I have set it to "C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\", based on the path in the lookup formulas.
For test you could make a folder for the master file, and a folder in that "George Akin - Input" for the agent file.
Change the statement TopAgentFolder = "C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\"
to TopAgentFolder = ThisWorkbook.Path + "\"

The folders and files are searched and used if "Input" is in the name.
If C4I means week 41, and is changed every week to point to this or last week, it is possible to find the week number, and change the path to match that.
Or make a function where you can select the folder above the agents folders.
Some of the projects in the agent file is not in the master file, a list is displayed.

The Auto row adjustment macro is a lot of repeated statements, for ranges next to "Status" in column B.
I have changed it to find the ranges, and only have the statements once.
Moved the old macro to another module.

Added a userform to display the progress in the programs.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hank IsaacsEngineerAuthor Commented:
Thank you, Ejgil

The person who put our original files together is no longer working here.  I'm going through a learning curve to take this over.

I'm not sure I understand if there's file renaming or the folder hierarchy to place the Master and the 8 agent files.  I can create or move folders, move files or rename files to make this work. A step by step list of actions for me would be helpful. Also, on the agent files, do I simply create "Picture 1", "Picture 2" and "Picture 3" in columns O,P & Q? for each of the 8 agents' files?
Ejgil HedegaardCommented:
There are 8 links in the file to other workbooks.
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\BK Ahn - Input\BK Ahn - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\George Akin - Input\George Akin - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\Mick Daigle - Input\Mick Daigle - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\John Fallon - Input\John Fallon - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\Bob Fyler - Input\Bob Fyler - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\Hubert Huh - Input\Hubert Huh - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\Jeon, Son Won - Input\Jeon, Son Won - Input.xlsx
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\Park, Yu Ho - Input\Park, Yu Ho - Input.xlsx

That means that each agent has his own folder in the folder
C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\
If the files are on a network, then it is not C: but another drive specification letter.

All the files and folders have the word "Input" in the name, so the program is made to search for that, for folders, and files.

The easiest solution is that the agents files are in the same folder as the Weekly-Master file.
Then no specific folder specification is needed, and ThisWorkbook.Path can be used, no matter the location, local or network.
ThisWorkbook is the file with the program = The Master file.
But you can put the files anywhere, even the links above can be read and used.

Try the test I mentioned for the 2 files I uploaded, just to see it work.
For test you could make a folder for the master file, and a folder in that "George Akin - Input" for the agent file.
Change the statement TopAgentFolder = "C:\C4I\C4I Construction Management\Weekly_Report\Weekly Reports\"
to TopAgentFolder = ThisWorkbook.Path + "\"

Also, on the agent files, do I simply create "Picture 1", "Picture 2" and "Picture 3" in columns O,P & Q? for each of the 8 agents' files?
Yes, make these headers in row 1.
Hank IsaacsEngineerAuthor Commented:
I adjusted the file path slightly for the weekly report and now the it indicates the agent folders cannot be found.  Can you please adjust the weekly-master to find the agents input?  Thank you.
Hank IsaacsEngineerAuthor Commented:
please see new attachment.
Hank IsaacsEngineerAuthor Commented:
I found the problem = I searched the script and changed the TopAgentFolder = "C" drive to "W" drive.  It appears to be working now.

I will insert photos next to the agents spreadsheets and test. Thanks.
Hank IsaacsEngineerAuthor Commented:

Tested, works great.

Before I accept the solution, I have a question.  Can we insert another module in the weekly-master that can search for pictures in the QA report, resize and center them to fit the cell?

If this can be done, I will submit as another question so you can get credit for it as well.

Thank you.
Ejgil HedegaardCommented:
Good you got it working.
Expected you had to change the drive letter.
If the files are on a network, then it is not C: but another drive specification letter.
But it is easy to miss a sentence in a long description.

It is a learning process to make VBA code.
Did you see the change I made for the row height adjustment macro?

And yes, it is possible to adjust picture size to fit the cell.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hank IsaacsEngineerAuthor Commented:
I did see the row height adjustment and your VBA code is very elegant. - Thank you.

I will accept this solution now and post another question to auto-size and center the images inside the destination cells on the "QA" report after they are copied over from agents file.

How can we arrange for you to do the VBA code?
Hank IsaacsEngineerAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.