What is causing a casting error when copying worksheet pages

Try using Sheets instead of Worksheets.

That's all I can suggest right now without seeing the rest of the code - like I said you seem to be calling other subs/methods/whatever.

I'm also wondering why after creating the new worksheet with GenerateWorksheet you then appear to be saving the worksheet.
It might be an idea to pass the workbook you've created at the start of the code as well as the data pair.
Sorry if this isn't helping much but I'm just going on what's been posted so far.
I'm currently trying to create a similar project to see if I can find any clues.

Hello,
Here is some additional information.  I will post the complete code tomorrow, when I get to work.
Originally GenerateWorksheet was a subroutine that populates an Excel template and allows users to save the resulting report.  Basically, users could generate one report at a time. Now the user wants to be able to generate multiple reports using that same template and to populate a  workbook with those reports as separate worksheets. I went ahead and converted GenerateWorksheet to a function that returned a spreadsheet to the calling procedure.  I thought that part  was working since the template was being populated and being saved to a location.

I'll look out for the code.

It sounds as though you had the right idea anyway.

When I do this sort of thing I try and take it as slowly as possible, within reason.

That sometimes helps.:)

Hello

Here is the code for the GenerateWorksheetFunction

 Private Function GenerateWorksheet(ByVal dp As DatasetPair) As Excel.Worksheet
        If cboYears.SelectedIndex = -1 Then
            MessageBox.Show("You must make a valid selection from the Budget Year drop-down box", "Missing Selection")
            Return Nothing
        End If
        Dim strDvidOne As String = String.Empty
        Dim strDvidTwo As String = String.Empty
        Dim n As Integer = 0
        Dim ws As New Excel.Worksheet

        'Try

        strDvidOne = dp.DataVersionIdOne.ToString
        strDvidTwo = dp.DataVersionIdTwo.ToString

        'Populates datagrid dgvPBTM
        GenerateReportData(strDvidOne, strDvidOne)

        If dgvPBTM.RowCount = 0 Then
            MessageBox.Show("No Data Available")
            Return Nothing
        End If
        Dim strInput As String = ""

        'Retrieves Excel template
        Dim file As String = Utilities.GetResourceFile("PrimaryBenefitsTraditionalMetricsTemplate")

        If file <> String.Empty Then
            ' Starts the application of the temporary location. If necessaryYou could copy the file to another location 

            Dim strExcelFile As String = file
            If Not IO.File.Exists(strExcelFile) Then Return ws '
            ' Start Microsoft Excel and make it visible.

            myExcel = New Excel.Application

            If myExcel Is Nothing Then
                Throw (New Exception("Unable to Start Microsoft Excel."))
            Else
                myExcel.Visible = True
                myExcel.WindowState = Excel.XlWindowState.xlMaximized

                ' Get the process ID of the Excel instance. This is used
                ' in the Closing Event to prevent orphaned Excel processes.
                Dim aProcesses() As Process = Process.GetProcesses
                Dim t As Integer

                For t = 0 To aProcesses.GetUpperBound(0)
                    If aProcesses(t).MainWindowHandle.ToString = myExcel.Hwnd.ToString Then
                        myExcelPID = aProcesses(t).Id
                        Exit For
                    End If
                Next
            End If

            Dim strWorkSheetName As String = "Sheet1" & DateTime.Today.ToString
            Dim aWorkbook As Excel.Workbook
            Dim aWorkSheet As Excel.Worksheet
            n += 1
            aWorkbook = myExcel.Workbooks.Open(file)
            aWorkSheet = aWorkbook.Sheets(n)
            aWorkSheet.Activate()

            If dgvPBTM.RowCount < 1 Then
                MessageBox.Show("No valid data")
                Return Nothing
            Else
                ' Assume the data to send to Excel is in a datagric view and it has 5 columns
                ' and will fill Excel starting with column C and row 4. 
                Dim intRows As Integer = myDataTable.Rows.Count
                Dim intCols As Integer = myDataTable.Columns.Count
                Dim strUserTitle As String = ""
                Dim strRangeString As String = ""

                strRangeString = "NemDataRange"

                Dim aRange As Excel.Range = aWorkSheet.Range(strRangeString)

                ' Remove any existing data from Excel.
                aRange.ClearContents()

                Dim cell As Excel.Range
                cell = aRange.Cells

                For Each cell In aRange
                    cell.Interior.Color = RGB(255, 255, 0)
                Next

                Dim vecRangeLines() As String
                Dim strRangeLines As String = "8, 10, 13, 15, 18, 20, 22, 24, 26, 29, 31, 33, 35"
                Dim intXLSLine As Integer = 0
                Dim intRangeLine As Integer

                vecRangeLines = strRangeLines.Split(",")

                'Iterate through the datasource
                For Each r As DataRow In myDataTable.Rows
                    'Assigning the line number to the variable
                    intRangeLine = Val(vecRangeLines(intXLSLine)) - 7
                    'Iterate through the columns
                    For x As Integer = 1 To intCols - 1
                        'Assign the value from the datasource to the range
                        If intRangeLine = 19 Then

                            If r(x) Is DBNull.Value Then
                                aRange.Cells(intRangeLine, x) = " "
                            Else
                                aRange.Cells(intRangeLine, x) = r(x) * 1000
                            End If

                        Else
                            If r(x) Is DBNull.Value Then
                                aRange.Cells(intRangeLine, x) = " "
                            Else
                                aRange.Cells(intRangeLine, x) = r(x)
                            End If
                        End If
                    Next
                    intXLSLine += 1
                Next

                'Populate year range in Excel sheet
                strRangeString = "YearHeaderRange"
                aRange = aWorkSheet.Range(strRangeString)

                Dim d As Integer = 1
                For c As Integer = 1 To myDataTable.Columns.Count - 1
                    'Assign the value from the datasource to the range
                    aRange.Cells(1, d) = myDataTable.Columns(c).ColumnName.ToString
                    d += 1
                Next

                strRangeString = "UserProvidedTitleLine"
                aRange = aWorkSheet.Range(strRangeString)

                strUserTitle = txtUserProvidedTitle.Text.ToString

                aRange.Cells(1, 1) = strUserTitle

                Dim strTechName As String = TechnologySetDB.GetTechnologyName(strTech)

                Dim p As New Policy
                p = PolicyDB.GetSelectedPolicy(strPolicy)
                Dim strPolicyName As String = p.Description.ToString

                Dim b As New Budget
                b = BudgetDB.GetSelectedBudget(strBudget)
                Dim strBudgetName As String = b.Description.ToString


                strRangeString = "ScenarioNameRange"
                aRange = aWorkSheet.Range(strRangeString)
                aRange.Cells(1, 1) = strFY & " " & strTechName & ", " & strPolicyName & ", " & strBudgetName '& ", " & "Version " & strVersion


                strRangeString = "DataDateRange"
                aRange = aWorkSheet.Range(strRangeString)
                aRange.Cells(1, 1) = "Printed: " & Now.ToString

                strRangeString = "DataVersionIdRange"
                aRange = aWorkSheet.Range(strRangeString)
                aRange.Cells(1, 1) = "DataVersionId: " & strdvid1 & vbCrLf & "DataVersionId: " & strdvid2

                ws = aWorkSheet
                Return ws

            End If
        Else
            MessageBox.Show("Unable to extract the File")
        End If


        'Catch ex As Exception
        'MessageBox.Show(ex.Message.ToString & vbCrLf & ex.GetType.ToString)
        ' Finally

        'CleanUp()
        'End Try
        Return ws
    End Function

