Results 1 to 4 of 4

Thread: help with logic of exporting one DGV after another to excel

  1. #1

    Thread Starter
    Frenzied Member CoachBarker's Avatar
    Join Date
    Aug 2007
    Location
    Central NY State
    Posts
    1,121

    help with logic of exporting one DGV after another to excel

    the logic of this has gotten the best of me. In my scheduling app the user creates schedules for 6 groups(RNAM, RNPM, NTechAm, NTechPM, WCAM, WCPM). The user can then preview each schedule in a DGV on a separate form. After previewing all 6 schedules they then export them one at a time to excel. The first schedule comes out fine thanks to help from.paul, what I want to do after the first schedule is done, is keep the workbook open and add a new worksheet for each remaining group(total of 6 worksheets).

    Here is the pertinent code:
    vb.net Code:
    1. fileToOpen = lblDateRange.Text
    2.             fileToOpen = fileToOpen.Replace(" ", "").Replace("-", "") & ".xls"
    3.             'for now the file path is C:\Documents and Settings\William\My Documents\
    4.             Dim filePath As String = folderToCreate
    5.             Dim fullFilePath As String = filePath & fileToOpen
    6.             ' check to see if the file exists, if not create a new file
    7.             If Not File.Exists(fullFilePath) Then
    8.                 ' declared globally
    9.                 'Dim xlApp As excel.Application
    10.                 'Dim xlWorkBook As excel.Workbook
    11.                 'Dim xlWorkSheet As excel.Worksheet
    12.                 Dim misValue As Object = System.Reflection.Missing.Value
    13.  
    14.                 xlApp = New excel.ApplicationClass
    15.                 xlWorkBook = xlApp.Workbooks.Add(misValue)
    16.                 xlWorkSheet = DirectCast(xlWorkBook.Sheets("sheet1"), excel.Worksheet)
    17.                 xlApp.Visible = True
    18.  
    19.                 ' Whole bunch of formatting goes here then
    20.  
    21.                 ' name for the worksheet
    22.                 wsName = lblAmPmPosition.Text
    23.                 wsName = wsName.Replace(" ", "").Replace("'", "")
    24.                 ' name for the workbook
    25.                 wbName = lblDateRange.Text
    26.                 wbName = wbName.Replace(" ", "").Replace("-", "") & ".xls"
    27.                 xlWorkSheet.Name = wsName
    28.                 xlWorkBook.SaveAs(folderToCreate & wbName)
    29.  
    30.                 ' if the file exists open the file and add the rest of the worksheets
    31.                 ' by exporting them to the existing workbook
    32.  
    33.                 ElseIf File.Exists(fullFilePath) Then
    34.                 'MessageBox.Show("it exists " & fullFilePath)
    35.                 ' this line is wrong becasue it opens a new instance of excel witha new workbook.
    36.                 ' have to get the correct code to fix it
    37.                 Dim xlApp As excel.Application = DirectCast(CreateObject("Excel.Application"), excel.Application)
    38.                Dim xlWorkBook As excel.Workbook = xlApp.Workbooks.Open(fullFilePath)
    39.                 Dim xlWorkSheet2 As excel.Worksheet
    40.                 xlWorkSheet2 = CType(xlWorkBook.Worksheets.Add(), excel.Worksheet)
    41.                 xlWorkSheet2.Name = "test"
    42.                 Dim misValue As Object = System.Reflection.Missing.Value
    43.                 xlWorkBook = xlApp.Workbooks.Add(misValue)
    44.                 xlApp.Visible = True
    45.  
    46.                ' more formatting
    47.  
    48.                ' if the worksheet doesn't exist create it
    49.                 wsName = lblAmPmPosition.Text
    50.                 wsName = wsName.Replace("/", "")
    51.                 wbName = fileToOpen
    52.                 wbName = wbName.Replace(" ", "").Replace("-", "")
    53.  
    54.                 ' check to see if worksheet already exists
    55.                 ' code provided by koolsid from forum post
    56.                 ' http://www.vbforums.com/showthread.php?t=509588&highlight=excel+worksheet+exists
    57.                 Dim existingWS As String, Found As Boolean, i As Integer
    58.                 Found = False
    59.                 'Replace Sheet3 with the name of the relevant sheet
    60.                 existingWS = wsName
    61.                 'loop thru the names of the sheet
    62.                 For i = 1 To xlApp.ActiveWorkbook.Sheets.Count
    63.                     If existingWS = xlApp.ActiveWorkbook.Sheets(i).ToString Then
    64.                         Found = True
    65.                         Exit For
    66.                     Else
    67.                         Found = False
    68.                     End If
    69.                 Next i
    70.                 'Display Status
    71.                 If Found = False Then
    72.                     MsgBox("Sheet Doesn't exist")
    73.                     xlWorkSheet2.Name = wsName
    74.                 Else
    75.                     'MsgBox("Sheet exists")
    76.                 End If
    77.                 'xlWorkSheet2.Name = wsName
    78.                 xlWorkBook.Save()
    79. end if
    80.  
    81.             'releasing object references
    82.             releaseObject(xlApp)
    83.             releaseObject(xlWorkBook)
    84.             releaseObject(xlWorkSheet)
    85.             releaseObject(xlWorkSheet2)
    86.  
    87.             xlWorkBook = Nothing
    88.             xlWorkBook = Nothing
    89.             xlApp.Quit()
    90.             xlApp = Nothing

    Should I do away with checking if the file exists and just create the workbook and keep it open and then add all 6 worksheets by exporting them 1 at a time.
    Thanks
    CoachBarker

    Code Bank Contribution
    Login/Manage Users/Navigate Records
    VB.Net | C#

    Helpful Links: VB.net Tutorial | C Sharp Tutorial | SQL Basics

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: help with logic of exporting one DGV after another to excel

    Over in the CodeBank, I posted an Excel class that I use to dump datatables and datareaders to Excel. You might find the code in there of interest. It's all late bound, so it isn't version specific for Excel, and should work for 2003-2010, though I haven't specifically tested the code against Excel 2010, since I don't even have a copy of that one, yet.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Frenzied Member CoachBarker's Avatar
    Join Date
    Aug 2007
    Location
    Central NY State
    Posts
    1,121

    Re: help with logic of exporting one DGV after another to excel

    will take a look at it and see if it will help. How about as far as leaving the excel workbook open once it is created, and adding each worksheet until it is complete. Any thoughts?
    Thanks
    CoachBarker

    Code Bank Contribution
    Login/Manage Users/Navigate Records
    VB.Net | C#

    Helpful Links: VB.net Tutorial | C Sharp Tutorial | SQL Basics

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: help with logic of exporting one DGV after another to excel

    That's pretty much what I did in that class. The workbook gets opened right off, then data is added to new worksheets or existing ones. So I guess you would say that I like that approach.
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width