asked on

How to work with hidden excel workbook?

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

CRJ2000

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

tvsundar

ASKER

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

ASKER CERTIFIED SOLUTION

CRJ2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanRollins

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