Page 1 of 1
[Solved] Return to Sender
Posted: Wed Jan 10, 2024 4:42 pm
by 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
Re: Return to Sender
Posted: Wed Jan 10, 2024 6:22 pm
by 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?
Re: Return to Sender
Posted: Thu Jan 11, 2024 6:59 pm
by 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.
Re: Return to Sender
Posted: Thu Jan 11, 2024 7:06 pm
by 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
Re: Return to Sender
Posted: Thu Jan 11, 2024 7:55 pm
by Gonzo714
Works Perfectly
Re: [Solved] Return to Sender
Posted: Fri Jan 12, 2024 1:35 am
by 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
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 5:51 am
by 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?
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 1:02 pm
by 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)
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 3:06 pm
by 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
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 7:34 pm
by FJCC
Gonzo714 wrote: ↑Fri Jan 12, 2024 3:06 pm
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?
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 8:01 pm
by 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.
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 8:23 pm
by 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.
Re: [Solved - with Addon] Return to Sender
Posted: Fri Jan 12, 2024 9:33 pm
by 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.