Quote:
Originally Posted by gmayor
I guess so - make changes as follows:
Code:
Option Explicit
Private strWorkbook As String
Private strSheet As String
Sub Macro1()
strWorkbook = BrowseForFile("Select Workbook", True)
strSheet = InputBox("Enter worksheet name", "Worksheet", "Sheet1")
AddHLinks ActiveDocument, strWorkbook, strSheet
End Sub
Sub AddHLinks(oDoc As Document, strWorkbook As String, strSheet As String)
'etc
|
Thank you so much for your help! You are genius! I ran it but the system informed "BrowseForFile" has not defined. Then I added a function for the "BrowseForFile". It successfully add the hyperlinks to the texts.
[/CODE]
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][/QUOTE]
But the system always pop out a message 94 (Invalid use of Null (Error 94)) after it ran.
The code highlighted: sFindText = Arr(0, i)
(
Invalid use of Null (Error 94) | Microsoft Learn)
May I know how to avoid or ignore it? I guess the problem comes from the Excel Sheet. Since the first column of my excel file is a 6-digit number like " '001234", which started from the symbol " ' ". Without " ' ", the 6-digit number will become a 4-digit number.