Select allOpen in new window

I added the line
MessageBox.Show(ws.Range("A8").Value.ToString)
to the calling routine to verify that I was receiving a valid worksheet.  I received a valid value which indicated that I was so that tells me that the GenerateWorksheet function is returning what it is supposed to

Why do you seem to have opened another instance of Excel in the function?
It's no wonder you need code to find 'ghost'/'orphaned' instances and close them
You shouldn't need to do that.
The template file could be opened in the current instance, where you could also create the worksheet.
It might be worthwhile to sit down and think about exactly what you want to do and work out how to approach.
Even if you explain it here in plain words it might help - the task doesn't seem to complicated.
By the way, where did you add the messagebox?
If it's in the function that creates the worksheet then it's probably not an ideal test, especially when you appear to be creating the new worksheet in an instance other than the 'main' one.
Again, sorry for sounding so negative - just trying to interpret the code and still trying to set something up to test it.

Brief Explantion
I have an interface that allows users to make mutltiple selections from a listview control that displayes pairs of data  I then populate a tist object via those selections.  So for example if the user makes for selections the list object is populated via the following code
  Dim intSheetCount As Integer = 0
        For i As Integer = 0 To lvMatchedDvid.Items.Count - 1
            Dim dp As New DatasetPair
            dp.DataVersionIdOne = lvMatchedDvid.Items(i).Text.ToString
            dp.DataVersionIdTwo = lvMatchedDvid.Items(i).SubItems(1).Text.ToString
            intSheetCount += 1
            dp.SheetId = intSheetCount
            dpl.Add(dp)
        Next

