[Solved] Copy and paste contents of cells across sheets

[zabolyx]

I'm currently trying to write a macro in OOoBASIC.

Code: Select all

Sub Main

Dim ColPosition as Integer
Dim RowPosition as Integer
Dim Doc as Object
Dim SheetCopy as Object
Dim SheetPaste as Object
Dim CellCopy as Object
Dim CellPaste as Object
Dim Contents as String

Doc = ThisComponent 'sets Doc as the current spreedsheet
SheetCopy = Doc.Sheets.getByName ("Import") 'Assigns the Import sheet as the sheet to copy from
SheetPaste = Doc.Sheets.getByName ("Paste") 'Assigns the Paste sheet as the sheet to paste to


For RowPosition = 0 to 249 'count through the rows of the import sheet to 
	For ColPosition = 0 to 12 'count through the columns that need to be copied
		CellCopy = SheetCopy.getCellByPosition (RowPosition, ColPosition) ' select cell to copy
		Contents = Cstr(Cell) ' copy cell contects as string to contents variable
		CellPaste = SheetPaste.getCellByPosition (RowPosition+2, ColPosition+5) 'select the cell to paste the copied info into
		CellPaste.String = Contents 'paste the contents variable into the selected cell
	Next ColPosition 'move to next column
Next RowPosition 'move to next row

End Sub

This code is supposed to copy the contents of columns A - M rows 1 - 250 from the sheet called Import and paste that into into columns B-N rows 5 - 254 on the sheet named Paste.

I'm also needing to create a button to attach it to. The module is called ImportOverwrite. I can create the button but need to know how to attach the module to the button so that it executes.

Any help is appreciated.

Title Edited to remove [OOoBasic]. Elements at the start of titles in square brackets have special meanings, like [Solved] or [Issue]. Better to re-word things as I have done for you. (TheGurkha, Moderator)

[FJCC]

There is a CopyRange method available that is easier to use than copying one cell at a time. Here is an example of how to use it.

Code: Select all

Doc = ThisComponent
Sheets = Doc.Sheets()
SheetCopy = Sheets.getByName("Import")
SheetPaste = Sheets.getByName("Paste")
CopyRange = SheetCopy.getCellRangeByName("A1:M250")
PasteCell = SheetPaste.getCellRangeByName("B5")
CopyAddress = CopyRange.RangeAddress
PasteAddress = PasteCell.CellAddress
SheetCopy.CopyRange(PasteAddress, CopyAddress)

The documentation on this method is here.
In your code, the variable Cell in this line

Code: Select all

Contents = Cstr(Cell)

doesn't get assigned any value beforehand, so the Contents variable is empty.

[zabolyx]

Thanks FJCC. That looks simple enough. Since this is a learning exercise ....

Cell is an object so the variable itself can not contain any data. I need to use a property of said object like cell.contents, but that is something I am confused about. I've seen cell.value, cell.string but these items specify what the contents type is. In my code I was needing to pull the contents of a cell no matter what the type is. How would one do that?

I have another macro to write (it looks like FJCC is the author of the one I will use, Thank FJCC) that is to check the contents of a cell for the data to know where to start pasting the cell range.

[Villeroy]

Every cell has a formula, a numeric value and a string at the same time.
If the formula is numeric, the value is the number and the string is the formatted output of that number.
If the formula is a text, the value is zero and the string is just that text.
If the formula is not a text and starts with "=", the value is either the formula's numeric return value or zero if the formula retruns a string. The string is either the formatted return value or the returned text.

Pseudo-property "Value" (methods get/setValue) always use the unformatted English notation for a floating point number with dots as decimal points (1.2345).
Pseudo-properties "Formula" and "FormulaArray" always use the English notation for formulas with English function names. Property "FormulaLocal" can be used for user interaction by means of localized formulas.

[zabolyx]

Villeroy: So cell.value referrers to the contents of the cell. If it is a string or value doesn't matter. So the variable I would store the contents of the cell in should be non-specified to allow the string or value to be held.

FJCC: the code works great. I do have one issue that I've come across. When it pastes the information onto the Paste sheet it is pasting B1:N250. Although the copy code has A1:N250.

I've included parts of the workbook to play with. Please feel free to play with it and let me know what I'm doing wrong.

Wait I know what is wrong. I need it to copy plain text from the Import sheet. The macro you gave me is copying the formula and therefore is incrementing the formula by the 1 column change.

What changes would I need to get the macro to copy only the values from the copy range of cells?

[FJCC]

I think this code does what you want. I don't see any formulas in your Import sheet. I tried a sheet of my own with formulas and this approach copies the results of the formula, whether text or a value.

Code: Select all

	Doc = ThisComponent
	Sheets = Doc.Sheets()
	SheetCopy = Sheets.getByName("Import")
	SheetPaste = Sheets.getByName("Paste")
	CopyRange = SheetCopy.getCellRangeByName("A1:M250")
	PasteRange = SheetPaste.getCellRangeByName("B5:N254")
	PasteRange.DataArray = CopyRange.DataArray

It would be better to post a separate thread for your question about tying the macro to a button. It would make it a lot easier for other to find.

[zabolyx]

Thank you....

I'll do that with the button question.

So the DataArray property referrers to the output of a cell (such as one with formula in them)?

[Villeroy]

The DataArray is a nested array of text and numbers within a range of cells. Empty cells are represented by empty strings, error values are represented by Basic Null. The array contains arrays of rows.
[ (A1,B1,C1,...X1) , (A2,B2,C2,...X2) , (A3,b3,c3,...X3) ]

You've got to install the MRI extension http://extensions.services.openoffice.o ... h/node/mri
At least you should use the debugging tools built into the Basic IDE.

[zabolyx]

Thanx guys. That's working great.

Part two is using that same code but reading the contents of a cell that lets me know how many items are already on the page and adjust the paste location to 1 row below the last record.

Here is what I have. It is giving me an error on the marked line. I'm trying to compose a string for the PasteRange = SheetPaste.getCellRangeByName(NewPosition) line. So if the Contents variable is 50 then the NewPosition should come out as the string B55:N304.... At least that is what I'm trying for.

The error I get is com.sun.star.uno.RuntimeException then the message field is just a "."

It worked fine until I added the new lines to build the modified value for use in the PasteRange = SheetPaste.getCellRangeByName(NewPosition). I thought because the original code had the range as a string so I figured placing a string variable in there wouldn't hurt. Obviously it did.

I tried to add NewPosition into the watchlist in the IDE. It did not seem to pull the variables current value. The watchlist is for monitoring variables contents as the macro runs correct? Is there any good documentation on the use of the IDE? I do have the OpenOffice.org BASIC Guide and I have been reading that through as I go. As well I have the books "Learn OpenOffice.org Spreadsheet Macro Programming" and "StarOffice Programmer's Tutorial" which between the 2 come to about 700 pages. I could use one on the IDE if anyone knows where to get it.

Code: Select all

	Dim Doc as Object
	Dim Sheets as Object
	Dim SheetCopy as Object
	Dim SheetPaste as Object
	Dim CopyRange as Object
	Dim PasteRange as Object
	Dim Cell as Object
	Dim Contents as Integer
	Dim NewPosition as String
		
	Doc = ThisComponent
	Sheets = Doc.Sheets()
	SheetCopy = Sheets.getByName("Import")
	SheetPaste = Sheets.getByName("Paste")

	Cell = SheetPaste.getCellByPosition (0,0)
	Contents = Cell.Value
	NewPosition = "B5" & CSTR(Contents+5) & ":N" & CSTR(Contents + 254)	

	CopyRange = SheetCopy.getCellRangeByName("A1:M250")
	PasteRange = SheetPaste.getCellRangeByName(NewPosition)
-->	PasteRange.DataArray = CopyRange.DataArray

[FJCC]

You get the error because of this line

Code: Select all

NewPosition = "B5" & CSTR(Contents+5) & ":N" & CSTR(Contents + 254) 

I think it should be

Code: Select all

NewPosition = "B" & CSTR(Contents+5) & ":N" & CSTR(Contents + 254) 

[zabolyx]

Sir... or Ma'am... that did it. Thank you kids. I feel like an idiot overlooking that.

It works beautifully... I'll be able to reuse this code in my next set of projects. Thanks again.

[sceptical-h]

The DataArray is a nested array of text and numbers within a range of cells. Empty cells are represented by empty strings, error values are represented by Basic Null. The array contains arrays of rows.
[ (A1,B1,C1,...X1) , (A2,B2,C2,...X2) , (A3,b3,c3,...X3) ]

This thread has finally solved my copy/paste problems. Thanks. But where is the DataArray method of the Range object documented?

[Villeroy]

http://extensions.services.openoffice.org/project/MRI