# VBForums CodeBank > CodeBank - Visual Basic .NET >  export DGV to word ~ excel

## .paul.

vb2008 export DGV to word ~ excel

(see also: export listview to word table: http://www.vbforums.com/showthread.php?t=624967)

----------


## zero_coke

Hi Paul, how can we append to an already existing excel file?

I have this code so far:



```
If Not File.Exists(Application.StartupPath & "\" & "test" & ".xlsx") Then

<insert .Paul.'s original DVG to excel code here>

'I also set visibility = false because I want my program to auto-save without user interaction

'plus I added

xlWorkBook.SaveAs(Application.StartupPath & "\" & "test" & ".xlsx") 
           
 'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing

DataGridView1.Rows.Clear() 'clear our datagridview table 

else

<insert .Paul.'s original DVG to excel code with these following changes:>

Dim xlApp as excel.application = new excel.Application

Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(Application.StartupPath & "\" & "test" & ".xlsx")

Dim xlWorkSheet As excel.Worksheet   'how do I select the current sheet on this file?

'And this line I modified the range to:
Dim range As excel.Range = xlWorkSheet.UsedRange

'And then I have this at the end to save my excel file

xlWorkBook.Save() 

'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing

DataGridView1.Rows.Clear() 'clear our datagridview table when user clicks save
```

Am I missing anything or do I need to do something else? Would this work?

----------


## .paul.

what do you want to append to your existing worksheet?

----------


## zero_coke

Whenever my users click "save" I clear the datagridview contents. Then:

Whenever the user's click "Save" again, program should check if there is an existing excel file named "test" and append to the end of it the current DGV contents. Then the DGV is cleared again and basically this is repeated everytime the user clicks "Save". 

However, if no such file named "test" exists then it should just make a new excel file and paste the DGV contents into it. 

That's all. So if certain file exists then append to it (excluding the column header names) and if doesn't exist then paste all of the DGV contents to a new file.

Does that make any sense? Do you have any other questions? I hope this made it clear...

----------


## .paul.

well you'd use similar code to my example, but you'd open the existing workbook, set xlWorkSheet to "sheet1" (same worksheet as originally used), copy your dgv + set the range allowing for the previously used cells, then paste the new information from your dgv.

----------


## zero_coke

Yeah I think the code I pasted above does that when I made the appropriate changes but it doesn't work for some reason....I don't know what I'm missing...

----------


## .paul.

ok post your complete code + i'll tell you where you're going wrong

----------


## zero_coke

```

Private Sub exportToExcel(ByVal dgv As DataGridView)
        If Not File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then
            Dim xlApp As excel.Application = New excel.Application
            Dim xlWorkBook As excel.Workbook
            Dim xlWorkSheet As excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)

            'xlApp.Visible = True

            Dim headers = (From ch In dgv.Columns _
                            Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                            Select header.Value).ToArray()
            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

            Dim items() = (From r In dgv.Rows _
                    Let row = DirectCast(r, DataGridViewRow) _
                    Where Not row.IsNewRow _
                    Select (From cell In row.Cells _
                        Let c = DirectCast(cell, DataGridViewCell) _
                        Select c.Value).ToArray()).ToArray()

            Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next
            table = table.TrimEnd(CChar(Environment.NewLine))
            Clipboard.SetText(table)

            Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray

            Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)

            range.Select()
            xlWorkSheet.Paste()

            range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
            With range.Borders(excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With

            xlWorkBook.SaveAs(Application.StartupPath & "\" & "TEST" & ".xlsx") 'save our workbook

            'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing

            TestDataGridView.Rows.Clear() 'clear the log

        ElseIf File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then
            MsgBox("bump, it exists")
            Dim xlApp As excel.Application = New excel.Application
            Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(Application.StartupPath & "\" & "TEST" & ".xlsx")
            Dim xlWorkSheet As excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)

            'xlApp.Visible = True

            Dim headers = (From ch In dgv.Columns _
                            Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                            Select header.Value).ToArray()
            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)

            Dim items() = (From r In dgv.Rows _
                    Let row = DirectCast(r, DataGridViewRow) _
                    Where Not row.IsNewRow _
                    Select (From cell In row.Cells _
                        Let c = DirectCast(cell, DataGridViewCell) _
                        Select c.Value).ToArray()).ToArray()

            Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next
            table = table.TrimEnd(CChar(Environment.NewLine))
            Clipboard.SetText(table)

            Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray

            Dim range As excel.Range = xlWorkSheet.UsedRange

            range.Select() '? should have this or not? is it over-writing?
            xlWorkSheet.Paste()

            range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
            With range.Borders(excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With

            xlWorkBook.Save()

            'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing

            TestDataGridView.Rows.Clear() 'clear the log
        Else
            Debug.Print("What the hell? Either exists or not lol")
        End If
    End Sub
```

Can you also show me how to not write the headers when appending?

----------


## .paul.

try this:


vb Code:
Private Sub exportToExcel(ByVal dgv As DataGridView)
        If Not File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then
            Dim xlApp As excel.Application = New excel.Application
            Dim xlWorkBook As excel.Workbook
            Dim xlWorkSheet As excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
             xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
             'xlApp.Visible = True
             Dim headers = (From ch In dgv.Columns _
                            Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                            Select header.Value).ToArray()
            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
             Dim items() = (From r In dgv.Rows _
                    Let row = DirectCast(r, DataGridViewRow) _
                    Where Not row.IsNewRow _
                    Select (From cell In row.Cells _
                        Let c = DirectCast(cell, DataGridViewCell) _
                        Select c.Value).ToArray()).ToArray()
             Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine
            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next
            table = table.TrimEnd(CChar(Environment.NewLine))
            Clipboard.SetText(table)
             Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
             Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)
             range.Select()
            xlWorkSheet.Paste()
             range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
            With range.Borders(excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
             xlWorkBook.SaveAs(Application.StartupPath & "\" & "TEST" & ".xlsx") 'save our workbook
             'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing
             TestDataGridView.Rows.Clear() 'clear the email log
         ElseIf File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then
            MsgBox("bump, it exists")
            Dim xlApp As excel.Application = New excel.Application
            Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(Application.StartupPath & "\" & "TEST" & ".xlsx")
            Dim xlWorkSheet As excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
             xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
             'xlApp.Visible = True
             Dim headers = (From ch In dgv.Columns _
                           Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _
                           Select header.Value).ToArray()
            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)
             Dim items() = (From r In dgv.Rows _
                    Let row = DirectCast(r, DataGridViewRow) _
                    Where Not row.IsNewRow _
                    Select (From cell In row.Cells _
                        Let c = DirectCast(cell, DataGridViewCell) _
                        Select c.Value).ToArray()).ToArray()
             Dim table As String
            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next
            table = table.TrimEnd(CChar(Environment.NewLine))
            Clipboard.SetText(table)
             Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray
             Dim range As excel.Range = xlWorkSheet.Range("B" & (xlWorkSheet.UsedRange.rows.count + 1).tostring & ":" & alphabet(headerText.Length) & (xlWorkSheet.UsedRange.rows.count + items.Length).ToString)
             range.Select() '? should have this or not? is it over-writing?
            xlWorkSheet.Paste()
             range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone
            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone
            With range.Borders(excel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeTop)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlEdgeRight)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlMedium
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideVertical)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)
                .LineStyle = excel.XlLineStyle.xlContinuous
                .ColorIndex = 1 'black
                .TintAndShade = 0
                .Weight = excel.XlBorderWeight.xlThin
            End With
             xlWorkBook.Save()
             'releasing object references
            xlWorkBook = Nothing
            xlWorkBook = Nothing
            xlApp.Quit()
            xlApp = Nothing
             TestDataGridView.Rows.Clear() 'clear the email log
        Else
            Debug.Print("What the hell? Either exists or not lol")
        End If
    End Sub

