| View previous topic :: View next topic |
| Author |
Message |
Francesco
Guest
|
 Posted: Thu Feb 12, 2004 5:17 pm Post subject: Copying contents (WO format) of a range of cells. Any help? |
 |
|
I want to copy contents (formulas, values or text) of a source range of cells to target position, without transferring range format too. How could I modify this macro to get this result?
Sub CopyRange
Dim oSheet1 as object
Dim oSheet2 as object
oSheet1 = ThisComponent.Sheets.getByName("Sheet2")
oSheet2 = ThisComponent.Sheets.getByName("Sheet1")
oRangeOrg = oSheet1.getCellRangeByName("B23:B28").RangeAddress
oRangeCpy = oSheet2.getCellRangeByName("B23:B28").RangeAddress
oCellCpy = oSheet2.getCellByPosition(oRangeCpy.StartColumn,_
oRangeCpy.StartRow).CellAddress
oSheet1.CopyRange(oCellCpy, oRangeOrg)
End Sub
Thanks for help
Francesco
fpignatelli@gmx.it |
|
| Back to top |
|
 |
Ian Laurenson
Guest
|
| Posted: Thu Feb 12, 2004 11:31 pm Post subject: Only paste special in API deprecated so here is my routine |
|
|
I couldn't find a pastespecial for a range of cells in the api that wasn't deprecated so I wrote this routine. Hope it helps - Ian Laurenson
| Code: |
subCopySpecialValues(oCellCpy, oRangeOrg)
sub subCopySpecialValues(oDest as object, oSource as object)
dim oDoc as object, oSheetSource as object, oSheetDest as object
dim i as integer, j as integer, tmp
oDoc = StarDesktop.CurrentComponent
oSheetSource = oDoc.sheets.getByIndex(oSource.sheet)
oSheetDest = oDoc.sheets.getByIndex(oDest.sheet)
for i = oSource.startRow to oSource.endRow
for j = oSource.startColumn to oSource.endColumn
tmp = oSheetSource.getCellByPosition(j, i)
if isNumeric(tmp.string) then
oSheetDest.getCellByPosition(j + oDest.column, i + oDest.row).value = tmp.value
else
oSheetDest.getCellByPosition(j + oDest.column, i + oDest.row).string = tmp.string
end if
next
next
end sub
|
|
|
| Back to top |
|
|
Ian Laurenson
Guest
|
| Posted: Fri Feb 13, 2004 8:05 pm Post subject: Din't allow for dates in my paste special routine |
|
|
 Sorry I didn't allow for dates in my paste special routine - hope it's obvious what bit of code this should replace:
| Code: |
if isnumeric(tmp) or isdate(tmp) then
oSheetDest.getCellByPosition(j + oDest.column, i + oDest.row).value = _
oSheetSource.getCellByPosition(j, i).value
else
oSheetDest.getCellByPosition(j + oDest.column, i + oDest.row).string = tmp
end if
|
Cheers, Ian |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
| Posted: Fri Feb 13, 2004 11:19 pm Post subject: |
|
|
Copy a sheet
| Code: |
Dim oDocument As Object, oSheets As Object
oDocument = ThisComponent
oSheets = oDocument.Sheets
oSheets.CopyByName("sheet1", "Copy", 2)
|
create a copy in third position (index 2) of sheet1 with a sheet's name : Copy
To copy a cell range i have found in OOoBasic documentation :
| Code: |
Dim oDocument As Object, oSheet As Object
Dim oCellRangeAdress As New com.sun.star.table.CellRangeAddress
Dim oCellAddress As New com.sun.star.table.CellAddress
oDocument = ThisComponent
oSheet=oDocument.sheets(0)
oCellRangeAddress.sheet=0
oCellRangeAddress.StartColumn = 1
oCellRangeAddress.StartRow=1
oCellRangeAddress.EndColumn=2
oCellRangeAddress.EndRow=2
oCellAddress.Sheet=1
oCellAddress.Column=0
oCellAddress.Row=5
oSheet.copyRange(oCellAdress,oCellRangeAddress)
|
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
Guest
|
| Posted: Sat Feb 14, 2004 12:11 am Post subject: |
|
|
Thank you Ian and SergeM, I were just a little less newby, I am sure I had solved my problem applying your suggestions, but after a lot of trying, I did'nt get much more but frustration and error messages 
Here is what I need.
Copy only contents (in my case strings, formulas and numbers, but NOT formats) of range A1:A6 from Sheet2 to B5:B10 of Sheet1, without changhing cell formats of destination range.
Can you (or anybody else) send me a code with all parameters already set  ?
I can get a macro for that with the macro recorder and the command "Past special", that way, the macro switches the active sheet to the sheet containing the source range and then to the one containing to the destination. Rather messy.
Thanks |
|
| Back to top |
|
|
Francesco
Guest
|
| Posted: Sat Feb 14, 2004 3:56 am Post subject: |
|
|
I have solved this way (resumed):
Sub Copia_Pond
For i = 22 to 27
oDoc = ThisComponent
oSheet = oDoc.Sheets.getByName("Sheet2")
oSourceCell = oSheet.getCellByPosition(3,i)
oSheet = oDoc.Sheets.getByName("Sheet1")
oTargetCell = oSheet.getCellByPosition(3,i)
oTargetCell.Formula = oSourceCell.Formula
Next i
End Sub
Thak you Ian for giving me the "For...Next" idea.
I'd like all the same to know about other possible solutions.
Bye
Francesco |
|
| Back to top |
|
|
dfrench
Moderator
Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand
|
| Posted: Sat Feb 14, 2004 11:51 am Post subject: |
|
|
It is generally faster to operate on ranges rather than iterate through cells so consider the following code ...
| Code: | Sub Macro1
oDoc = ThisComponent
oSourceRange=oDoc.Sheets.getByName("Sheet1").getcellrangebyposition(3,22,3,27) '
oTargetRange=oDoc.Sheets.getByName("Sheet2").getcellrangebyposition(3,22,3,27)
oTargetRange.setformulaarray(oSourceRange.getformulaarray())
End Sub |
In any event, it is good practice to avoid doing things within the for/next loop that can be done outside. |
|
| Back to top |
|
|
Ian Laurenson
Guest
|
| Posted: Sat Feb 14, 2004 2:12 pm Post subject: |
|
|
Thanks dfrench,
I had completely overlooked getFormulaArray as I had getDataArray (when I misread the original posting).
So the original macro could be rewritten as;
| Code: | Sub subCopyRange()
dim oDoc as object
Dim oSheet1 as object, oRangeOrg as object
Dim oSheet2 as object, oRangeCpy as object
oDoc = StarDesktop.CurrentComponent
oSheet1 = oDoc.sheets.getByName("Sheet1")
oSheet2 = oDoc.sheets.getByName("Sheet2")
oRangeOrg = oSheet1.getCellRangeByName("B23:B28")
oRangeCpy = oSheet2.getCellRangeByName("B23:B28")
oRangeCpy.setFormulaArray(oRangeOrg.getFormulaArray)
End Sub |
Cheers, Ian |
|
| Back to top |
|
|
dech
General User
Joined: 30 Aug 2010
Posts: 8
|
| Posted: Wed Oct 20, 2010 12:49 pm Post subject: |
|
|
Hello,
I want to use this:
| Code: | sub copy_range_and_paste_it_to_another_cell
oSheet = thisComponent.Sheets.getByIndex(0)
oSheet1 = thisComponent.Sheets.getByIndex(1)
source = oSheet.getCellRangeByName("A1:z100").getRangeAddress()
destination = oSheet1.getCellByPosition(0,0).getCellAddress()
oSheet.copyRange( destination , source )
End sub |
but It would be nice to let it paste all but not objects, sometimes only values ect. - is there any possibility to set flags with "copyRange()" ?
I dont want to use macro recorder(UNO) because I need it work in hidden sheet.
Thank you.
_________________
W 7 , OO 3.2.1 |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
