Copy sheet from excel workbook to another workbook

Question

Below is my code for the copying process from one workbook to another. I looked up a lot of similar issues but i could not get this working. when I run this the two files open up and then i get a third one called book1 with all results. then i get an error "Copy method of Worksheet class failed". What Im trying to do is copy the general report sheet from o.Book to xBook. I want to leave the books open for now until this is correct but i will use Xbook later. Can I get help with this please?

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim oExcel As Excel.ApplicationClass
    Dim oBook As Excel.WorkbookClass
    Dim oBooks As Excel.Workbooks

    Dim xExcel As Excel.ApplicationClass
    Dim xBook As Excel.WorkbookClass
    Dim xBooks As Excel.Workbooks

    Dim user As String
    Dim opath As String
    Dim opathS As String
    Dim timeStamp As DateTime = DateTime.Now
    Dim path2 As String


    Label1.Text = "Working..."

    'Get the current system user user and set path to file
    user = Environment.UserName
    opath = "C:\Users\" + user + "\Downloads\ADC Open.xls"
    path2 = "C:\Users\" + user + "\Downloads\Personal.xlsm"
    opathS = "C:\Users\" + user + "\Desktop\Report.xls"

    'Create first object
    oExcel = CreateObject("Excel.Application")
    oExcel.DisplayAlerts = False
    oExcel.Visible = True
    oBooks = oExcel.Workbooks

    'Create second object
    xExcel = CreateObject("Excel.Application")
    xExcel.DisplayAlerts = False
    xExcel.Visible = True
    xBooks = xExcel.Workbooks

     'open first book 
    oBook = oBooks.Open(opath)

    'open second book
    xBook = xBooks.Open(path2)

    oBook.Worksheets("general_report").Copy(After:=xBook.Worksheets("general_report"))
    'Run the subroutine.
    'xExcel.Run("Execute")

    'xExcel.DisplayAlerts = False

    'Delete sheet not needed any more
    'xBook.Sheets("general_report").Delete

    'xExcel.DisplayAlerts = False

    'Save results to new file
    xBook.SaveAs(opathS)

    Label1.Text = "File saved at: " + opathS
    'Close the workbook and quit Excel.
    oBook.Close(False)

    System.Runtime.InteropServices.Marshal.
       ReleaseComObject(oBook)
    oBook = Nothing
    System.Runtime.InteropServices.Marshal.
       ReleaseComObject(oBooks)
    oBooks = Nothing
    oExcel.Quit()
    System.Runtime.InteropServices.Marshal.
       ReleaseComObject(oExcel)
    oExcel = Nothing

    'Delete  original file after finished with it 
    'System.IO.File.Delete(opath)
End Sub

At first sight you are creating two instances of Excel, open the workbooks in each instance (so each instance has one open workbook), then in the first one you intent to copy the sheet to the other workbook, which is _closed_ in that instance and marked read-only since it is already open elsewhere (in the other instance of Excel). Is it correct? — AntoineL, Mar 26 '18 at 11:34
That is correct, unfortunately I dont understand how to fix this instance problem. — Laszlo Malina, Mar 26 '18 at 11:47
I wanna copy the sheet from oExcel into xExcel and later wanna delete oExcel — Laszlo Malina, Mar 26 '18 at 12:04
`oExcel` and `xExcel` are instances, ie. Windows processes. You do not want to "delete" them. You might want to delete the workbooks, or more exactly the files which are the persistent storage of those books; `Erase opath` might do it, directly from VB.net, without even opening any instance of Excel. In fact, you should not have an instance of Excel which had that workbook opened for the erase to succeed! — AntoineL, Mar 26 '18 at 12:56

score 0 · Answer 1 · answered Mar 26 '18 at 12:18

0

Can't add a comment yet, but if VB is the same across all platforms, shouldn't you Set the variable after declaring it ?

Set MyObject = YourObject ' Assign object reference. 
Set MyObject = Nothing ' Discontinue association.

answered Mar 26 '18 at 12:18

CCM

136
2
4
17

1

My guess is that the OP is using VB.net (without fully realize there are differences between VB.net and VBA); then [that question](https://stackoverflow.com/questions/1468890) explains the fate of `Set`. – AntoineL Mar 26 '18 at 12:58
@AntoineL I assumed OP wasn't using Excel VBA otherwise there was no need to create the Excel object, but from my past errors, forgetting to `Set` variables resulted in `Copy method of Worksheet class failed`. – CCM Mar 26 '18 at 13:04
@CCM When I try to use Set, to set the objects I get an error that the Set assignment statements are no longer supported – Laszlo Malina Mar 26 '18 at 13:42

score 0 · Answer 2 · answered Mar 27 '18 at 08:25

After all the responses I started looking into these object settings and find code that help with the explainations, I refactored my previous version and this is what I came up with. It works like a charm now. Thanks everyone for the help and comments.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim xlApp As Excel.Application = New Excel.Application

    Dim user As String
    Dim sourcePath As String
    Dim targetPath As String
    Dim savePath As String


    Label1.Text = "Working..."
    user = Environment.UserName
    sourcePath = "C:\Users\" + user + "\Desktop\Report\ADC Open (Dell GTIE JIRA).xls"
    targetPath = "C:\Users\" + user + "\Desktop\Report\Personal1.xlsm"
    savePath = "C:\Users\" + user + "\Desktop\Report\Report" & Format(Now(), "DD-MMM-YYYY") & ".xlsm"

    Dim wbSourceBook As Excel.Workbook = xlApp.Workbooks.Open _
        (sourcePath, ReadOnly:=False)
    Dim wbTargetBook As Excel.Workbook = xlApp.Workbooks.Open _
        (targetPath, ReadOnly:=False)

    'Excel expects to receive an array of objects that
    'represent the worksheets to be copied or moved.
    Dim oSheetsList() As Object = {"general_report"}

    wbSourceBook.Sheets(oSheetsList).Copy(Before:=wbTargetBook.Worksheets(1))

    wbSourceBook.Close(True)

Copy sheet from excel workbook to another workbook

2 Answers2