How to export hyperlinks from Excel to Word?

syl3786 · #7 02-23-2023, 08:49 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Export Word Data to Excel	anifa	Word	2	12-11-2022 02:51 PM
Word form with the intent of Excel export	Flyckten	Word VBA	5	09-03-2018 05:34 AM
VBA Export Data as Text from Excel to Word	lwbarnes	Word VBA	3	06-09-2016 02:47 PM
Through VBA, export range from Excel to Word	duugg	Word VBA	0	08-24-2009 07:50 PM
Word to Excel hyperlinks and spaces	gak	Word	1	09-14-2008 08:38 AM