excel - .Copy() twice

▲
0
▼

I have a workbook that consists of 6 worksheets. I want to save sheet 2 & 3

    With Excel
            .SheetsInNewWorkbook = 6
            .Workbooks.Add()
            ....

            .Worksheets(2).Copy()
            filename = "1.txt"
            .ActiveWorkbook.SaveAs(filename, -4158)
            .Worksheets(3).Copy()
            filename = "2.txt"
            .ActiveWorkbook.SaveAs(filename, -4158)
    End With

Upon copying the 2nd time, my application crash and return this error: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)). I noticed the error was due to trying to "Copy" the 2nd time, so how should I copy twice in this case?

vb.net
excel
asked on Stack Overflow May 22, 2014 by Sky • edited May 22, 2014 by Sky

2 Answers

▲
0
▼

Try this:

With Excel
    Dim sourceWB As Workbook = .Workbooks.Add()
    '~~> Rest of your code here
    .
    .
    .
    sourceWB.Worksheets(2).Copy
    .ActiveWorkbook.SaveAs("C:\blah\test1.txt", -4158)
    sourceWB.Worksheets(3).Copy
    .ActiveWorkbook.SaveAs("C:\blah\test2.txt", -4158)
End With
answered on Stack Overflow May 22, 2014 by L42 • edited May 22, 2014 by L42
▲
0
▼

Not sure why, but .Worksheets(2).Copy() will open another workbook "Book2" in this case, therefore need to close it.

.DisplayAlerts = False
.ActiveWorkbook().Close()

Upon closing the copied worksheet then copy again.

.Worksheets(3).Copy()
filename = "2.txt"
.ActiveWorkbook.SaveAs(filename, -4158)

The full code will be 

.Worksheets(2).Copy()
filename = "1.txt"
.ActiveWorkbook.SaveAs(filename, -4158)
.DisplayAlerts = False
.ActiveWorkbook().Close()
.Worksheets(3).Copy()
filename = "2.txt"
.ActiveWorkbook.SaveAs(filename, -4158)
answered on Stack Overflow May 22, 2014 by Sky

User contributions licensed under CC BY-SA 3.0