• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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.
Weekly-Master-C4I-Status-Report-VBA-10-O
George-Akin---Input.xlsx
Copying-images-between-Excel-files-using
0
Hank11
Asked:
Hank11
  • 8
  • 3
1 Solution
 
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
0
 
Hank11Author Commented:
okay, updated names on files. Copying is *.ppt and Weekly is *.xlsm
Copying-images.pptx
Weekly-Master.xlsm
0
 
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.
Weekly-Master.xlsm
George-Akin---Input.xlsx
0
Industry Leaders: 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!

 
Hank11Author 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?
0
 
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.
0
 
Hank11Author 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.
File-Path---weekly-master-and-agent-sub-
0
 
Hank11Author Commented:
please see new attachment.
0
 
Hank11Author 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.
0
 
Hank11Author Commented:
Ejgil,

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.
0
 
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.
0
 
Hank11Author 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?
0
 
Hank11Author Commented:
Elegant!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now