Solved

Manipulating Files of Multiple Formats in a Folder Based on Date

Posted on 2013-02-06
21
203 Views
Last Modified: 2013-03-15
Greetings everyone.  Does anyone know how to help me out with this if at all possible.  I want to search for files in a specific folder, find any newly created or updated files, then convert the newly created files to a pdf, merge the newly updated files to another file (with similar name), and finally update a spreadsheet I am using a database with a hyperlink to the new files created.  


The spreadsheet that I want to update (attached) is what the users use to see the files.  I have userforms that contain the names of the files.  I want to add any newly created files to the list on this sheet.  This would involve adding a hyperlink to the file location.  It would also be a plus if there was someway to pull the information such as part number, description, etc off of the file and add to the database form but I can save that for another question.  Right now, i am concerned only with creating/updating the hyperlink to the file location. There should be no need to change any of the files that were recently updated since their name and hyperlink should already be on the spreadsheet.  Is there a better way to do this?  If so, I am open to suggestions.  Thanks everyone!
Berry-Letter-Database.xlsm
0
Comment
Question by:Christopher Wright
  • 13
  • 8
21 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 38871082
The best way to do this is to have new files arrive in one directory and then get moved to another directory after processing.

...and finally update a spreadsheet I am using a database with a hyperlink to the new files created
This really needs tweaking.  It isn't clear whether this is two separate sentences or you might be missing "as".
0
 

Author Comment

by:Christopher Wright
ID: 38873876
My apologies for the ambiguity.  I have a column with hyperlinks to each Berry letter location.  Ultimately, I would like to have this automatically maintained with code.  Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38874280
I would guess this is possible, but your question lacks enough specificity to garner an answer more than "probably so"
0
 

Author Comment

by:Christopher Wright
ID: 38874472
In my introductory thread, I provided a workbook named 'BERRY LETTER DATABASE.'  In this workbook, I have two hidden worksheets named 'SHEET1' and 'BERRY LETTER ITEM DATABASE.'  The sheet named 'BERRY LETTER ITEM DATABASE' is not needed in this process.  The hyperlinks in which I am referring to are found on 'SHEET1.'  The hyperlinks display pdf files that are stored in a specific location.  To construct the hyperlink, I have combined the strings below:

• File Location:  "file:///\\ads-fs1\ads\Chris Wright\Approved Berry Letters\"
• Vendors Folder Name:  "180'S Innovative Performance Wear\"
• Vendor Spreadsheet Name: "180's Innovative Performance Wear Berry Letter"
• File Type:  ".pdf"

The only portion of the hyperlink which changes is the vendor's name. Once a vendor has a hyperlink, it will no longer change. They problem lies in having these created with code based on what is found in a certain location (specified below).

In my introductory thread, I stated that I wanted to:
• Search for files in a specific folder  (U:\Berry Letters\APPROVED\)
• Find any newly created or updated files
• Convert the newly created files to a pdf (if they are not already a pdf)
• Look in a different location for existing files (I:\Chris Wright\Approved Berry Letters\)
• If a file already exists, then merge the existing with the new.
• If no file exists, then create a folder and save the new files
• Finally, when all of this is complete, I want to update the spreadsheet named 'BERRY LETTER DATABASE' that we are reffering to.

This workbook is being used as a database that contains the hyperlinks to all of the pdf files for viewing.  I hope I specified the process in which I am hoping to automate.  Please let me know if I need to clear anything else.  I hope to change "probably so" to a "definitely yes".  Thanks for all of your help and support.  God bless!
0
 

Author Comment

by:Christopher Wright
ID: 38876909
Any thoughts?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38877251
What is a detailed description of "merge" relative to the newly created and existing PDF files?

What criteria are used to match new files with existing files?
0
 

Author Comment

by:Christopher Wright
ID: 38878368
By merge, I simply want to add one pdf file to another.  It's basically the same as doing a right click and select the "combine supported files in acrobat".  This is what I am doing now in the manual process.  I have provided a picture referencing what I am speaking of.

