S
selcast
I am trying to copy sheet from another workbook opened in a new instance of
excel. I need to do it as a new instance as I do not want the user to see
the other workbook being opened (and I will run a progress meter as many
workbooks have to be opened and checked). Code is
Dim xlBack As Excel.Application
Dim wbkOpened As Workbook
Dim wbkThis As Workbook
Dim strFolder As String
Set wbkThis = Application.ActiveWorkbook
Set xlBack = New Excel.Application 'Open in the background
strFolder = strNameThisWbk("F") 'My function to get the current folder
Set wbkOpened = xlBack.Workbooks.Open(strFolder & "\" & "Test1.xls", False,
True)
xlBack.Visible = True 'Make visible during debug
wbkOpened.Sheets(1).Copy Before:=wbkThis.Sheets(1)
This fails on the last line with "Copy method of Worksheet class failed"
excel. I need to do it as a new instance as I do not want the user to see
the other workbook being opened (and I will run a progress meter as many
workbooks have to be opened and checked). Code is
Dim xlBack As Excel.Application
Dim wbkOpened As Workbook
Dim wbkThis As Workbook
Dim strFolder As String
Set wbkThis = Application.ActiveWorkbook
Set xlBack = New Excel.Application 'Open in the background
strFolder = strNameThisWbk("F") 'My function to get the current folder
Set wbkOpened = xlBack.Workbooks.Open(strFolder & "\" & "Test1.xls", False,
True)
xlBack.Visible = True 'Make visible during debug
wbkOpened.Sheets(1).Copy Before:=wbkThis.Sheets(1)
This fails on the last line with "Copy method of Worksheet class failed"