Solved

What is causing a casting error when copying worksheet pages

Posted on 2010-09-09
25
434 Views
Last Modified: 2012-05-10
I have been trying to develop code that copies a worksheet from one workbook to another workbook.  I am getting a casting erros on line   ws.Copy(wbkDest.Worksheets(wbkDest.Worksheets.Count - 1)).  I'm not sure what is causing it.  The exact error message is "Specified cast is not valid."
Private Sub btnGenerateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click



        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



        MessageBox.Show(strFilePath)

        For Each dp As DatasetPair In dpl

            Dim excelApp As New Excel.Application

            Dim ws As New Excel.Worksheet

            ws = GenerateWorksheet(dp)

            ws.SaveAs(wb.Path.ToString)





            Dim strSourceFilePath As String

            strSourceFilePath = wb.Path.ToString & "\" & wb.Name.ToString

            MessageBox.Show(strSourceFilePath)



            'wb.Close()

            Dim wbkDest As Excel.Workbook = excelApp.Workbooks.Open(strFilePath)





            Dim wbkSource As Excel.Workbook = excelApp.Workbooks.Open(strSourceFilePath)

            'ws.Copy(Before:=wbkDest.Worksheets.Count - 1)

            ws.Copy(wbkDest.Worksheets(wbkDest.Worksheets.Count - 1))



           

        Next





        'Catch ex As Exception

        '    MessageBox.Show(ex.Message, ex.GetType.ToString)

        'Finally

        '    Me.Cursor = Cursors.Arrow

        'End Try







    End Sub

Open in new window

0
Comment
Question by:chtullu135
  • 16
  • 9
25 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 33642765
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.
0
 

Author Comment

by:chtullu135
ID: 33643326
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33643591
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.:)
0
 

Author Comment

by:chtullu135
ID: 33646133
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

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33646876
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 33648424
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.
0
 

Author Comment

by:chtullu135
ID: 33649544
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

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33649555
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 33649806
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?
0
 

Author Comment

by:chtullu135
ID: 33650030
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
0
 

Author Comment

by:chtullu135
ID: 33650666
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

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 33651041
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?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:chtullu135
ID: 33652041
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

Open in new window

0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 33655532
Why not pass the (an) instance of Excel to the CreateWorkbook function?

You would do it similarly to how you pass the datapair (whatever that is - I can't find any data type or object with that name.

Then you wouldn't need to create another instance within the function.

Also why are you creating using ExcelApplication.Class?

The function for creating the workbook seems pretty straightforward.

Here's some untested code as an example - give it a try and post back.

I'll give it a go when I get a chance.
Public Shared Function CreateWorkbook(xlApp As Excel.Application) As Excel.Workbook



        Dim filename As String 

        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 

 

            ' Don't create excel instance - use xlApp which has been passed to the function

            Dim misValue As Object = System.Reflection.Missing.Value 

             

            excelWorkbook = xlApp.Workbooks.Add(misValue) 



            excelSheet = excelWorkbook.Sheets("sheet1") 



            ' not quite sure what's happening here

            ' is this to show the newly created workbook?



            xlApp.ScreenUpdating = True 

 

            xlAppApp.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

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33655804
I'll give it a try.  It's about midnight here in Colorado.  but I'll try it in the morning.  Thanks again
0
 

Author Comment

by:chtullu135
ID: 33659380
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33659404
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.
0
 

Author Comment

by:chtullu135
ID: 33663122
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

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33663384
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

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 33663401
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?
0
 

Author Comment

by:chtullu135
ID: 33663536
I'm using an excel template in my resource folder of the project file and poplulating it
0
 

Author Comment

by:chtullu135
ID: 33663693
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
0
 

Author Comment

by:chtullu135
ID: 33663840
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
0
 

Author Closing Comment

by:chtullu135
ID: 33664169
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 33664313
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now