Automating Hyperlink List from folder - with each document's HEADER information.

[The_Fixer_Guy]

Hello all,

I am admittedly a newbie to VBA but have been learning, tinkering and made some successful macros or modified existing code I've found on the internet, in tutorials or in forums such as this.

I am very excited to join this community and learn more. Perhaps one day I will be proficient enough to offer advice or code to others on here. I certainly hope.

I am a temp at a corporate job tasked with organizing a directory in a master spreadsheet. I've already build the spreadsheet with individual sheets for the volume sections required.

Each sheet has navigation macro buttons. The column information is house in


column A (hyperlinked directory folder)


column B and column C are where my macro comes into play.


The macro runs up to a pre-assigned network address, grabs the file names in the folder and then creates a hyperlink list.

Rather than tying both columns B and C to a single macro, each colum has an "UPDATE" button to run the folder/hyperlink macro for that specific column.

It all works as expected and desired.

column B is the current policy macro list


column c is the "ARCHIVE" sub folder for that specific volume's root folder.



My question:
I am seeking to build another macro for column B (possibly modify it for column C as well). I would like the macro to be tied to or added as a submacro for columb B's macro so that when column B is updated it will create the hyperlink list that currently runs but also update an aditional 1-3 columns I will build that pulls information from a document's header.

I've found code that can pull date created, modified, size, ect. but that is superfluous. As well, the company want's to know when a document has been enacted as policy and that date needs to remain static but change when the document is updated. This is why I've contrived this system.


This way, workers in the office and create their documents and simply file them in the appropriate folders (as I currently have) and when they hit "UPDATE" the list will rebuild and serve the pertinent information (e.g. policy creation date, effective date, review date, revision due date).


If this can be done by pulling information from a document's HEADER or HEADER TABLE (as I suspect it can) then, all documents will be assigned this default header format, making the process more streamlined and workers will only need to hit the "UPDATE" button to automatically update the index spreadsheet and keep track of where policies and documents are and which need to be reviewed and modified.


Here is a sample of code for how the column B list is built (I have substituted the directory address with a generic address to protect the company's information.

Sub Introduction_Build_Index()

Range("B3:B500").ClearContents
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("directory folder address goes here")
i = 2
'loops through each file in the directory
For Each objFile In objFolder.Files
 'select cell
 Range(Cells(i + 1, 2), Cells(i + 1, 2)).Select
'create hyperlink in selected cell
 ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
 objFile.Path, _
 TextToDisplay:=objFile.Name
 i = i + 1
Next objFile
Call ScrollToTopLeft
End Sub
Sub ScrollToTopLeft()
'This macro scrolls to the top left of your spreadsheet (cell A1)
ActiveWindow.ScrollRow = 1 'the row you want to scroll to
ActiveWindow.ScrollColumn = 1 'the column you want to scroll to
End Sub

Any help in the direction I should look to automate the "document HEADER grab and listing" would be greatly appreciated.
As well, any sample code is welcome also.


Thank you all!
Have a GREAT DAY!

 

[Yongle]

See link below to see how to read document properties

https://exceloffthegrid.com/reading-document-properties-vba/