Solved

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

Posted on 2014-10-15
12
196 Views
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.
Weekly-Master-C4I-Status-Report-VBA-10-O
George-Akin---Input.xlsx
Copying-images-between-Excel-files-using
0
Comment
Question by:Hank11
  • 8
  • 3
12 Comments
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
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
 

Author Comment

by:Hank11
Comment Utility
okay, updated names on files. Copying is *.ppt and Weekly is *.xlsm
Copying-images.pptx
Weekly-Master.xlsm
0
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
 

Author Comment

by:Hank11
Comment Utility
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
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
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
 

Author Comment

by:Hank11
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Hank11
Comment Utility
please see new attachment.
0
 

Author Comment

by:Hank11
Comment Utility
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
 

Author Comment

by:Hank11
Comment Utility
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
 
LVL 20

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
Comment Utility
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
 

Author Comment

by:Hank11
Comment Utility
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
 

Author Closing Comment

by:Hank11
Comment Utility
Elegant!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now