Results 1 to 10 of 10

Thread: Export Excel CSV to MS word with specific table format

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Export Excel CSV to MS word with specific table format

    Im a bit out of my comfort zone with excel so was wondering if someone can point me in the right direction of how this could be accomplished?


    We have a CSV file that contains multiple columns, some rows however have additional columns that need to be inside that specific row ONLY and not create additional rows.. i need to export this data into a word document table like so (pic attached).
    [
    example attached (pic) - is that if there are additional collateral types and descriptions for the same FS number, split the column for the row instead in the word table.

    basically i need to get the excel data into a table like that into a word table.. is that possible?


    is there any way to accomplish this?

  2. #2
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,894
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    This is a Word VBA macro which imports the .csv file into the current Word document, so put the code in a Word module, not an Excel module.

    Code:
    Public Sub Create_Table_From_Csv_File()
    
        Dim csvFile As String
        Dim FSO As Object
        Dim csvTextStream As Object
        Dim csvLines As Variant
        Dim csvData As Variant
        Dim csvTable As Table
        Dim i As Long, r As Long, c As Long
        
        csvFile = "C:\folder\path\Your csv data.csv"   'CHANGE THIS
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set csvTextStream = FSO.OpenTextFile(csvFile)
        csvLines = Split(csvTextStream.ReadAll, vbCrLf)
        csvTextStream.Close
        
        ReDim csvData(0 To UBound(csvLines) - 1)
        For i = 0 To UBound(csvLines) - 1
            csvData(i) = Split(csvLines(i), ",")
        Next
        
        Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=4, _
                                                 DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
        With csvTable
        
            .Cell(1, 1).Range.Text = csvData(0)(0)   'FS Number
            .Cell(1, 2).Range.Text = csvData(0)(1)   'Reg
            .Cell(1, 3).Range.Text = csvData(0)(3)   'Collateral type
            .Cell(1, 4).Range.Text = csvData(0)(4)   'Collateral description
    
            r = 0
            For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                If UBound(csvData(i)) > 4 Then
                    'Split this row (i+r+1) columns 3 and 4 into multiple rows
                    .Cell(i + r + 1, 3).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 4).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                End If
                For c = 3 To UBound(csvData(i)) Step 2
                    .Cell(i + r + 1, 3).Range.Text = csvData(i)(c)
                    .Cell(i + r + 1, 4).Range.Text = csvData(i)(c + 1)
                    r = r + 1
                Next
                r = r - 1
            Next
            
        End With
    
        MsgBox "Done!"
        
    End Sub

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    Hi John, that's really nearly perfect thank you!

    i'm struggling with understanding the rangetext and .cell part as in the real version there are 10 columns and the possibility of columns 8,9,10 with the same headers repeating more times for the same row.

    the repeating columns are Collateral Type, Collateral Description and Item(s).

    I tried to fiddle with the numbers but it doesnt work for me and get a debug!



    Where can i figure out what the numbers mean and what column/row they are referencing?

    I understand a few parts to change but not the numbers in specific parts

    Code:
        Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
        With csvTable
        
            .Cell(1, 1).Range.Text = csvData(0)(0)   'FS Number
            .Cell(1, 2).Range.Text = csvData(0)(1)   'Reg
            .Cell(1, 3).Range.Text = csvData(0)(3)   'Collateral type
            .Cell(1, 4).Range.Text = csvData(0)(4)   'Collateral description
    .cell(1,5).range.text = Csvdata(0)(5) 'Column name ??
    
    
            r = 0
            For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                If UBound(csvData(i)) > 4 Then
                    'Split this row (i+r+1) columns 3 and 4 into multiple rows
                    .Cell(i + r + 1, 3).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 4).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
    This bit i am not understanding :)
                End If
                For c = 3 To UBound(csvData(i)) Step 2
                    .Cell(i + r + 1, 3).Range.Text = csvData(i)(c)
                    .Cell(i + r + 1, 4).Range.Text = csvData(i)(c + 1)
                    r = r + 1
                Next
                r = r - 1

  4. #4
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,894
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    The .Cell syntax is .Cell(row, column). Add the numbers and variables either side of the comma to determine which row or cell they are referencing. When a Cell is Split, the number of rows in the table increases by NumRows (or it might be NumRows-1 - I don't know without checking), hence the need for the i + r + 1 addition.

    NumColumns specifies the number of columns in the new Word table, so specifying NumColumns:=10 will create the Word table with 10 columns; is that what you want? You seem to be saying that it should be 5 columns, with "Item(s)" as the 5th column, so shouldn't it be NumColumns:=5?

    For your repeating "Item(s)" column as the 5th column in the table, try adding (untested):

    Code:
    .Cell(i + r + 1, 5).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
    and in the For Next loop:
    Code:
                    .Cell(i + r + 1, 5).Range.Text = csvData(i)(c + 2)

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    Slowly getting there trying to get an understanding of i + r + 1 is proving difficult, where is information for this looping command online?

    so to confirm i have 10 columns, columns 8,9,10 can repeat so there may be further information for certain rows with data in 11,12,13 , 14,15,16 etc that need to be looped from if exist for that row so it creates one column for all collateral type, description and items.

    the editing i have done is this but it debugs on me i assume as i have the looping bit syntax slightly wrong

    Code:
        Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _                                             DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
        With csvTable
        
            .Cell(1, 1).Range.Text = csvData(0)(0)   'Fin
            .Cell(1, 2).Range.Text = csvData(0)(1)   'P
            .Cell(1, 3).Range.Text = csvData(0)(3)   'P2
            .Cell(1, 4).Range.Text = csvData(0)(4)   'Expiry Date and Time
            .Cell(1, 5).Range.Text = csvData(0)(5)   'Status
            .Cell(1, 6).Range.Text = csvData(0)(6)   'D
            .Cell(1, 7).Range.Text = csvData(0)(7)   'Secured
            .Cell(1, 8).Range.Text = csvData(0)(8)   'Collateral Type
            .Cell(1, 9).Range.Text = csvData(0)(9)   'Collateral description
            .Cell(1, 10).Range.Text = csvData(0)(10)  'Items
    
    
    
    
    
    
            r = 0
            For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                If UBound(csvData(i)) > 7 Then
                    'Split this row (i+r+1) columns 3 and 4 into multiple rows
                    .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                End If
                For c = 3 To UBound(csvData(i)) Step 2
                    .Cell(i + r + 1, 8).Range.Text = csvData(i)(c)
                    .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 2)
                    .Cell(i + r + 1, 10).Range.Text = csvData(i)(c + 3)

    When trying your amended looping bit just for one column (7) it debugs on this line

    Code:
           For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                If UBound(csvData(i)) > 7 Then
                    'Split this row (i+r+1) columns 3 and 4 into multiple rows
                
                .Cell(i + r + 1, 7).Split NumRows:=(UBound(csvData(i)) - 4) / 2 + 1, NumColumns:=1
                End If
                For c = 3 To UBound(csvData(i)) Step 2
                
                    .Cell(i + r + 1, 7).Range.Text = csvData(i)(c + 2)
                    
                    r = r + 1
                Next
                r = r - 1
            Next
            
        End With
    
    
        MsgBox "Done!"
        
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,894
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    Quote Originally Posted by originalsauce View Post
    Slowly getting there trying to get an understanding of i + r + 1 is proving difficult, where is information for this looping command online?
    That isn't a loop; it's a simple addition, accounting for the rows added when a row is split - the r variable is incremented in the For c loop.

    so to confirm i have 10 columns, columns 8,9,10 can repeat so there may be further information for certain rows with data in 11,12,13 , 14,15,16 etc that need to be looped from if exist for that row so it creates one column for all collateral type, description and items.
    But this part of your code implies that column numbers 9, 10, 11 in the .csv file can repeat:


    Code:
        With csvTable
        
            .Cell(1, 1).Range.Text = csvData(0)(0)   'Fin
            .Cell(1, 2).Range.Text = csvData(0)(1)   'P
            .Cell(1, 3).Range.Text = csvData(0)(3)   'P2
            .Cell(1, 4).Range.Text = csvData(0)(4)   'Expiry Date and Time
            .Cell(1, 5).Range.Text = csvData(0)(5)   'Status
            .Cell(1, 6).Range.Text = csvData(0)(6)   'D
            .Cell(1, 7).Range.Text = csvData(0)(7)   'Secured
            .Cell(1, 8).Range.Text = csvData(0)(8)   'Collateral Type
            .Cell(1, 9).Range.Text = csvData(0)(9)   'Collateral description
            .Cell(1, 10).Range.Text = csvData(0)(10)  'Items
    The csv rows and columns are indexed starting at 0. csvData(0)(0) is row 1, column 1 in the csv data, csvData(0)(1) is row 1, column 2, csvData(0)(8) is row 1, column 9 (not column 8). The Word table rows and columns are indexed starting at 1 - .Cell(1, 1) is row 1, column 1 in the table, .Cell(1, 2) is row 1, column 2 in the table. You have omitted csvData(0)(2) in the above code - is that intentional?

    Can you clarify exactly what the repeating column numbers are in the csv data? The first column being column 1.

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    Thanks John, I have correct the columns like so:
    Code:
       
            .Cell(1, 1).Range.Text = csvData(0)(0)   'Row 1 column 1
            .Cell(1, 2).Range.Text = csvData(0)(1)   'Row 1 column 2
            .Cell(1, 3).Range.Text = csvData(0)(2)   ' Row 1 column 3
            .Cell(1, 4).Range.Text = csvData(0)(3)   'Row 1 column 4
            .Cell(1, 5).Range.Text = csvData(0)(4)   'Row 1 column 5
            .Cell(1, 6).Range.Text = csvData(0)(5)   'Row 1 column 6
            .Cell(1, 7).Range.Text = csvData(0)(6)   'Row 1 column 7
            .Cell(1, 8).Range.Text = csvData(0)(7)   'Row 1 column 8
            .Cell(1, 9).Range.Text = csvData(0)(8)   'Row 1 column 9
            .Cell(1, 10).Range.Text = csvData(0)(9)   'Row 1 column 10
    in Excel , Column 8,9,10 are always present, but the additional data for the same columns could be in 11,12,13.
    I have amended as above , does that look ok?

    I have adjusted this bit i believe would now be 10?

    Code:
        Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _
                                                 DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
    but this section of code i think needs amendment?, not i have tried guessing the correct numbers but no luck ..
    Code:
              r = 0
            For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                If UBound(csvData(i)) > 10 Then
                    'Split this row (i+r+1) columns 3 and 4 into multiple rows
                    .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
                    .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 9) / 2 + 1, NumColumns:=1
                End If
                For c = 8 To UBound(csvData(i)) Step 2
                    .Cell(i + r + 1, 9).Range.Text = csvData(i)(c)
                    .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 1)
                    r = r + 1
                Next
                r = r - 1
            Next

    Thank you very much for your help so far

  8. #8
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,894
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    Try this macro:
    Code:
    Public Sub Create_Table_From_Csv_File()
    
        Dim csvFile As String
        Dim FSO As Object
        Dim csvTextStream As Object
        Dim csvLines As Variant
        Dim csvData As Variant
        Dim csvTable As Table
        Dim i As Long, r As Long, c As Long
        
        csvFile = "C:\folder\path\Your csv data.csv"   'CHANGE THIS
        
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set csvTextStream = FSO.OpenTextFile(csvFile)
        csvLines = Split(csvTextStream.ReadAll, vbCrLf)
        csvTextStream.Close
        
        ReDim csvData(0 To UBound(csvLines) - 1)
        For i = 0 To UBound(csvLines) - 1
            csvData(i) = Split(csvLines(i), ",")
        Next
        
        Set csvTable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=UBound(csvLines), NumColumns:=10, _
                                  DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed)
        
        With csvTable
        
            .Cell(1, 1).Range.Text = csvData(0)(0)   'Row 1 column 1
            .Cell(1, 2).Range.Text = csvData(0)(1)   'Row 1 column 2
            .Cell(1, 3).Range.Text = csvData(0)(2)   'Row 1 column 3
            .Cell(1, 4).Range.Text = csvData(0)(3)   'Row 1 column 4
            .Cell(1, 5).Range.Text = csvData(0)(4)   'Row 1 column 5
            .Cell(1, 6).Range.Text = csvData(0)(5)   'Row 1 column 6
            .Cell(1, 7).Range.Text = csvData(0)(6)   'Row 1 column 7
            .Cell(1, 8).Range.Text = csvData(0)(7)   'Row 1 column 8
            .Cell(1, 9).Range.Text = csvData(0)(8)   'Row 1 column 9
            .Cell(1, 10).Range.Text = csvData(0)(9)  'Row 1 column 10
    
            r = 0
            For i = 1 To UBound(csvData)
                .Cell(i + r + 1, 1).Range.Text = csvData(i)(0)
                .Cell(i + r + 1, 2).Range.Text = csvData(i)(1)
                .Cell(i + r + 1, 3).Range.Text = csvData(i)(2)
                .Cell(i + r + 1, 4).Range.Text = csvData(i)(3)
                .Cell(i + r + 1, 5).Range.Text = csvData(i)(4)
                .Cell(i + r + 1, 6).Range.Text = csvData(i)(5)
                .Cell(i + r + 1, 7).Range.Text = csvData(i)(6)
                If UBound(csvData(i)) > 7 Then
                    'Split this row (i+r+1) columns 8, 9 and 10 into multiple rows
                    .Cell(i + r + 1, 8).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
                    .Cell(i + r + 1, 9).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
                    .Cell(i + r + 1, 10).Split NumRows:=(UBound(csvData(i)) - 6) / 3, NumColumns:=1
                End If
                For c = 7 To UBound(csvData(i)) Step 3
                    .Cell(i + r + 1, 8).Range.Text = csvData(i)(c)
                    .Cell(i + r + 1, 9).Range.Text = csvData(i)(c + 1)
                    .Cell(i + r + 1, 10).Range.Text = csvData(i)(c + 2)
                    r = r + 1
                Next
                r = r - 1
            Next
            
        End With
    
        MsgBox "Done!"
        
    End Sub

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    wow that's incredible thanks John. it works a treat and definately better than i managed by a mile!

    It does seem to create 3 columns and rows extra for each cell after column 10, if theres a quick fix to only create those cells past column 10 if there is data inside the next column that would be amazing - is that possible?

    if not, no worries at all, i am blown away by your excellent help.

  10. #10
    New Member montyfern's Avatar
    Join Date
    Oct 2017
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export Excel CSV to MS word with specific table format

    GENIUS! Thanks originalsauce & John_w! I had to tweak the code a bit, only have 10 columns, & kept bombing out on the last c = 7 To Unbound part, but modified to reflect splitting rows above from 11-13 & then it worked. Wow! Didn't spend too much time on this & your help's invaluable. GRAZIE

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •