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 Befo=wbkThis.Sheets(1)

This fails on the last line with "Copy method of Worksheet class failed"
--
Mining

You can copy sheet and paste to another workbook---All within the same
instance of excel...

If this post helps click Yes
---------------
Jacob Skaria


"selcast" wrote:

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 Befo=wbkThis.Sheets(1)

This fails on the last line with "Copy method of Worksheet class failed"
--
Mining

This fails on the last line with "Copy method of Worksheet class failed"

If this does turn out to not be possible, could you create a new
sheet, and select / copy / paste the contents across? This is probably
more reliable than using the Sheets.Copy function as that truncates
cells to 255 characters.

Phil Hibbs.

if you turn screen updating off
Application.ScreenUpdaing = false
then you can open the workbook in the same excel instance ...


"selcast" wrote:

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 Befo=wbkThis.Sheets(1)

This fails on the last line with "Copy method of Worksheet class failed"
--
Mining