none
vb code to move a worksheet to a new file

    Question

  • Please let me know if this is not the best forum for this since this question is about using Visual Basic to do some Excel specific stuff.

    Here is what I have and what I want to do.

    I included Microsoft.Office.Interop to my code.
    And I have a variable called objSheet that is defined as type Excel.Worksheet.

    I am at a section in my code where I've assigned a particular worksheet to the variable objSheet.
    Now, I want to take that worksheet and move a copy of it into a new file.

    After poking around a bit, I see that I can write objSheet.Move(), but I think this is to move the sheet to another ordinal position within the same file.
    However, as I've stated, I'm looking for something that will move a copy of the sheet to a new file.

    This would be equivalent to openning an Excel workbook and right clicking on a worksheet and choosing the "Move or Copy..." option and selecting "new book".

    How would I do this within my VB code?
    Friday, May 22, 2009 5:56 PM

Answers

  • If you want to copy to another file

    Dim MyExcel As New Excel.Application
            Dim sourcewb As Excel.Workbook = MyExcel.Workbooks.Open("C:\Backup\Source.xls")
            Dim ToCopyws As Excel.Worksheet = sourcewb.Worksheets(1)  'taking first sheet
            Dim destwb = MyExcel.Workbooks.Open("C:\Backup\Dest.xls")
            ToCopyws.Copy(destwb.Worksheets(1)) 'for after work sheet 1 ToCopyws.Copy(, destwb.Worksheets(1))
           
            destwb.Close(True)  'save true
            sourcewb.Close(False) 'dont save
    
            'closing properly
            Try
                MyExcel.Quit()
                'make sure excel exited, this is important otherwise you might end up running many instances of excel(check task manager)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ToCopyws)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sourcewb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destwb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
                GC.Collect()
                GC.WaitForPendingFinalizers()
            Catch ex As Exception
                destwb = Nothing
                sourcewb = Nothing
                ToCopyws = Nothing
                MyExcel = Nothing
            End Try

    But if you want to save it to a new file then after getting Tocopyws, you can simple save it to new file

     ToCopyws.SaveAs("c:\backup\newfile.xls")

    Arjun Paudel
    • Marked as answer by DoolinDalton Tuesday, May 26, 2009 9:20 PM
    Friday, May 22, 2009 6:49 PM

