AutoFit not working in vb.net when creating a xls

  • Thread starter ljhopkins_LOST and Cant find My Way
  • Start date
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()
 
T

Tom Ogilvy

oSheet.Autofit
oSheet.protect("mypassword")
oBook.SaveAs(sSampleFolder & "Book1.xls")

Hopefully you don't have merged cells.
 
L

ljhopkins_LOST and Cant find My Way

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")
 
T

Tom Ogilvy

osheet.Columns.Autofit

was what I intended - not sure how the Columns part got left out.

I definitely wouldn't loop through the columns.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top