----------


## zero_coke

I'll try this and let you know. 

In the mean time, do you know how to make the columns in excel file auto-size so I don't have to manually shift the cells width since they're too small and text overwrites onto next cell if you know what I mean.

----------


## .paul.

add this after the other range formatting code:


vb Code:
range.Columns.AutoFit()

----------


## zero_coke

Hey Paul, can you please upload the class file? I just copied and pasted your code and went through removing all the line numbers manually and still I'm getting a whole bunch of errors (the squiggly blue line under variables).

Also, can you please make that autofit adjustment? I'm not too sure where you mean by "other range" code.

----------


## .paul.

i didn't make a class file. those squiggly lines probably mean you don't have the right imports in your class. which version of windows are you using? i can copy that code without the line numbers...

----------


## zero_coke

Alright, that fixed it. First I removed the line numbers manually and then I made a program to remove the line numbers using regex I guess that messed it up.

So where exactly do I put the range.Columns.AutoFit() ?

EDIT: Nevermind, just saw it. I was checking the top the whole time meanwhile it was at the bottom. 

Thanks Paul! I'll try this out, hopefully works without flaws. I'll keep you updated.

----------


## zero_coke

Uh oh...it is not appending...What do I do now Paul?

----------


## .paul.

ok. can you upload your project + i'll debug it.

