How to work with hidden excel workbook?

tvsundar
tvsundar used Ask the Experts™
on
Hi All,

I have an Hidden workbook. I want to automate this workbook through VB. I opened the workbook through VB and assigned it to Workbook object.

I copy some contents of one sheet and when i use Pastespecial or Select method with other sheet, it throws Object or with block variable not set.

appExcel.Workbooks("Tables").Sheets(strSourcesheet).Activate
appExcel.Workbooks("Tables").Sheets(strSourcesheet).Cells.Copy

appExcel.Workbooks("Tables").Sheets(strDestinationsheet).Activate
appExcel.Workbooks("Tables").Sheets(strDestinationsheet).Cells(1, 1).Select  'In this line it throws the error

appExcel.ActiveCell.PasteSpecial xlPasteFormats
appExcel.ActiveCell.PasteSpecial xlPasteValues


Pls Hlp in this reg.

Thank you,
Sundar
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
It sounds like the strDestinationSheet variable is either not set or does not contain the name of an existing sheet.

I think that there may be a better way to achieve what you are trying to do.  It appears that you are trying to copy the entire contents of the source sheet into the destination sheet.  Is that true?

Would copying the sheet, rather than the contents of the sheet accomplish your task?  If so, you can actually use the copy method of the worksheet:

appExcel.Workbooks("Tables").Sheets(strSourceSheet).Copy after:=appExcel.Workbooks("Tables").Sheets(strSourceSheet)

If not, how about the following?  It requires no selection of the cells.

appExcel.Workbooks("Tables").Sheets(strSourceSheet).UsedRange.Copy

appExcel.Workbooks("Tables").Sheets(strdestinationsheet).Range("A1").PasteSpecial xlPasteFormats

appExcel.Workbooks("Tables").Sheets(strdestinationsheet).Range("A1").PasteSpecial xlPasteValues
 

Author

Commented:
Hi CRJ2000,

Sorry for the late reply.

I tried ur code (second one). Actually i don't need the first suggestion u gave.

The following error is thrown to me when i tried ur code

Run-time error '-2147417851 (80010105)':
Methos 'PasteSpecial' of object 'Range' failed

Actually, because of the workbook is hidden, the Range, Select, Activate and some other methods are not working

Pls hlp me,

Sundar
Commented:
I've been able to do this using both of the methods posted (your's and mine).  I did, however, have to make one change.  When I referenced "Tables" I needed to use "Tables.xls"

If this was problem for you, however, it should have shown up before the point at which your code is breaking.

Let me ask a couple of follow-up questions...

1.  What version of Excel are you using?

2.  Do you have a reference to Excel in your project, or are you using CreateObject?

3.  When you say that the workbook is hidden, do you mean that Excel is not visible?  I've been able to use both methods without Excel being visible.

4.  Forgive me for suggesting something so simple, but are you positive that the strDestination variable is set correctly?  It must contain the name of an existing sheet.

Chris

Author of the Year 2009

Commented:
Hi tvsundar,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept CRJ2000's comment(s) as an answer.

tvsundar, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept THIS comment as an answer.
==========
DanRollins -- EE database cleanup volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial