Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Between Workbooks
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.Active Sheet; cellRange = src.get_Range(firstCell, lastCell); Excel.Application xl = new Excel.Application(); Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Add(Excel.XlWBATempla te.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Between Workbooks
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(F alse,False,xlA1,False)) Set wbkNewWB = Workbooks.Add(xlWBATWorksheet) Set rngDST = wbkNewWB.Worksheets(1).Cells(rngSRC.Row,rngSRC.Col umn) 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 "Brite Global (Aric)" wrote in message ... 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.Active Sheet; cellRange = src.get_Range(firstCell, lastCell); Excel.Application xl = new Excel.Application(); Excel.Workbook wb = (Excel.Workbook)xl.Workbooks.Add(Excel.XlWBATempla te.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a range from closed workbooks (ADO) | Excel Discussion (Misc queries) | |||
copy range to multiple workbooks | Excel Discussion (Misc queries) | |||
Copy a range in two different workbooks | Excel Programming | |||
Copy/paste range variable between workbooks | Excel Programming | |||
Copy range between workbooks | Excel Programming |