All replies

  • If you want to copy to another file

    Dim MyExcel As New Excel.Application
            Dim sourcewb As Excel.Workbook = MyExcel.Workbooks.Open("C:\Backup\Source.xls")
            Dim ToCopyws As Excel.Worksheet = sourcewb.Worksheets(1)  'taking first sheet
            Dim destwb = MyExcel.Workbooks.Open("C:\Backup\Dest.xls")
            ToCopyws.Copy(destwb.Worksheets(1)) 'for after work sheet 1 ToCopyws.Copy(, destwb.Worksheets(1))
           
            destwb.Close(True)  'save true
            sourcewb.Close(False) 'dont save
    
            'closing properly
            Try
                MyExcel.Quit()
                'make sure excel exited, this is important otherwise you might end up running many instances of excel(check task manager)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ToCopyws)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sourcewb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destwb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
                GC.Collect()
                GC.WaitForPendingFinalizers()
            Catch ex As Exception
                destwb = Nothing
                sourcewb = Nothing
                ToCopyws = Nothing
                MyExcel = Nothing
            End Try

    But if you want to save it to a new file then after getting Tocopyws, you can simple save it to new file

     ToCopyws.SaveAs("c:\backup\newfile.xls")

    Arjun Paudel
    • Marked as answer by DoolinDalton Tuesday, May 26, 2009 9:20 PM
    Friday, May 22, 2009 6:49 PM
  • Thanks. I see how you are doing it.

    Regaring your code for closing properly, what is all that stuff?
    I usually do just workbook.close() and excel.quit(), but not the other stuff.
    And never had any problems.
    What can go wrong if I don't do all that system.runtime.interopservices.... stuff?
    Tuesday, May 26, 2009 1:49 PM
  • That is to make sure you have cleaned up everything, if you do not do that you might end up having many instances of Excel in memory, check task manager to verify. And it might also lock your file until excel remains in memory. Some people prefer those in finally statement but if you go that way check for null(Nothing) before releasing, even you can check there for better.

    Its always good to release com object that way

    Arjun Paudel
    Tuesday, May 26, 2009 1:52 PM
  • OK, thanks.

    Another question on the line that says, Dim destwb = MyExcel.Workbooks.Open("C:\Backup\Dest.xls")

    I'm getting an error on this saying the file does not exist.
    For me, this would be a new file and I thought the command above will create the target file for me, but I guess it expects it to be there?

    How would I create a new target workbook?
    Tuesday, May 26, 2009 3:13 PM
  • You ignored the last line of post :)
    Dim MyExcel As New Excel.Application
            Dim sourcewb As Excel.Workbook = MyExcel.Workbooks.Open("C:\Backup\Source.xls")
            Dim ToCopyws As Excel.Worksheet = sourcewb.Worksheets(1)  'taking first sheet
            ToCopyws.SaveAs("c:\backup\newfile.xls")  'change the path 
    sourcewb.Close(False) 'dont save 'closing properly Try MyExcel.Quit() 'make sure excel exited, this is important otherwise you might end up running many instances of excel(check task manager) System.Runtime.InteropServices.Marshal.ReleaseComObject(ToCopyws) System.Runtime.InteropServices.Marshal.ReleaseComObject(sourcewb) System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel) GC.Collect() GC.WaitForPendingFinalizers() Catch ex As Exception sourcewb = Nothing ToCopyws = Nothing MyExcel = Nothing End Try


    Arjun Paudel
    Tuesday, May 26, 2009 3:16 PM
  • Yes, I didn't see that last line.

    EDIT: Actually, I see that your code works, but for my specific need, I need to use the .Add to add a new workbook, do the copy into it, and then close it while saving it with a new name.  I hope you know what I mean. But I see that when I use the .Add, it creates 3 sheets by default. Is there a way to Add a workbook and specify how many sheets are created? Furthermore, can I specify the name(s) of the sheet(s)?
    Tuesday, May 26, 2009 3:40 PM
  • Try this!

     Dim MyExcel As New Excel.Application
            Dim sourcewb As Excel.Workbook = MyExcel.Workbooks.Open("C:\Backup\Source.xls")
            Dim ToCopyws As Excel.Worksheet = sourcewb.Worksheets(1)  'taking first sheet
    
            Dim wb = MyExcel.Workbooks.Add()
           ' MyExcel.
            ToCopyws.Copy(wb.Worksheets(1))
            Dim destws As Excel.Worksheet = wb.Worksheets(2)
            destws.Delete()   'deleting worksheet 2, its 2 because new is inserted before it
            destws = wb.Worksheets(1)
            destws.Name = "Copied Worksheet Name" 'change name
    
            wb.SaveAs("c:\backup\newfile.xls")
            sourcewb.Close(False) 'dont save
            wb.Close()
            'closing properly
    
            Try
                MyExcel.Quit()
                'make sure excel exited, this is important otherwise you might end up running many instances of excel(check task manager)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ToCopyws)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(destws)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sourcewb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(MyExcel)
                GC.Collect()
                GC.WaitForPendingFinalizers()
            Catch ex As Exception
                sourcewb = Nothing
                ToCopyws = Nothing
                destws = Nothing
                wb = Nothing
                MyExcel = Nothing
    
            End Try


    Arjun Paudel
    Tuesday, May 26, 2009 4:56 PM
  • Just out of curiosity, does the Worksheet you're copying have any formulas, formatting or ranges defined or is it just raw data?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 26, 2009 5:09 PM
  • Just out of curiosity, does the Worksheet you're copying have any formulas, formatting or ranges defined or is it just raw data?
    Paul ~~~~ Microsoft MVP (Visual Basic)

    Just values, no formulas. So no added complexity related to that.
    Tuesday, May 26, 2009 9:19 PM
  • Arjun, thanks for your help. I was able to achieve what I wanted by using the .delete as you suggested.
    Tuesday, May 26, 2009 9:20 PM