osheet.Columns.Autofit
was what I intended - not sure how the Columns part got left out.
I definitely wouldn't loop through the columns.
--
Regards,
Tom Ogilvy
"ljhopkins_LOST and Cant find My Way" wrote:
vb.net puts oSheet.Autofit()
and it blows up
I did do this and it worked
Dim ColX As Integer = 1
Dim oCol As Object
For ColX = 1 To colcount
oCol = oSheet.cells.item(1, ColX).EntireColumn.autofit
Next
oSheet.protect("mypassword")
"Tom Ogilvy" wrote:
oSheet.Autofit
oSheet.protect("mypassword")
oBook.SaveAs(sSampleFolder & "Book1.xls")
Hopefully you don't have merged cells.
--
Regards,
Tom Ogilvy
"ljhopkins_LOST and Cant find My Way" wrote:
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()