Hi everone,
I would like to seek help about editing the following macro.
(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 aim the macro can 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)
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
2. Extract 6-digit numbers from selected Excel file's column B into the macro
3. Use the following code to calculate start time'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
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
4. Enter the meeting ID for merging the link
Dim mtgID As String mtgID = InputBox If mtgID = Empty Then Exit Sub
5. Add hyperlinks to the names according to selected Excel file's column A
ActiveDocument.Hyperlinks.Add Anchor:=aRng, Address:="https://ABC?meetingid=" & mtgID & "&start=" & startTime aRng.Collapse Direction:=wdCollapseEnd Loop End With End Sub
Those names' format are as follows:
Here are the demo files for testing:With Selection.Find .Font.Name = "Times New Roman" .Font.Size = 14 .Font.BOLD = True .MatchWildcards = True`
- Word Document (contain Names) https://docs.google.com/document/d/1...f=true&sd=true
- Excel file (contain column A(Names) and column B (6-digit numbers) https://docs.google.com/spreadsheets...f=true&sd=true
I would like to express my gratitude in advance to anyone who can provide assistance in editing the macro. Thank you!