[Solved] CopyRange Copy Cells to another Spreadsheet or file
Posted: Sat Jul 21, 2012 11:07 am
Hello all,
I would like to create a macro to copy a cell range(one row, C56:J56) from my invoice template file: “C:\CalcWork\Invoice.ods” and paste it to the first empty row in column A of my invoice log file: “C:\CalcWork\InvoiceLog.ods”. It would need to be run from a button on sheet2 of “Invoice.ods” and pasting into sheet1 of “InvoiceLog.ods”.
I am doing this invoice for a family member and would like the process to be a simple button click for the usual reasons. Additionally, I realize that a database would probably be a better long term solution.
I know that my problem may seem trivial or dumb or “answered a thousand times” so I'll apologize up front. But I have spent hours trying to find this exact answer. I have read quite a bit of Mr Pitonyak's Useful Macro Information, and numerous posts from a very knowledgeable poster by the name of Villeroy however my implementation, or lack there of, seems to be the problem.
This is all very new to me and I don't know if I'm attacking it from the wrong angle/keyword, or maybe this is much more complicated than it would seem. I find very little on accessing the second file and doing the pasting part. I've tried CopyRange, but maybe it needs to be a GetDataArray SetDataArray type thing. It's a bit overwhelming when you've never tried this stuff before. In any event I'll post what I started working on below. If nothing else it should make for a good laugh.
Maybe there needs to be a thread on what you can't do. But if it can be done, or done in a different way I'd appreciate any solutions.
Thanks,
Tom
I would like to create a macro to copy a cell range(one row, C56:J56) from my invoice template file: “C:\CalcWork\Invoice.ods” and paste it to the first empty row in column A of my invoice log file: “C:\CalcWork\InvoiceLog.ods”. It would need to be run from a button on sheet2 of “Invoice.ods” and pasting into sheet1 of “InvoiceLog.ods”.
I am doing this invoice for a family member and would like the process to be a simple button click for the usual reasons. Additionally, I realize that a database would probably be a better long term solution.
I know that my problem may seem trivial or dumb or “answered a thousand times” so I'll apologize up front. But I have spent hours trying to find this exact answer. I have read quite a bit of Mr Pitonyak's Useful Macro Information, and numerous posts from a very knowledgeable poster by the name of Villeroy however my implementation, or lack there of, seems to be the problem.
This is all very new to me and I don't know if I'm attacking it from the wrong angle/keyword, or maybe this is much more complicated than it would seem. I find very little on accessing the second file and doing the pasting part. I've tried CopyRange, but maybe it needs to be a GetDataArray SetDataArray type thing. It's a bit overwhelming when you've never tried this stuff before. In any event I'll post what I started working on below. If nothing else it should make for a good laugh.
Maybe there needs to be a thread on what you can't do. But if it can be done, or done in a different way I'd appreciate any solutions.
Thanks,
Tom
Code: Select all
Sub ContractThisToSomeoneThatKnowsWhatTheyreDoing
Dim oDocA As Object
oDocA = ThisComponent 'Invoice Sheet Template file, C:\CalcWork\invoice.ods. Macro will someday run from here.
Dim oDocB As Object 'Invoice Sumary Row will be copyed to this Invoice Log Spreadsheet C:\CalcWork\invoicelog.ods.
Dim oSheetA As Object
oSheetA = thisComponent.getSheets.getByIndex( 1 )
oRange = oSheetA.getCellRangeByName( "C56:J56" )
oDocB = StartDesktop.loadComponentFromURL( ConvertToURL( "C:\CalcWork\invoicelog.ods" ), "_blank", _
0, Array() )
cur = oSheet.createCursor
cur.gotoEndOfUsedArea(false)
cur.gotoOffset(0 - cur.rangeAddress.endColumn,1)'Column A, Row end of use area +1
'(AND PASTE Call function(AbraCadabra,Vwala))
End Sub
. Every time I make a macro to solve a problem it seems to create 2 more. However, I'm very close to a one click macro that will copy the invoice to a new file with the invoice number as a name for reference, then log the key information to the invoice log file, and then return to a clean invoice ready to start all over. This is a good start.