The criteria used to match new files with existing is determined by the date it is added to the location "U:\Berry Letters\APPROVED\".  I want to have any file added the day prior into this location and move it to the location of the Berry Letters "I:\Chris Wright\Approved Berry Letters\.  So basically, we are moving the files from the location in the U:\ drive and move it to the I:\ drive.  The only tricky part is that if no folder already exists in the I:\ drive for a newly added Vendor Berry letter, I'd like to have a new folder created and then save the newly created pdf file into it.  As I stated before, the only time a merge is necessary is if a file is added to the U:\ drive location and there already exists a file/folder in the I:\ drive with the same name.  Thanks

Combine files in acrobat
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38880273
I concatenate PDF files using the PDFTK.EXE utility. The utility requires a DLL in the same directory as the utility, downloaded with the PDFTK 'package'.

>>...is determined by the date...
I think the file names are somehow involved.  Can you show/describe this process with three or four files and folders?
0
 

Author Comment

by:Christopher Wright
ID: 38881501
Yes sir, I will definitely provide some examples.  Thank you so much!
0
 

Author Comment

by:Christopher Wright
ID: 38882981
I have attached a Word doc which provides a view of each drive location.  In the U:Drive, you will see the names of each file and the date which is was created/modified.  If that date falls within a certain time frame, then it will qualify to be copy/moved.  Now, if the file that qualifies to be copy/moved is not a pdf, it would need to be merged to the existing pdf somehow.  Possibly convert to pdf and the merge?  You'd probably know a better way.  

Moving on, once the file is converted to pdf, then it would need to either be merged to a file that exists already or, if the file does not exist in the I:Drive already, create a new file altogether.

In this example, if I wanted to move the Vendors added on 02/12/2013, you'll see that two files named Amphenol exists in theU:Drive.  These would need to be merged from the U:Drive and moved to the I:Drive.  If it so happened that Amphenol already existed in the I:Drive, then the two files would be merged together from the U:Drive and merged to the files in the I:Drive.  

If you see the file named Gerber, it is not in .pdf format.  This would be an example of a file that needed to be converted to .pdf and then moved and/or merged to the I:Drive.
Screen-Shots.docx
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Christopher Wright
ID: 38895173
Any thoughts on this?
0
 

Author Comment

by:Christopher Wright
ID: 38902102
Are you still waiting on me to provide something here?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38902581
If you have files without an extension, what application is associated with them?  You used "Gerber" as an example.  Is it a text file, Word document, Excel workbook, or something else?
0
 

Author Comment

by:Christopher Wright
ID: 38907336
All files will have an extension as .pdf, excel (.xls, .xlsx), word (.doc, .docx), and sometimes picture files (bitmap and .jpeg).  I have never seen a .txt file come across.  So I feel safe in saying that those are the only types.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38908029
Do you have a PDF 'printer' on your system?
0
 

Author Comment

by:Christopher Wright
ID: 38915121
Yes sir, I do.  This my default printer as a matter of fact.  Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38915232
Which PDF-producing software is this? What we need to be able to do is to specify the path and name of the resulting PDF file. Some software gives you this capability and some only displays a dialog box for user interaction.
0
 

Author Comment

by:Christopher Wright
ID: 38916424
The software I use for the PDF printer is  Adobe PDF.  I believe that this is standard software, nothing fancy.  Everytime I print, it requests a path and name before proceeding.
0
 

Author Comment

by:Christopher Wright
ID: 38923461
Any ideas?
0
 

Author Comment

by:Christopher Wright
ID: 38969960
Are you still with me on this one?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 38974834
I've done some research on the solution, but I have no way to test this.

There seems to be a way to control the Adobe printer.
http://www.access-programmers.co.uk/forums/showthread.php?t=194395
http://forums.adobe.com/thread/790897
0

Featured Post

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

759 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

19 Experts available now in Live!

Get 1:1 Help Now