I then loop tthrough each object in the list


I then loop through each object in the list and generate a worksheet for each selection.  That's the purpose of GenerateWorksheet.  I want to insert the worksheet returned by each iteration of the loope into the workbook created in   Dim wb As Excel.Workbook = Utilities.CreateWorkbook()

I've since make modificaition to the calling procedure by eliminating
I added the messagebox in the calling procedure.  I did go ahead and simplified it,. As you can see

        Dim dpl As New List(Of DatasetPair)

        'Try
        Me.Cursor = Cursors.WaitCursor
        Dim intSheetCount As Integer = 0
        For i As Integer = 0 To lvMatchedDvid.Items.Count - 1
            Dim dp As New DatasetPair
            dp.DataVersionIdOne = lvMatchedDvid.Items(i).Text.ToString
            dp.DataVersionIdTwo = lvMatchedDvid.Items(i).SubItems(1).Text.ToString
            intSheetCount += 1
            dp.SheetId = intSheetCount
            dpl.Add(dp)
        Next

        Dim wb As Excel.Workbook = Utilities.CreateWorkbook()
        Dim strFilePath As String = wb.Path.ToString & "\" & wb.Name.ToString

        For Each dp As DatasetPair In dpl
            Dim excelApp As New Excel.Application
            Dim ws As New Excel.Worksheet
            ws = GenerateWorksheet(dp)
            Dim wbkDest As Excel.Workbook = excelApp.Workbooks.Open(strFilePath)
            MessageBox.Show(ws.Range("A8").Value.ToString)
            ws.Copy(wbkDest.Worksheets(wbkDest.Worksheets.Count - 1))
        Next

Select allOpen in new window

BTW
I added the messagebox in the calling procedure.  I did go ahead and simplified it,. As you can see
I removed the following line
 ws.SaveAs(wb.Path.ToString)
            Dim strSourceFilePath As String
            strSourceFilePath = wb.Path.ToString & "\" & wb.Name.ToString
            MessageBox.Show(strSourceFilePath)
