Hello everyone.. I apologize if this comes off as a long-winded explanation..
At my job, we use Excel frequently for database management. One database in particular keeps information on individuals that are processed (Ex: Name, Reason Code, Date Processed, Case #, Ect...)
<tbody>
</tbody>
Before a person's information is logged in the database spreadsheet, a case file must be made and submitted for approval. Once approved, The Case files are stored in a Hard Drive folder in PDF format. The person's information is then logged in the spreadsheet with the Case# in Column 5. Each Case# for each individual is hyperlinked to their respective PDF Case File.
My boss has recently assigned me the task of reviewing, completing, signing, and hyperlinking 5 years worth of case files that have been on the back burner since 2012. (400+ Case Files...) Over the past few days, I've neared its completion and have about 100 left to review and sign before the hyperlinking phase, in which I will have to check/hyperlink the 400+ case files to each individual in the database. This is where I need your help..
I have an idea for a VBA that will save me some time but my experience in VBA/Macros is minimal.. I've only just found out about this convenience and I'm attempting to absorb as much information as I can to learn it and use it to my benefit, but for now PLEASE HELP ME if you can.
So here's what I'm hoping for: I want a Macro that hyperlinks the PDF Case File to the Case# of each individual in the database automatically or through as few steps as possible. The PDF file name matches the value of Column A (the persons name).
Spreadsheet Example:
<tbody>
</tbody>
PDF Example: John Doe.pdf
These two values match and I'm assuming a find function exists that can match a cell value to a file name. I need to place the found file in another cell (in this case: the Case# portion) as a hyperlink. All of the PDF case files are in the same folder in the hard drive so the location stays the same, only the file name changes. What are my options or is this even doable?
At my job, we use Excel frequently for database management. One database in particular keeps information on individuals that are processed (Ex: Name, Reason Code, Date Processed, Case #, Ect...)
Name | SSN | Date | Reason Code | Case# | Company | Approver |
John Doe | 0000 | 4/5/2012 | DA | 45678 | Atlantic | John Smith |
Jane Doe | 0001 | 5/6/2013 | SV1 | 23456 | Brock | John Smith |
<tbody>
</tbody>
Before a person's information is logged in the database spreadsheet, a case file must be made and submitted for approval. Once approved, The Case files are stored in a Hard Drive folder in PDF format. The person's information is then logged in the spreadsheet with the Case# in Column 5. Each Case# for each individual is hyperlinked to their respective PDF Case File.
My boss has recently assigned me the task of reviewing, completing, signing, and hyperlinking 5 years worth of case files that have been on the back burner since 2012. (400+ Case Files...) Over the past few days, I've neared its completion and have about 100 left to review and sign before the hyperlinking phase, in which I will have to check/hyperlink the 400+ case files to each individual in the database. This is where I need your help..
I have an idea for a VBA that will save me some time but my experience in VBA/Macros is minimal.. I've only just found out about this convenience and I'm attempting to absorb as much information as I can to learn it and use it to my benefit, but for now PLEASE HELP ME if you can.
So here's what I'm hoping for: I want a Macro that hyperlinks the PDF Case File to the Case# of each individual in the database automatically or through as few steps as possible. The PDF file name matches the value of Column A (the persons name).
Spreadsheet Example:
Name | Date | Case # | Company |
John Doe | 12/5/2012 | 45678 | Atlantic |
<tbody>
</tbody>
PDF Example: John Doe.pdf
These two values match and I'm assuming a find function exists that can match a cell value to a file name. I need to place the found file in another cell (in this case: the Case# portion) as a hyperlink. All of the PDF case files are in the same folder in the hard drive so the location stays the same, only the file name changes. What are my options or is this even doable?