karenahahaha
10-16-2023, 07:03 PM
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
'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
3. Use the following code to calculate start time
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:
With Selection.Find
.Font.Name = "Times New Roman"
.Font.Size = 14
.Font.BOLD = True
.MatchWildcards = True`
Here are the demo files for testing:
Word Document (contain Names) https://docs.google.com/document/d/1sRHz--7o_OwhzVwcPf1SlKlJAVP5QPpb/edit?usp=sharing&ouid=108146638602496496156&rtpof=true&sd=true
Excel file (contain column A(Names) and column B (6-digit numbers) https://docs.google.com/spreadsheets/d/1slyC0Ht-qsMnsxGYtDhYWzufU-bq3Tbg/edit?usp=sharing&ouid=108146638602496496156&rtpof=true&sd=true
I would like to express my gratitude in advance to anyone who can provide assistance in editing the macro. Thank you!
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
'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
3. Use the following code to calculate start time
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:
With Selection.Find
.Font.Name = "Times New Roman"
.Font.Size = 14
.Font.BOLD = True
.MatchWildcards = True`
Here are the demo files for testing:
Word Document (contain Names) https://docs.google.com/document/d/1sRHz--7o_OwhzVwcPf1SlKlJAVP5QPpb/edit?usp=sharing&ouid=108146638602496496156&rtpof=true&sd=true
Excel file (contain column A(Names) and column B (6-digit numbers) https://docs.google.com/spreadsheets/d/1slyC0Ht-qsMnsxGYtDhYWzufU-bq3Tbg/edit?usp=sharing&ouid=108146638602496496156&rtpof=true&sd=true
I would like to express my gratitude in advance to anyone who can provide assistance in editing the macro. Thank you!