----------


## zero_coke

I'm sorry Paul, I can't upload the project. It's just some agreement that I have with my client and partners. However, I greatly appreciate your help. Would you like me to make you a test project and see if it appends?

----------


## .paul.

actually i think i found it. in the append part remove this line:


vb Code:
xlWorkBook = xlApp.Workbooks.Add(misValue)

----------


## zero_coke

When I do that it I can't open the excel file at all. Just opens and closes really fast.

----------


## .paul.

ok. post the code again

----------


## zero_coke

vb Code:
Private Sub exportToExcel_new(ByVal dgv As DataGridView)        If Not File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then            Dim xlApp As excel.Application = New excel.Application            Dim xlWorkBook As excel.Workbook            Dim xlWorkSheet As excel.Worksheet            Dim misValue As Object = System.Reflection.Missing.Value             xlWorkBook = xlApp.Workbooks.Add(misValue)            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)             Dim headers = (From ch In dgv.Columns _                            Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _                            Select header.Value).ToArray()            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)             Dim items() = (From r In dgv.Rows _                    Let row = DirectCast(r, DataGridViewRow) _                    Where Not row.IsNewRow _                    Select (From cell In row.Cells _                        Let c = DirectCast(cell, DataGridViewCell) _                        Select c.Value).ToArray()).ToArray()             Dim table As String = String.Join(vbTab, headerText) & Environment.NewLine             For Each a In items                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)                table &= String.Join(vbTab, t) & Environment.NewLine            Next             table = table.TrimEnd(CChar(Environment.NewLine))            Clipboard.SetText(table)             Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray             Dim range As excel.Range = xlWorkSheet.Range("B2:" & alphabet(headerText.Length) & (items.Length + 2).ToString)             range.Select()            xlWorkSheet.Paste()             range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone            With range.Borders(excel.XlBordersIndex.xlEdgeLeft)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeTop)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeRight)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlInsideVertical)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlThin            End With            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlThin            End With             range.Columns.AutoFit() 'auto-size the columns so the columns look nice            xlWorkBook.SaveAs(Application.StartupPath & "\" & "TEST" & ".xlsx") 'save our workbook             'releasing object references            xlWorkBook = Nothing            xlWorkBook = Nothing            xlApp.Quit()            xlApp = Nothing             DataGridView1.Rows.Clear() 'clear the log         ElseIf File.Exists(Application.StartupPath & "\" & "TEST" & ".xlsx") Then            MsgBox("bump, it exists")            Dim xlApp As excel.Application = New excel.Application            Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(Application.StartupPath & "\" & "TEST" & ".xlsx")            Dim xlWorkSheet As excel.Worksheet            'Dim misValue As Object = System.Reflection.Missing.Value             'xlWorkBook = xlApp.Workbooks.Add(misValue)            xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)             Dim headers = (From ch In dgv.Columns _                           Let header = DirectCast(DirectCast(ch, DataGridViewColumn).HeaderCell, DataGridViewColumnHeaderCell) _                           Select header.Value).ToArray()            Dim headerText() As String = Array.ConvertAll(headers, Function(v) v.ToString)             Dim items() = (From r In dgv.Rows _                    Let row = DirectCast(r, DataGridViewRow) _                    Where Not row.IsNewRow _                    Select (From cell In row.Cells _                        Let c = DirectCast(cell, DataGridViewCell) _                        Select c.Value).ToArray()).ToArray()             Dim table As String = String.Empty             For Each a In items                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)                table &= String.Join(vbTab, t) & Environment.NewLine            Next             table = table.TrimEnd(CChar(Environment.NewLine))            Clipboard.SetText(table)             Dim alphabet() As Char = "abcdefghijklmnopqrstuvwxyz".ToUpper.ToCharArray            Dim range As excel.Range = xlWorkSheet.Range("B" & (xlWorkSheet.UsedRange.Rows.Count + 1).ToString & ":" & alphabet(headerText.Length) & (xlWorkSheet.UsedRange.Rows.Count + items.Length).ToString)             range.Select() '? should have this or not? is it over-writing?            xlWorkSheet.Paste()             range.Borders(excel.XlBordersIndex.xlDiagonalDown).LineStyle = excel.XlLineStyle.xlLineStyleNone            range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone             With range.Borders(excel.XlBordersIndex.xlEdgeLeft)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeTop)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeBottom)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlEdgeRight)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlMedium            End With            With range.Borders(excel.XlBordersIndex.xlInsideVertical)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlThin            End With            With range.Borders(excel.XlBordersIndex.xlInsideHorizontal)                .LineStyle = excel.XlLineStyle.xlContinuous                .ColorIndex = 1 'black                .TintAndShade = 0                .Weight = excel.XlBorderWeight.xlThin            End With             range.Columns.AutoFit() 'auto-size the columns so the excel columns look nice             xlWorkBook.Save() 'save our workbook              'releasing object references            xlWorkBook = Nothing            xlWorkBook = Nothing            xlApp.Quit()            xlApp = Nothing             DataGridView1.Rows.Clear() 'clear the log        Else            Debug.Print("What the hell? Either exists or not lol")        End If    End Sub

----------


## .paul.

it does work. you don't see it because you removed:


vb Code:
xlApp.Visible = True

+ you close excel after the code has run, which is almost instantly

----------


## zero_coke

Yeah but it should at least be saved right? I mean, i have 


vb Code:
xlWorkBook.Save() 'save our workbook

At the end so would it matter if the visibility was on or off? And I don't want the visibility to be on, I just want the program to save it automatically without showing the user. Do I have to turn visibility on?

----------


## .paul.

have you tried opening the file in excel? it works + saves whether you make excel visible or not

----------


## zero_coke

Here's my tester project. It doesn't save for some reason. Can you have a look at it please Paul?

Thanks

It's 4 am in the morning here. I'll be back tomorrow Paul. Thanks!

----------


## .paul.

you didn't remove these lines from the append part as i told you to:


vb Code:
Dim misValue As Object = System.Reflection.Missing.Value
 xlWorkBook = xlApp.Workbooks.Add(misValue)

if you remove these lines it works

----------


## .paul.

also, before the 2nd range.columns.autofit, put this:


vb Code:
range = xlWorkSheet.Range("B2:" & alphabet(xlWorkSheet.UsedRange.Columns.Count) & (xlWorkSheet.UsedRange.Rows.Count).ToString)

----------


## zero_coke

Yay! Thanks Paul it works now! It appends exactly the way I wanted to!

So Paul, final question if you do not mind:

How can you get the application to overwrite the existing file automatically? When I click save I see this:



When you click "no" my debugger points out an error on some line because I think the user must click Yes or the append part of the code results in an error.

I don't even know why Excel is prompting to replace the file when you're just opening it, writing some data do it and saving it.

----------


## .paul.

i'm not sure about that. that didn't happen when i ran it.
start a new thread for that...

----------


## zero_coke

Hey Paul,

Excel doesn't prompt me anymore. I made a stupid mistake and added


vb Code:
xlapp.SaveWorkspace()

So I removed that ^ and it works now.

Just one question: When a cell is empty, the code gives me an error here:

ERROR: Object reference not set to an instance of an object.

Error location:

            For Each a In items
                Dim t() As String = Array.ConvertAll(a, Function(v) v.ToString)
                table &= String.Join(vbTab, t) & Environment.NewLine
            Next

And it highlights the *Function(v) v.ToString)* part.

