[Solved] Return to Sender

[Gonzo714]

I have a file with multiple sheets.
From any active sheet within the file, i need to jump over to a specific sheet & cell, copy its value, then return to the previous sheet.
I achieved the return feature with a simple macro of:

Code: Select all

Sub Go_To_Last_Sheet()	
with thiscomponent.currentcontroller 	
.setActiveSheet(thiscomponent.sheets(thiscomponent.sheets.count -1))	
end with	
End Sub	
	

...but the active sheet in question isnt always the last sheet in line (as the file keeps adding sheets)
Does BASIC have the ability to 'remember' which sheet it was called from, then returning to it?


Im using Debian 12 with LO 7.5

[FJCC]

If I understand you correctly, you don't need to do any jumping between sheets. Let's say you are working on Sheet3 and you need to get the value of L4 on Sheet1 and put it in B3 of the current sheet

Code: Select all

oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
CellL4 = oSheet1.getCellrangeByName("L4")
Val = CellL4.Value
CurrSheet = ThisComponent.CurrentController.ActiveSheet
CellB3 = CurrSheet.getCellrangeByName("B3")
CellB3.Value = Val

I just typed that without testing it, but that is a sketch of what I think you want to do. Is that right?

[Gonzo714]

Thank you for your reply, and I do like your thoughts there, however, for others who are looking for answers, i do get an error message of
Action Not Supported - Invalid Procedure Call on the line of: Val = CellL4.Value

I do like the idea, and will work on it.

[FJCC]

Oops! This works.

Code: Select all

Sub GetL4
oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
CellL4 = oSheet1.getCellrangeByName("L4")
Val_L4 = CellL4.Value
CurrSheet = ThisComponent.CurrentController.ActiveSheet
CellB3 = CurrSheet.getCellrangeByName("B3")
CellB3.Value = Val_L4
end sub

[Gonzo714]

Works Perfectly

[Gonzo714]

Addon Question.....
First off, this script is so much smoother than the traditional Copy/Paste macro.
I discovered this macro will only transfer (numberical) values, where a formula (string) was required.
A simple change to the script solves that issue, but for one of the strings I need it to be (pasted as) unformated text.
Im at a loss how to achieve this for this script

Code: Select all

Sub GetL4_String
oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
CellL4 = oSheet1.getCellrangeByName("L4")
Val_L4 = CellL4.String
CurrSheet = ThisComponent.CurrentController.ActiveSheet
CellB3 = CurrSheet.getCellrangeByName("B3")
CellB3.String= Val_L4
end sub

EDIT: I think this might be achieved if a RANGE of cells with strings could be transfered, but altering the "L4" to "L4:P4" isn't doing this.
Also, assuming the secondary cells should match in size of the primary, altering "B3" to "B3:F3" also isnt working

The error i get with this as a RANGE of cells is Method Not found:String for the line Val_L4 = CellL4.String

[FJCC]

Yes, you can't use .String or .Value on a range. I'm confused about what you need. Do you need the String in each of a range of cells copied, or the formula of each of a range of cells, or something else? There is a function for simply copying a range of cells to a destination. Would that do what you want?

[Gonzo714]

yes, looking back i did ramble on a bit.... my bad!

I'll just condence it down to this request:
Can the script be altered to transpose a RANGE of cell data versus a single cell? - kinda like:

Code: Select all

Sub GetL4_String
oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
CellL4 = oSheet1.getCellrangeByName("L4:P4")
Val_L4 = CellL4.String
CurrSheet = ThisComponent.CurrentController.ActiveSheet
CellB3 = CurrSheet.getCellrangeByName("B3:F3")
CellB3.String= Val_L4
end sub

and then yes, I do need both varibles if possible, the string for some cells, and the formula for others
(Im assuming that may have to be done by multiple scripts, which is fine)

[Gonzo714]

Think i got it - this will transpose the RANGE of data, but is there a way to "PasteOnlyUnformatted" the data?

Code: Select all

Sub GetL4_String
oSheet1 = ThisComponent.Sheets.getByName("Sheet1")
CellL4 = oSheet1.getCellrangeByName("L4:P4")
Val_L4 = CellL4.DataArray
CurrSheet = ThisComponent.CurrentController.ActiveSheet
CellB3 = CurrSheet.getCellrangeByName("B3:F3")
CellB3.DataArray= Val_L4
end sub

[FJCC]

is there a way to "PasteOnlyUnformatted" the data

I'm not sure what you mean. The DataArray doesn't carry any formatting information, so using that is effectively Paste Unformatted, isn't it?

[Gonzo714]

one would think so, but if I manualy select and copy the same cell range, and past-only into the 2nd sheet, i get the same results as this script. If I Paste-special-UnformatedText I get the needed results.
Is there possibly a method of sending the .dataarray to the clipboard then i can force the Paste:UnformattedText ?

if not, then i appreciate all your effort.

[FJCC]

Can you upload a file showing the data to be copied and the two different results of paste-unformatted and the macro? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.

[Gonzo714]

I am not permitted to upload the file, its not my property.
So in making another version to upload here, I discovered my issue is based on the way the cells are designed.
Your script works perfectly for a sheet that is designed with 'normal thinking' in mind, however instead of mere formulas or values, these contents are hyperlinks (with that sheets name embeded within) that allow you to link between sheets, which is kinda cool.
Therefore, I now understand why your script isnt working on this design, and why unformatted pasting is required.
I appologize for wasting your time (and mine) with this addon question.

Thank you for your time and effort.