L
ljhopkins_LOST and Cant find My Way
I have an application that pulls the data from a datagridview and creates an
xls
using the following code. I need to set the col with to autofit and then
protect all col
but S,T,U. but I cant get the autofit to work or to set width... Help Please
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
nTotalRowCount = rowcount
nCurrentRowCount = 0
nRowPointer = 0
'------------end the row count
oInsertRange = oSheet.Range("A2")
Do While nCurrentRowCount < nTotalRowCount
nRowsInArray = nTotalRowCount - nCurrentRowCount
If nRowsInArray > 20000 Then
nCurrentRowCount = nCurrentRowCount + 20000
nRowsInArray = 20000
nRowPointer = nCurrentRowCount - nRowsInArray
Else
nCurrentRowCount = nTotalRowCount
End If
ReDim DataArray(nRowsInArray - 1, colcount)
For r = 0 To nRowsInArray - 1
For c = 0 To colcount - 1
DataArray(r, c) = DataGridView1(c, nRowPointer +
r).Value.ToString
Next
Next
oInsertRange = oInsertRange.Resize(nRowsInArray, colcount)
oInsertRange.Value = DataArray
oInsertRange = oInsertRange.Offset(nRowsInArray, 0)
Loop
oExcel.Visible = True
''''''''''''Save the Workbook and quit Excel.
oRange = oSheet.range("S1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oRange = oSheet.range("T1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oRange = oSheet.range("U1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oSheet.protect("mypassword")
oBook.SaveAs(sSampleFolder & "Book1.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
xls
using the following code. I need to set the col with to autofit and then
protect all col
but S,T,U. but I cant get the autofit to work or to set width... Help Please
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
nTotalRowCount = rowcount
nCurrentRowCount = 0
nRowPointer = 0
'------------end the row count
oInsertRange = oSheet.Range("A2")
Do While nCurrentRowCount < nTotalRowCount
nRowsInArray = nTotalRowCount - nCurrentRowCount
If nRowsInArray > 20000 Then
nCurrentRowCount = nCurrentRowCount + 20000
nRowsInArray = 20000
nRowPointer = nCurrentRowCount - nRowsInArray
Else
nCurrentRowCount = nTotalRowCount
End If
ReDim DataArray(nRowsInArray - 1, colcount)
For r = 0 To nRowsInArray - 1
For c = 0 To colcount - 1
DataArray(r, c) = DataGridView1(c, nRowPointer +
r).Value.ToString
Next
Next
oInsertRange = oInsertRange.Resize(nRowsInArray, colcount)
oInsertRange.Value = DataArray
oInsertRange = oInsertRange.Offset(nRowsInArray, 0)
Loop
oExcel.Visible = True
''''''''''''Save the Workbook and quit Excel.
oRange = oSheet.range("S1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oRange = oSheet.range("T1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oRange = oSheet.range("U1").entireColumn
oRange.locked = False
oRange.interior.colorindex = 36
oSheet.protect("mypassword")
oBook.SaveAs(sSampleFolder & "Book1.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()