You can check if a cell is empty by:



```
String.IsNullOrEmpty(CStr(DataGridView1.Item(i, x).Value))
```

But I don't know where to put it. Seems like you're using an array function...

----------


## .paul.

this works:

Dim t() As String = Array.ConvertAll(a, Function(v) if(not v is nothing, v.ToString, ""))

----------


## zero_coke

Awesome, that works  :Smilie: 

I want to ask one more question. May I? I really don't want to bother you anymore. You've done so much.

----------


## .paul.

what's the question?

----------


## zero_coke

The excel application doesn't close. When I go to task manager, its still there so how can I close it properly?

I could use



```
Dim proc As System.Diagnostics.Process

For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
proc.Kill()
Next
```

But I don't think this is the right way to end it right?

----------


## .paul.

try: 
xlworkbook.close
xlapp.close (or dispose)

----------


## Verra

Hi Paul, 

I'm having trouble making the gridview go to a specific bookmark, it's probably my ignorance. How would I go about doing this?

Thanks,

Verra

----------


## .paul.

my example uses a new word doc with no bookmarks set. i'm assuming you're using an existing word doc with predefined bookmarks?

----------


## Verra

Yes, I've got a few data grid views which will display in a report in a word document; all the bookmarks are pre-defined.

----------


## .paul.

