Copy sheet from workbook in new instance of Excel

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"
 
J

Jacob Skaria

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

If this post helps click Yes
 
P

Phil Hibbs

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.
 
P

Patrick Molloy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top