Macro for hyperlinks

[Jared1985]

I am new to Mr. Excel, but I have found some helpful things in the past via google searching. I am hoping I can get some help directly associated with my dilemma for a personal project I am working on. I'll try to be as accurate & thorough as possible.

Currently in my excel workbook I have two tabs. Tab one is strictly for a database table that information can be pulled from via the "Vlookup" function. Column "A" is a list of part numbers, "B" is the part description for the part number from "A", "C" has a formulated file path from columns "D, A, & F" (D holds the folder path G:\\SAMPLE FOR TESTING\, A holds the file name (which is the part number), & F holds the file extension = .docx). Currently these paths references only WORD documents, but will at some point hold information for other documents (Excel, PDF, and not sure how many others).

So, tab two I am using to look up information on tab one at random that will extract the information and insert it to the respective cells. However, the information in column "B" has been coupled with the hyperlink reference from "C" from tab one using the hyperlinks function. This was achieved by using the following formula where "ITEM HYPERLINKS" is reference to tab one........ The formula ties the hyperlink to the displayed text from column "B" allowing for the exact path to be masked/hidden by the text (part description) displayed in the cell(s) of column "B".

=HYPERLINK(VLOOKUP(A2,'ITEM HYPERLINKS'!A:C,3,FALSE),VLOOKUP(A2,'ITEM HYPERLINKS'!A:C,2,FALSE))

I can click on the link and it follows the path & opens the file in Word without any problems.

The problem:
I have set up a Control Button and have tried to set it up with a Macro that will allow for the path to be followed, the document to open, then the document print, the document close, the program close, then the next cell and continues until all documents have printed in order.
However, the process keeps telling me to debug.

Any ideas on coding that will allow for the macro to follow the formulated hyperlink and perform the functions to completion?

Thanks in advance for your any help! I've been stuck on this one for a while.

 

[Perpa]

Re: Need help with setting a macro for hyperlinks - detailed information on problem

Any ideas on coding that will allow for the macro to follow the formulated hyperlink and perform the functions to completion?

Thanks in advance for your any help! I've been stuck on this one for a while.

Jared1985,
Welcome to the forum.
The link below showed how to print Word Documents:
VBA-Excel: Open and Print the Word Document

This next link is from Windows Secrets Forum courtesy of zeddy, showed how to print PDF documents:
Print External PDF Files from Excel Sheet | Windows Secrets Lounge

Assuming you will select ALL or some of the part numbers to be printed (NOTE: just column C values would be selected). I combined the two macros using IF statements and made the following macro:

Sub Jared_Print()
'Select the Files to be printed, then RUN this macro
    For Each cell In Selection
        zFile = cell.Value             'fetch filename from cell
        
        If zFile Like "*.doc?" Then     'check it is a doc file type
            Dim objWord
            Dim objDoc
            Set objWord = CreateObject("Word.Application")
            Set objDoc = objWord.Documents.Open(zFile)
            objWord.Visible = True
            objDoc.PrintOut
            objWord.Quit
        End If
    
        If zFile Like "*.pdf" Then      'check it is a pdf file type
            zProg = "C:\Program Files (x86)\PDF Complete\pdfvista.exe"
            'NOTES for the 'Shell' line beow:
                  '/s=don't show splash screen
                  '/n=new instance
                  '/h=minimised window
                  '/t=print to default printer; or use /t <filename> 
 <drivername> 

            Shell (zProg & " /n /h /t " & zFile)        'execute command to print the pdf document
        End If
    
    Next
End Sub

You can add IF statements for additional file types if required...OR... You can use SELECT CASE to select the file type option.
I use PDF Complete, and zeddy shows alternatives for ADOBE READER, depending on the version you have.
You can run the macro using Alt+F8, or create a Form Control or ActiveX Control button.
So there is some code to print two file types from the files selected. Let us know how it goes.
Perpa</drivername></filename>

 

[Jared1985]

Wish I could say things went smoothly, but I keep getting an "Error 91" message on every run of the macro. From what I have found out it is a Problem bigger than myself or my system. Not sure how to resolve it, as I have tried but no options for adding "everyone" is available on my system.

 

[Perpa]

Jared1985,
It is not clear what you mean by the last sentence - '... I have tried but no options for adding "everyone" is available on my system. '

I tested the macro after selecting the complete path and filename including extension of several files listed in column C of my worksheet. Those files were printed in the order listed.

After selecting the files in column C, try stepping through the macro one line at a time to see where it errors.

Are all the documents in column C Word documents?

What PDF program and version is on your system?

Without more information I am not sure how I can help.
Perpa

 

[Jared1985]

The "everyone" refers to windows/Microsoft support files having to be added to "Everyone or All users", but when I followed the directions to update the sub-folders explicitly for this, there were no options allowing me to be able to complete it as directed. However, I have come across some other info on hyperlinks that may allow me to be able to more accurately and efficiently get the results I am looking to end up with. I just happen to stumble across it in another hyperlink discussion from another forum similar to this one.

Thank you very much for your help. I am going to be able to use some of your tips as well in some areas.

 

[Perpa]

The "everyone" refers to windows/Microsoft support files having to be added to "Everyone or All users", but when I followed the directions to update the sub-folders explicitly for this, there were no options allowing me to be able to complete it as directed. However, I have come across some other info on hyperlinks that may allow me to be able to more accurately and efficiently get the results I am looking to end up with. I just happen to stumble across it in another hyperlink discussion from another forum similar to this one.

Thank you very much for your help. I am going to be able to use some of your tips as well in some areas.

Jared,
Sounds like you are getting it sorted out...good to hear. For whatever help I may have been, you are most welcome.
Perpa