#1
|
|||
|
|||
Simple issues about adding hyperlinks
Hello,
I would like to make some modifications to the macro discussed in my previous post (refer to the URL: https://www.msofficeforums.com/word-...six-digit.html). The updated macro should have the ability to perform the following tasks: 1. Using the following code to browse and choose the Excel file (the Excel file contains column A (text) and column B (6-digit numbers) Code:
Dim EXL As Object Dim xlsWB As Object Dim xlsPath As String Set EXL = CreateObject("Excel.Application") xlsPath = BrowseForFile("Select Workbook", True) If Not xlsPath = vbNullString Then Set xlsWB = EXL.Workbooks.Open(xlsPath) Private Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String Dim fDialog As FileDialog On Error GoTo ERR_HANDLER Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog .Title = strTitle .AllowMultiSelect = False .Filters.Clear If bExcel Then .Filters.add "Excel workbooks", "*.xls,*.xlsx,*.xlsm" Else .Filters.add "Word documents", "*.doc,*.docx,*.docm" End If .InitialView = msoFileDialogViewList If .Show <> -1 Then GoTo ERR_HANDLER: BrowseForFile = fDialog.SelectedItems.Item(1) End With lbl_Exit: Exit Function ERR_HANDLER: BrowseForFile = vbNullString Resume lbl_Exit End Function Code:
'I don't know how to write this part. Here's the code for Extracting 6-digit numbers from Word for your references. With aRng.Find .ClearFormatting .Text = "[0-9]{6}" .MatchWildcards = True Code:
Dim Hr As Integer Dim Mn As Integer Dim Sc As Integer Dim startTime As Long Do While .Execute ' Get hours, minutes, and seconds from time marker hrs = CInt(Left(aRng.Text, 2)) mins = CInt(Mid(aRng.Text, 3, 2)) secs = CInt(Right(aRng.Text, 2)) startTime = hrs * 3600 + mins * 60 + secs Code:
Dim mtgID As String mtgID = InputBox If mtgID = Empty Then Exit Sub Code:
ActiveDocument.Hyperlinks.Add Anchor:=aRng, Address:="https://ABC?meetingid=" & mtgID & "&start=" & startTime aRng.Collapse Direction:=wdCollapseEnd Loop End With End Sub Code:
With Selection.Find .Font.Name = "Times New Roman" .Font.Size = 14 .Font.BOLD = True .MatchWildcards = True 1. Word Document (contain Names) Word Document.docx 2. Excel file (contain column A(Names) and column B (6-digit numbers) Excel file.xlsx Prior to conducting my research, I sought advice from experienced VBA experts who suggested a solution. According to their recommendation, I can incorporate a numbered bookmark for each item found. This approach enables the retrieval of the calculated start time in seconds, which can then be assigned back to the corresponding hyperlinks associated with each bookmark. But I am still new to VBA and I am not fully understand that. I would like to express my gratitude in advance to anyone who can provide assistance in editing the macro. Thank you! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Macro issue: Adding Hyperlinks to six-digit numbers | yanyan9896 | Word VBA | 5 | 10-08-2023 04:19 AM |
Adding Hyperlinks (more than 1) to a BIG picture | Word Smith | Word | 3 | 08-23-2022 04:01 PM |
Adding PreserveFormatOnUpdate to macro that updates all hyperlinks within a document | JellehFishh | Word VBA | 5 | 03-17-2020 02:47 PM |
Issues with transferring files with hyperlinks. Help with a solution? | tay | Excel | 0 | 02-20-2015 07:28 AM |
Hyperlinks get messed when adding or deleting some slides? | Learner7 | PowerPoint | 0 | 05-17-2010 04:39 AM |