+ Reply to Thread
Results 1 to 4 of 4

AutoFit not working in vb.net when creating a xls

  1. #1
    ljhopkins_LOST and Cant find My Way
    Guest

    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. #2
    Tom Ogilvy
    Guest

    RE: 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. #3
    ljhopkins_LOST and Cant find My Way
    Guest

    RE: 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. #4
    Tom Ogilvy
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1