Copy Range Between Workbooks

[Guest]

Hi,

I have a selected range in a 2003 Workbook that contains a C# addin.
What I want is to Export that range into a new Workbook.

The SaveAs will not work (unless I am unaware of this), because I don't want
the Addin to be available in the new Workbook, just the data.

I tried the following:

Excel.Range cellRange, rng;
Excel.Worksheet src;
src = (Excel.Worksheet)Fleet.Globals.ThisWorkbook.ActiveSheet;

cellRange = src.get_Range(firstCell, lastCell);

Excel.Application xl = new Excel.Application();
Excel.Workbook wb =
(Excel.Workbook)xl.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets.get_Item(1);
rng = ws.get_Range(firstCell, lastCell);
cellRange.Select();
cellRange.Copy(rng);
ws.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false);

Trying this I got an error: "Cannot empty the Clipboard"

Thanks,

Aric

 

[Ronald Dodge]

As for VBA code within Excel using early binding is concerned, you can use
the following:

DIM rngSRC as Excel.Range, rngDST as Excel.Range, wbkNewWB as Excel.Workbook

Set rngSRC =
Thisworkbook.ActiveSheet.Range(Selection.Address(False,False,xlA1,False))
Set wbkNewWB = Workbooks.Add(xlWBATWorksheet)
Set rngDST = wbkNewWB.Worksheets(1).Cells(rngSRC.Row,rngSRC.Column)

rngSRC.Copy(rngDST)

wbkNewWB.SaveAs ThisWorkbook.Path & "\FileName.xls"

If you are planning on replacing the backup copy of the file that already
has the file name to it, you may want to replace the "SaveAs" text in the
above line of code to "SaveCopyAs", otherwise you will get the message box
about do you want to replace the file that is already there with the one you
are currently saving. There is no way to get rid of that message box
without having to use the SaveCopyAs method in place of the SaveAs method.
There are a couple of other differences between these 2 methods.

One word of caution. I generally avoid the use of Active<object> and
Selection as these objects can cause problems down the road. There are uses
for them, but in general, it's just good practice to avoid them if
reasonably possible. That was one of the first things I ran into issues
with after I started learning how to use VBA which my initial learning
process started with using the macro recorder.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000