Dim wbkSource As Excel.Workbook = excelApp.Workbooks.Open(strSourceFilePath

You still seem to be creating multiple instances of Excel.

Now I might be missing something but I don't think you would need to do that.

With one instance you can create, modify, copy, save etc worksheets/workbooks, and you don't need a seperate instance for each workbook.

Also why do you seem to create the workbook, then open another instance of Excel and then open the created workbook in the new instance.

I don't know why but I think the root of this problem is because you have all these instances flying about all over the place.

I'm not sure how VB.NET, or even Excel VBA, deals with copying between application instances - but I've got a feeling it doesn't 'like' dealing with that sort of thing.

Have you tried adding watches on your variables and stepping trhough the code in debug mode?

I've gone ahead and modified the code a bit more as shown below.  In the sample below how do I have wb and ws use the same instance.  To be honest, I new at automation and the concept of Excel multiple instances.  How do assign wb and ws to the same instance?

Dim wb As Excel.Workbook = Utilities.CreateWorkbook()
       
        For Each dp As DatasetPair In dpl      
            Dim ws As Excel.Worksheet
            ws = GenerateWorksheet(dp)  
            MessageBox.Show(ws.Range("A8").Value.ToString)
            ws.Copy(After:=wb.Worksheets("Sheet1"))
     
        Next

Hello,
I'm still at it and I'd like to run the following code.  Could you take a look at it and see if my logic is sound
First I declare workbook and worksheet objects  as well as an application object via
Dim xlsApp As Excel.Application
        Dim xlsWB As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet

I then instantiate an Excel instance via
        xlsApp = New Excel.Application
I then create a workbook and then retrieve itts path via
Dim wb As Excel.Workbook = Utilities.CreateWorkbook()
        Dim strFilePath As String = wb.Path.ToString & "\" & wb.Name.ToString
I then instantiate xlsWB via the open method of xlsApp.Workbooks where xlsAPP is the current instance of Excel, thus instantiating xslWB in the same instance of Excel
Then I set xlsSheet to sheet1 in the worksheet collection of xlsWB
I then execute GenerateWorksheet which returns a worksheet that is assigned to xlsSheet.  After that I execute the copy method

Dim xlsApp As Excel.Application
        Dim xlsWB As Excel.Workbook
        Dim xlsSheet As Excel.Worksheet


        xlsApp = New Excel.Application
        Dim wb As Excel.Workbook = Utilities.CreateWorkbook()
        Dim strFilePath As String = wb.Path.ToString & "\" & wb.Name.ToString
        xlsWB = xlsApp.Workbooks.Open(strFilePath)
        xlsSheet = xlsWB.Worksheets(1)
        For Each dp As DatasetPair In dpl
            xlsSheet = GenerateWorksheet(dp)
            xlsSheet.Copy(After:=xlsApp.Workbooks(xlsWB).Worksheets("Sheet1"))
        Next

Select allOpen in new window

Right that clarifies things a bit, and I might have got muddled up a bit.

A few questions though.

1 Where does the new workbook get created?

2 Is the new workbook is created then saved and closed?

3 Is 2 the reason you need to open it again?

4 Is there an instance of Excel in existence before you this piece of your code?

5 What workbook does GenerateWorksheet create the worksheet in?

1. The new workbook get created via  
 Dim wb As Excel.Workbook = Utilities.CreateWorkbook(). I've attached that code .  
2. This is where the workbook is saved.  
3. The reason that I reopened it is because I wasn't sure how to pass the instance of excel it was created to the calling procedure.  
4. There isn't a instance of EXcel in existent until I call the CreateWorkbook procedure
5. The Generate Worksheet function populate a template that reside in the resource directory of the project file.  It is then passed to the calling procedure

Public Shared Function CreateWorkbook() As Excel.Workbook
        Dim filename As String
        Dim excelApp As Excel.Application
        Dim excelWorkbook As Excel.Workbook
        Dim excelSheet As Excel.Worksheet
        Dim SaveFileDialog As New SaveFileDialog

        If My.Settings.DefaultLocation = "" Then
            SaveFileDialog.InitialDirectory = "C:\"
        Else
            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.ToString
        End If
        SaveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx"
        SaveFileDialog.FilterIndex = 2
        SaveFileDialog.RestoreDirectory = True

        '***************************Creates Spreadsheet*********************************************

        If SaveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            filename = SaveFileDialog.FileName
            'Validate file name
            If Convert.ToString(filename) = "" Then
                Return Nothing
                Exit Function
            End If

            'Create excel instance
            Dim misValue As Object = System.Reflection.Missing.Value
            excelApp = New Excel.ApplicationClass

            excelWorkbook = excelApp.Workbooks.Add(misValue)
            excelSheet = excelWorkbook.Sheets("sheet1")
            excelApp.ScreenUpdating = True

            excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper
            If Not filename.EndsWith(".xlsx") Then
                excelWorkbook.SaveAs(filename, 56)
            Else
                excelWorkbook.SaveAs(filename)
            End If

            MsgBox("File generated successfully at " & filename)
            
            Return excelWorkbook
        Else
            Return Nothing

        End If
        
    End Function

Select allOpen in new window

I'll give it a try.  It's about midnight here in Colorado.  but I'll try it in the morning.  Thanks again

I took your advice and instead of opening multiple instances of Excel, I used the same intance and passed it to both the Create Workbook and GenerateWorksheet functions.  It worked.  I have to now prevent the populated template from having to be saved and add some clean-up code to make sure excel is shut down after the calling procedure ends so that there are no instances of Excel hangin around.

Why not just close the template without saving it?

The workbook Close method has one argument - SaveChanges.

It can be true or false, I'll let you guess which doesn't save any changes.

As for clean-up code, always a good idea but wouldn't it perhaps be better not to have to use it.

eg ensure there is no possibility of any 'ghost' instances

You've already taken a step towards doing that by emilinating the use of multiple instances.

Another thing you might want to look at is what you are actually doing with Excel and how you are doing it.

A lot of the time the reason for ghost instances is because things haven't been referenced properly.

Hello,

I know about the workbook close method and the save changes argument.  But what is the syntax  to refer to the worksheets parent workbook.  I thought is was worksheet.parent.  In my case, in the calling procedure, I wanted to close the parent workbook of the xlsSheet after it is copied to the destination workbook.  I thougt it is the parent property of the worksheet. but that doesn't seem to be it

 For Each dp As DatasetPair In dpl
                xlsSheet = GenerateWorksheet(dp, xlsApp)
                MessageBox.Show(xlsSheet.Range("A8").Value.ToString)
                xlsSheet.Copy(Before:=xlsWB.Worksheets(1))
            Next
Close Parent workbook of xlsSheet

Select allOpen in new window

Hello,
I did some more research and found  a function to retrieve the workbook name at http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.parent(VS.80).aspx
I then modified this function and inserted the modified code after the loop to close the temporary worksheet as shown in the enclosed code.  I'm making progress slowly but surely

wb = Utilities.NewCreateWorkbook(xlsApp)

            Dim strFilePath As String = wb.Path.ToString & "\" & wb.Name.ToString
            xlsWB = xlsApp.Workbooks.Open(strFilePath)
            xlsWB.Activate()
            xlsSheet = xlsWB.Worksheets(1)
            For Each dp As DatasetPair In dpl
                xlsSheet = GenerateWorksheet(dp, xlsApp)
                MessageBox.Show(xlsSheet.Range("A8").Value.ToString)
                xlsSheet.Copy(Before:=xlsWB.Worksheets(1))
            Next
            Dim book As Excel.Workbook = CType(xlsSheet.Parent, Excel.Workbook)
            Dim wkbName As String
            wkbName = book.Name
            xlsApp.Workbooks(wkbName).Close(False)
            Me.Cursor = Cursors.Arrow

Select allOpen in new window

Why do you need to refer to the worksheet's parent?

Don't you already have a reference to the workbook?

If you don't then perhaps it's time you did have one.

It might even be time to totally rethink all the objects you are working with/using.

I'm pretty sure you can do what you want with 1 instance of Excel, you might need more than one worksheet/workbook but if you only have one instance of Excel then closing them etc shouldn't be a problem.

Just make sure you are referencing everything correctly and that you are passing the right arguments to your functions.

eg if you want to create a new workbook you can use the existing instance of Excel, so pass that

One thing I forgot to ask is why are you creating the worksheet in a new workbook, then copying it to the 'main' workbook?

Couldn't you just create the new worksheet in the workbook you are working in?

I'm using an excel template in my resource folder of the project file and poplulating it

I've gone ahead and attached a copy of the template file so you can see the sheet I am populating and copying to the destination workbook.
PrimaryBenefitsTraditionalMetric.xlsx

I've been monitoring the instances of Excel that are running via Task Manager and there seems to be only one instance running. So everything looks like it's running correctly

Thanks again for all your help.  I particularly new to working with Excel from vb.net and I was having some trouble with the Excel object model as well as the issue of  multiple instances of Excel

No problem - I'm relatively new to VB.Net, normally use C#.

I think the basic concepts are samish, though I think if you had tried this in C# you might have
found yourself with a lot more cast errors.

Well I almost always seem to anyway.