could you post the word doc you're using + clarify which dgv should go where?

----------


## Verra

The word document I'm using is HUGE, approx 200 pages. Here's an example of part of it:

"Blah blah blah assets etc (talks about assets)

[Bookmark here for Assets Summary]

more text here about other things

-a few pages later- 

text about liabilities

[Bookmark for Liabilities Summary]

then more text"

The bookmarked grid views don't get anymore complex than that, just sticking them after or before paragraphs and headings.

----------


## Verra

http://social.msdn.microsoft.com/For...d-9bccf025f007 - I found this but when I try and adapt it to my code the blue underline appears, I'm thinking I don't have the correct references added, but I wouldn't have a clue which ones I need.

----------


## .paul.

try this:

----------


## Verra

That works! Thank-you very much  :Smilie:

----------


## .paul.

if you're not sure about the references, you can check them in the example. it uses Microsoft Word 12.0 Object Library which you'll find on the references COM tab

----------


## Verra

Hi Paul, 

*Nevermind, I just figured out why it wasn't working. It doesn't like being split up onto a couple of lines.*

I've gone through and added more grid views to the code you've given me, and I'm coming up with an error which I'm sure the answer to is simple; but my thinking currently isn't at its best haha.

It's saying that 'dgvs' isn't declared, when it definitely is. Here's the part where the object is declared:



```
'Bookmarks are "grid1" (relates to DataGridView1), + "grid2" (relates to DataGridView2)
        Dim dgvs(,) As Object = {{dgAssetsSummary, "AssetsSummary"}, {dgLiabilitiesSummary, "LiabilitiesSummary"}, {dgAssetsFull, "AssetsFull"}, & _ 
                                 {dgLiabilitiesFull, "LiabilitiesFull"}, {dgPAEFull, "PAEFull"}, {dgGoalsMediumTerm, "GoalsMediumTerm"}, & _
                                 {dgGWStopped, "GWStopped"}, {dgGWAdded, "GWAdded"}, {dgFLSum, "FLSums"}}
```

Sorry to trouble you about this again, I just can't see why it isn't working.

Thankyou,

Verra

----------


## SC01

I'm thinking in adapt this example to export one list to excel but i get the following error:

haven't changed a thing. only saw the dependencies if the excel dependence was at fault but it was there. the 'export to word' button works perfectly

----------


## redseujac

Hi .Paul

