Hi Paul, how can we append to an already existing excel file?
I have this code so far:
Code:
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?
Last edited by zero_coke; Mar 9th, 2011 at 06:18 PM.
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...
Last edited by zero_coke; Mar 9th, 2011 at 07:46 PM.
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.
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...
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?
Last edited by zero_coke; Mar 9th, 2011 at 08:05 PM.
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.
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.
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...
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.
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?
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?
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.
Reply With Quote
Re: export DGV to word ~ excel
