Hi everyone,
I need help with VBA excel
I have a table below
I have a code to extract data from table to word and add hyperlink of the file, but I don't know how to add style heading level 1 to folder name and heading level 2 to file name so that they can be shown in Navigation pane.
Please help!
Thanks for reading
Oh, and here is my code:
Option Explicit
Sub InsertHyperLink()
Dim aWord As Object
Dim wDoc As Object
Dim i&, EndR&
Dim Rng As Range
Set aWord = CreateObject("Word.Application")
Set wDoc = aWord.Documents.Add
EndR = Range("A65536").End(xlUp).Row
For i = 9 To EndR
If Cells(i, 3) <> Cells(i - 1, 3) Then
wDoc.Range.InsertAfter Cells(i, 3) & vbCrLf
End If
wDoc.Range.InsertAfter vbCrLf
wDoc.Paragraphs(wDoc.Paragraphs.Count).Range.Hyperlinks.Add Anchor:=wDoc.Paragraphs(wDoc.Paragraphs.Count).Range, _
Address:=Cells(i, 5), TextToDisplay:=Left(Cells(i, 4), InStr(1, Cells(i, 4), ".") - 1)
wDoc.Range.InsertAfter vbCrLf
wDoc.Range.InsertAfter vbCrLf
Next
aWord.Visible = True
aWord.Activate 'Result
Set wDoc = Nothing
Set aWord = Nothing
End Sub
I need help with VBA excel
I have a table below
I have a code to extract data from table to word and add hyperlink of the file, but I don't know how to add style heading level 1 to folder name and heading level 2 to file name so that they can be shown in Navigation pane.
Please help!
Thanks for reading
Oh, and here is my code:
Option Explicit
Sub InsertHyperLink()
Dim aWord As Object
Dim wDoc As Object
Dim i&, EndR&
Dim Rng As Range
Set aWord = CreateObject("Word.Application")
Set wDoc = aWord.Documents.Add
EndR = Range("A65536").End(xlUp).Row
For i = 9 To EndR
If Cells(i, 3) <> Cells(i - 1, 3) Then
wDoc.Range.InsertAfter Cells(i, 3) & vbCrLf
End If
wDoc.Range.InsertAfter vbCrLf
wDoc.Paragraphs(wDoc.Paragraphs.Count).Range.Hyperlinks.Add Anchor:=wDoc.Paragraphs(wDoc.Paragraphs.Count).Range, _
Address:=Cells(i, 5), TextToDisplay:=Left(Cells(i, 4), InStr(1, Cells(i, 4), ".") - 1)
wDoc.Range.InsertAfter vbCrLf
wDoc.Range.InsertAfter vbCrLf
Next
aWord.Visible = True
aWord.Activate 'Result
Set wDoc = Nothing
Set aWord = Nothing
End Sub