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

Posted on 2014-10-15
Last Modified: 2014-10-26
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.
Question by:Hank11
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
LVL 21

Expert Comment

by:Randy Poole
ID: 40383958
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

Author Comment

ID: 40385817
okay, updated names on files. Copying is *.ppt and Weekly is *.xlsm
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40390280
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40393484
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?
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40394742
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.

Author Comment

ID: 40401067
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.

Author Comment

ID: 40401069
please see new attachment.

Author Comment

ID: 40401072
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.

Author Comment

ID: 40401103

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.
LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 40402802
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.

Author Comment

ID: 40405561
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?

Author Closing Comment

ID: 40405562

Featured Post

Schedule a Tour of the ATEN booth at InfoComm 2017

Tour the ATEN booth to see the the Latest Addition to the Modular Matrix Switch Series, New 4K HDMI Over IP Extender and more! Enter ATEN's Ultimate Giveaway Sweepstakes for a chance to win one of several great prizes, including an ATEN US7220 2-Port Thunderbolt 2 Sharing Switch!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question