VBA excel - Data File from sheet to word document with hyperlink-CodePudding

I'm trying to create a Word file that contains excel data with hyperlink of file path data, like this:

But I want the result like this:

Here is the code I use:

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 = 2 To EndR
        
        wDoc.Range.InsertAfter Cells(i, 1) & vbCrLf
        wDoc.Range.InsertAfter vbCrLf
        wDoc.Paragraphs(wDoc.Paragraphs.Count).Range.Hyperlinks.Add _
          Anchor:=wDoc.Paragraphs(wDoc.Paragraphs.Count).Range, _
          Address:=Cells(i, 3), _
          TextToDisplay:=Left(Cells(i, 2), InStr(1, Cells(i, 2), ".") - 1)
        wDoc.Range.InsertAfter vbCrLf
        wDoc.Range.InsertAfter vbCrLf
    Next
        aWord.Visible = True

    aWord.Activate   'Xem ket qua
    Set wDoc = Nothing
    Set aWord = Nothing
End Sub

Please help! Thanks for reading.

CodePudding user response：

Seems like you just want to not repeat the folders, so test for that

replace

wDoc.Range.InsertAfter Cells(i, 1) & vbCrLf

with

If Cells(i, 1) <> Cells(i - 1, 1) then
    wDoc.Range.InsertAfter Cells(i, 1) & vbCrLf
End If