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

Manipulating Files of Multiple Formats in a Folder Based on Date

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
Christopher Wright
Asked:
Christopher Wright
  • 13
  • 8
1 Solution
 
aikimarkCommented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
aikimarkCommented:
I would guess this is possible, but your question lacks enough specificity to garner an answer more than "probably so"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Any thoughts?
0
 
aikimarkCommented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
aikimarkCommented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir, I will definitely provide some examples.  Thank you so much!
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Any thoughts on this?
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Are you still waiting on me to provide something here?
0
 
aikimarkCommented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
aikimarkCommented:
Do you have a PDF 'printer' on your system?
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir, I do.  This my default printer as a matter of fact.  Thanks
0
 
aikimarkCommented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Any ideas?
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Are you still with me on this one?
0
 
aikimarkCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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