Two more questions about how to export DGV to Word.
1) Your sample code exports the DGV to a *new* Word document. What's the code for exporting the DGV to a document that's *opened* in Word, I mean append the DGV to the end of that open Word document ? Attention: the document must not be opened, it IS open in Word.
2) What is the code to align the text in the Word table cells to the right instead of left ?
I applied successfully your sample code, but I couldn't figure out how to do what I asked in the previous questions.
Thanks
Jacques

----------


## .paul.

the problem with a document that IS open in Word is finding a way to refer to it.

2/ 'align right
    oTable.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight

----------


## redseujac

Thank you paul for your help.

I already applied sucessfully your answer to my second question.

As to the first problem - copying the DGV to an opened Word document - I have the following code in VBA to copy the contents of a worksheet to an open Word document and it's working like a charm. Unfortunately I'm not able to write similar code in Visual Basic.



```
Sub CopyFromExcel2VisibleOpenWordDoc()

    Dim WrdApp As Object
    Dim WrdDoc As Object

    Dim LastRow As Long
    Dim lRow As Long
    Dim iCol As Integer
    LastRow = 0

    On Error Resume Next

    Set WrdApp = GetObject(, "Word.Application")

    If WrdApp Is Nothing Then
        MsgBox "MS Word is not available!" _
               & vbCrLf & "Start MS Word" _
               & vbCrLf & "and put the cursor at the right place" _
               & vbCrLf & "in the right document!", vbExclamation
    End If

    For iCol = 1 To 6
        lRow = Cells(65536, iCol).End(xlUp).Row
        If lRow > LastRow Then LastRow = lRow
    Next iCol

    'Copy from last row to begin table
    ActiveSheet.Range(Cells(1, "A"), Cells(LastRow, "F")).Copy

    With WrdApp
        .Visible = True
        .Activate

        Set WrdDoc = WrdApp.ActiveDocument
        'WrdDoc.Show

        With WrdDoc
            .Activate
            .Application.Selection.TypeParagraph
            .Application.Selection.Paste
        End With

    End With

    Application.CutCopyMode = False

End Sub
```

Do you see something in that code that gives you an idea?

----------


## .paul.

ok. using my code, change:


vb.net Code:
' Create Word Application
Dim oWord As Word.Application = DirectCast(CreateObject("Word.Application"), Word.Application)

to:


vb.net Code:
Dim oWord As Word.Application
Try
    ' Get Word Application
    oWord = DirectCast(GetObject(, "Word.Application"), Word.Application)
Catch ex As Exception
    MsgBox("Can't find Word Document", MsgBoxStyle.Information)
    Return
End Try

----------


## redseujac

Thank you paul.

Unfortunately the DGV is still added to a NEW opened Word document. The DGV is NOT added to the Word document that is already open.

Maybe the problem is due to this code in your code:



```
Dim oDoc As Word.Document = oWord.Documents.Add
```

----------


## .paul.

ok. post the code you tried. i'll see if i can find the error.

----------


## .paul.

> Maybe the problem is due to this code in your code:
> 
> 
> 
> ```
> Dim oDoc As Word.Document = oWord.Documents.Add
> ```


yes, almost definitely. i don't know the exact code you need to use until i've tried the code you're using in vb

----------


## .paul.

Dim oDoc As Word.Document = oWord.ActiveDocument

----------


## redseujac

referring to the ActiveDocument did the trick finally  :Thumb: 

Thank you veru much, paul.

Now we have 2 sample codes: 1 to copy a DGV to a new Word document and 1 to copy it (append it) to an open Word document.

Nice  :Cool:

----------


## .paul.

> referring to the ActiveDocument did the trick finally 
> 
> Thank you veru much, paul.
> 
> Now we have 2 sample codes: 1 to copy a DGV to a new Word document and 1 to copy it (append it) to an open Word document.
> 
> Nice


did you see the insert into bookmarks example in post #42?

----------


## redseujac

Yes, I did paul, but I couldn't use the code in that example.

But you are right indeed: there are not 2 but 3 examples. My fault  :Blush:

----------


