Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2019, 12:23 PM
Jazz43 Jazz43 is offline Extract Path in quotes and insert it into text Windows 10 Extract Path in quotes and insert it into text Office 2016
Advanced Beginner
Extract Path in quotes and insert it into text
 
Join Date: Oct 2009
Posts: 54
Jazz43 is on a distinguished road
Default Extract Path in quotes and insert it into text

Hi,


Is there a way to extract a part of text from a line and insert it as a hyperlink on the "name of pdf" on that's line with macros?

I made a batch file to export a list of path and his filenames but it can't create hyperlinks inside a .txt file

In theory i know how can i did it.
But in practice, i don't know to much about vba.
I would really grateful if someone could help me, because the list has approximately 800 files.
So beforehand thanks.

Example:

With this:

Ars Combinatoria, Bruno, Llull - General Memory Chat - Art of Memory Forum.pdf "C:\Users\Admin\DOCUME~1\Inks\Ars Combinatoria, Bruno, Llull - General Memory Chat - Art of Memory Forum.pdf.lnk"
Only Few days left for exams - Homework, Test, and Study Help - Art of Memory Forum.pdf "C:\Users\Admin\DOCUME~1\Inks\Only Few days left for exams - Homework, Test, and Study Help - Art of Memory Forum.pdf.lnk"
https___forum_artofmemory_com_t_bitacora-memory-chronicle-11_26217.pdf "C:\Users\Admin\DOCUME~1\Inks\https___forum_artofm emory_com_t_bitacora-memory-chronicle-11_26217.pdf.lnk"
artofmemory_com_wiki_How_to_Build_a_Memory_Palace. pdf "C:\Users\Admin\DOCUME~1\Inks\artofmemory_com_wiki _How_to_Build_a_Memory_Palace.pdf.lnk"

I'll hope do this:

Ars Combinatoria, Bruno, Llull - General Memory Chat - Art of Memory Forum.pdf
Only Few days left for exams - Homework, Test, and Study Help - Art of Memory Forum.pdf
https___forum_artofmemory_com_t_bitacora-memory-chronicle-11_26217.pdf
artofmemory_com_wiki_How_to_Build_a_Memory_Palace. pdf

Last edited by Jazz43; 10-17-2019 at 12:38 PM. Reason: I add a line break
Reply With Quote
  #2  
Old 10-17-2019, 08:56 PM
gmayor's Avatar
gmayor gmayor is offline Extract Path in quotes and insert it into text Windows 10 Extract Path in quotes and insert it into text Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You can't have hyperlinks in a text file, but you can in a Word document. Assuming that you example is a fair representation call the following macro from a loop to open your documents
Code:
Sub AddLinks(oDoc As Document)
Dim oPara As Paragraph
Dim oRng As Range
Dim vPara As Variant
Dim strLink As String
Dim strDisplay As String
Dim lngPara As Long

    For Each oPara In oDoc.Paragraphs
        Set oRng = oPara.Range
        oRng.End = oRng.End - 1
        If InStr(1, oRng.Text, "C:\") > 1 Then
            vPara = Split(oRng.Text, Chr(34))
            strDisplay = Trim(vPara(0))
            strLink = Trim(Replace(vPara(1), Chr(34), ""))
            oRng.Text = ""
            oRng.Hyperlinks.Add Anchor:=oRng, Address:=strLink, TextToDisplay:=strDisplay
        End If
    Next oPara
lbl_Exit:
    Set oPara = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 10-19-2019, 08:11 AM
Jazz43 Jazz43 is offline Extract Path in quotes and insert it into text Windows 10 Extract Path in quotes and insert it into text Office 2016
Advanced Beginner
Extract Path in quotes and insert it into text
 
Join Date: Oct 2009
Posts: 54
Jazz43 is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
You can't have hyperlinks in a text file, but you can in a Word document. Assuming that you example is a fair representation call the following macro from a loop to open your documents
Code:
Sub AddLinks(oDoc As Document)
Dim oPara As Paragraph
Dim oRng As Range
Dim vPara As Variant
Dim strLink As String
Dim strDisplay As String
Dim lngPara As Long

    For Each oPara In oDoc.Paragraphs
        Set oRng = oPara.Range
        oRng.End = oRng.End - 1
        If InStr(1, oRng.Text, "C:\") > 1 Then
            vPara = Split(oRng.Text, Chr(34))
            strDisplay = Trim(vPara(0))
            strLink = Trim(Replace(vPara(1), Chr(34), ""))
            oRng.Text = ""
            oRng.Hyperlinks.Add Anchor:=oRng, Address:=strLink, TextToDisplay:=strDisplay
        End If
    Next oPara
lbl_Exit:
    Set oPara = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub
Thank you very much for the quick answer but I would not know how to call the macro with a loop, I tried to use the code by changing
Sub AddLinks(oDoc As Document) to:
Code:
Sub AddLinks()
Dim oDoc As Document
....
But it didn't work, so how can i use your code?
Reply With Quote
  #4  
Old 10-19-2019, 09:11 PM
gmayor's Avatar
gmayor gmayor is offline Extract Path in quotes and insert it into text Windows 10 Extract Path in quotes and insert it into text Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The following will loop through the documents in a selected folder and run my earlier code

Code:
Sub BatchProcess()
Dim strFile As String
Dim strName As String
Dim strPath As String
Dim oDoc As Document
Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .TITLE = "Select folder and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            MsgBox "Cancelled By User", , "List Folder Contents"
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1)
        Do Until Right(strPath, 1) = ""
            strPath = strPath & ""
        Loop
    End With
    strFile = Dir$(strPath & "*.doc")
    ' If the files are .txt format then change .doc in the line above to .txt
    While strFile <> ""
        Set oDoc = Documents.Open(strPath & strFile)
        'do stuff with the document e.g. call the macro I posted earlier
        AddLinks oDoc
        ' If the files are .txt format then add the following 2 lines
        'strName = Left(oDoc.Name, InStrRev(oDoc.Name, ".")) & "docx"
        'oDoc.SaveAs2 FileName:=strPath & strName, FileFormat:=12, AddToRecentFiles:=False
        oDoc.Close SaveChanges:=wdSaveChanges
        strFile = Dir$()
    Wend
    MsgBox "Processing complete"
lbl_Exit:
    Set oDoc = Nothing
    Exit Sub
End Sub
to test the original macro, open a document and run the following
Code:
Sub Test()
AddLinks ActiveDocument
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 10-20-2019, 10:43 PM
Jazz43 Jazz43 is offline Extract Path in quotes and insert it into text Windows 10 Extract Path in quotes and insert it into text Office 2013
Advanced Beginner
Extract Path in quotes and insert it into text
 
Join Date: Oct 2009
Posts: 54
Jazz43 is on a distinguished road
Default

Now it Works!

Thanks gmayor you have really saved my life.
Reply With Quote
Reply

Tags
link hyperlink filename

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Path in quotes and insert it into text Insert variable in path name bearcublandon Word VBA 6 01-03-2019 08:17 PM
Extract Path in quotes and insert it into text How to replace straight quotes with smart quotes in existing document PABwriter Word 4 05-27-2016 03:36 PM
Changing dumb quotes to smart quotes Reisende Word 2 05-02-2016 08:56 PM
Extract Path in quotes and insert it into text insert auto path & file name in ppt 2013 rideninc PowerPoint 1 01-28-2014 05:38 AM
Extract Path in quotes and insert it into text Changing single-quotes to double-quotes Bobosmite Word 5 04-15-2013 06:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft