| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFit not working in vb.net when creating a xls
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() |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFit not working in vb.net when creating a xls
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() |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFit not working in vb.net when creating a xls
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() |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFit not working in vb.net when creating a xls
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() |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel autofit row height not working | Excel Worksheet Functions | |||
| Excel 2007 AutoFit Row Height not working | Excel Discussion (Misc queries) | |||
| Autofit for row height is not working. Why? | Excel Discussion (Misc queries) | |||
| Wrap text and row autofit not working in Excel | Excel Discussion (Misc queries) | |||
| format-row-autofit not working | Excel Worksheet Functions | |||
Linear Mode