## .paul.

> Yes, I did paul, but I couldn't use the code in that example.
> 
> But you are right indeed: there are not 2 but 3 examples. My fault


no problem... that's the whole point of this thread, to answer questions about exporting dgvs to Word + Excel :Big Grin:

----------


## redseujac

paul

After the DGV has been exported to MS Word table, I experience an annoying problem: the content in the Word table cells is not formatted as I want it to be.

After the date, the time is added ("00:00:00") and the numbers have too many decimal places (instead of 2).

I have tried to resolve this problem by first converting the DGV cells values using standard numeric format strings (ToString method) before pasting them to the Word table, but I am not very happy with this method, because then my DGV values aren't usable anymore (converted to strings) and I am forced to remove them all from the DGV and to enter all the data again, if I want to calculate results once more.

I guess it would be better to format the Word table cells content directly after pasting the DGV to Word, but honestly I don't know how to manage that.

I would be grateful  if you could give me some code to insert in your existing code to format the Word table cells content for dates and numbers the way the dates don't have the time added and the decimal places for the numbers are limited to 2.

Thanks.

----------


## redseujac

Hello paul

1) Still no answer possible to my post #59?

2) How to adapt your code to copy NOT the ENTIRE DataGridView to Word, but only the SELECTED area (with mouse e.g.) of the DataGridView, thus the selected columns and rows?

Thanks & best regards

Jacques

----------


## .paul.

1/ i'm not sure of how to tackle that. it'd probably be best to format the dateTime in the items array after reading the dgv
2/ that's a whole new program. there are plenty of examples either here on vbforums or you can find them through google. sorry i couldn't be more help.

----------


## chdboy

Here are the errors I get from this code

----------


## chdboy

ok I got it 

change 


```
Excel.ApplicationClass xlapp = new Excel.ApplicationClass();
```

to this


```
Excel.Application xlapp = new Excel.Application();
```

----------


## .paul.

you're not using excel 2007 then... sorry i didn't get back to you earlier.

----------


## chdboy

Not a problem..yeah I'm using 2010

----------


## LiamC

HI,

I'm posting the questions I have in this topic as I'm trying to adapt the code paul has provided in the zip file.

I've got VS2010 and MS Office 2010 as environment. I'm only using the Export to EXCEL part.

My question: I'd like to be able to chose the worksheet I'm pasting the selection on.

I've already done this adjustment from your code to be able to chose the starting cell:



```
Dim range As Excel.Range= xlWorkSheet.Range("I62").Resize(items.length,headerText.length)
```

Which doesn't use the alphabet() array anymore.

As to which sheet is concerned I tried this:



```
Dim xlWkSheet As Excel.Worksheet=Ctype (xlWorkBook.Worksheets(Index:=1), excel.Worksheet)
```

It has worked, but when I tried to change the "Index" it caused an error and range.select() was highlighted. 

How would one chose what worksheet is the target ?

----------


## Nitesh

Hey .paul.,

I've just come across your code and it work's great. I am trying this using late binding. Please let me know how I would do the following using late binding:




> range.Borders(excel.XlBordersIndex.xlDiagonalUp).LineStyle = excel.XlLineStyle.xlLineStyleNone

----------


## .paul.

try this:



```
xlApp = CreateObject("Excel.Application")
...
range.Borders(xlApp.XlBordersIndex.xlDiagonalUp).LineStyle = xlApp.XlLineStyle.xlLineStyleNone
```

----------


## Nitesh

Thanks. I get this error:




> Public member 'XlBordersIndex' on type 'ApplicationClass' not found.


on this line of code:




> objRange.Borders(xlApp.XlBordersIndex.xlDiagonalUp).LineStyle = xlApp.XlLineStyle.xlLineStyleNone

----------


## .paul.

all of the members of the XlBordersIndex enum have an integer value, as do the members of the XlLineStyle enum, + the XlBorderWeight enum.

----------


## Nitesh

hey .paul.

another question. I have a datagridview combobox column. When I export to Excel the Integer value of the combo is printed. How do I go about getting the text value for combobox columns in my export code?

----------

