Solved: Copying from one workbook to another WITHOUT seeing source

[tbanov]

Hi,

Here's what I'm trying to do:

The user opens one workbook with a single worksheet that has a datepicker, a bunch of formatted cells and a button below somewhere. There's another workbook somewhere that contains several worksheets, all with the same format as the source workbook/sheet, but without the datepicker and button. When the button is clicked, the second workbook is to be accessed, data is to be pulled from a certain worksheet (which is determined by the value of the datepicker), and then it is to be pasted onto the user's workbook/sheet. The data I am interested in is in a specific range, and doesn't map perfectly due to the datepicker in the other workbook/sheet.

I have this all set up - the only thing I'm having trouble with is pulling the data. I know it's easy to just open the workbook and copy + paste, but I need to do this while keeping the user oblivious to where the data is coming from. My users are quite picky :P. I can't seem to find any info on how to do this.

I've tried recording a macro and running Data -> Import External Data -> Import Data, and I can specify the destination within the interface provided, but I cannot find any way in the code to specify the range of data that I am interested in.

Any suggestions?

Thanks for your time.

[figment]

try opening a new instance of excel, seting that instance to invisable then opening the second workbook to this instance. if you create the second instance when the workbook opens and close the instance when the workbook closes, then the user will be unlikly to notice, also this keeps you from having to constantly create the and close the instance as the select diffrent information. the only down side to this is, if force quit excel, or in some way close it with out letting the close macro run, then they will have an instance of excell open, that they will not be able to access.

[tbanov]

The potential of a force quit isn't a big deal for me. It is a little bit slower because of the fact that it has to open up a new instance of Excel, but that's alright.

Thanks.