Could you help create a macro to copy one line range (as array - text only - no formatting) and paste it to other .ods file? especially to a sheet table name?
copy to other sheet file and name.ods (9.5 KB)
From your sample file:
copy interval range » B4:I4to file » /saved.ods on sheet » INSERTED
paste it to same folder (directory), other ods file, a especifly sheet name
on top of sheet
There are some questions:
- Is there an existing target file, or you need create a brand new spreadsheet file by the macro?
- Do you want rewrite the exiting data located on the target cell rage, or you want to add the new data into the next empty row?
Please study Andrew Pitonyak’s free macro books:
https://www.pitonyak.org/oo.php
And install one of the excellent third party object Inspection tools: XrayTool or MRI - if you want to list the properties and methods of the programming objects.
Merge sheets of spreadsheet documents can do this job.
If the source sheets do not contain any data other than the red cells on your sample sheet, the macro should do the right thing when it copies the used area of the source sheet.
If not, a minor change in the Basic code can fix the source range.
- Line 77 on Basic module “Module1” in library “SheetMerge” has a little syntax error. Append a closing brace. Older versions where not picky about that.
- Line 108: urg = getUsedRange(sh1) can be changed to
sh1.getCellRangeByName("B4:I4")
in order to always fetch the same range.
3) On the setup sheet, set the TopInsert option to 1. If you keep all the other options empty, all first sheet’s of all *.ods files in the same directory will be merged to the row below the cell named “Target”.
@rickcosp: You need only to study the methods Stardesktop.loadComponentFromUrl for open the ODS file, and then getDataArray and setDataArray for copy/paste.
1 Like
get/setDataArray is exactly what my macro does for each file in a specific folder while keeping the information about the source file, so each list entry can be tracked back to its origin. It expands the target range, so all the formulas, names, chart ranges, conditional formats etc. stay intact within the target document.
1 Like
1- Yes, it is to paste into an existing file name as target (inserted.ods) and to an existing sheet table (tab) called eg.:”M2”.
2 - The main ideia is to put on the top of the table of contents. So is needed to put down all content on the destination sheet. And paste always to the same location. Always on the top of data table.
@Zizi64 thank you zizi64 I will check it.
I hope all tips to solve that mission arround.
@Villeroy my ideia is export the data to an ods file. This file inserted.ods is hold all data classified by sheet name (tab name).
After a time, I will save all data to csv file. And that csv file will be converted to JavaScript object.
Or to JSON using php function that I created. In that case I will transform to JavaScript object. And it will be get filtered informations to HTML page.
1 Like
As you already used the tag “database” you might consider also to use a database to store your data. ( I guess you know PHP works nicely with databases…)
1 Like
What are you wanting to do in the grand scheme of things? If you are wanting to manipulate CSV files or save CSV files from portions of other sheets then I could post another example a little more taylored perhaps than @Villeroy’s SheetMerger.
or write cell values directly into the database instead of csv.
Or create the JavaScript object (I mean all syntax of this object) or JSON by Libre macro 
- without the transformation via CSV.
If that means: “by BASIC Makro” so its same nonsense again!
@karolus: I thought Basic or Python :-).
For example I use really no-sexy Basic code to get Msgbox with the coordinates of curve in Draw, then I select the code in Msgbox and CtrlC/V to the Sub in Basic editor.
For test: run Draw, Insert/Textbox - write some letter to Textbox; then convert Textbox to Curve (Shape/Convet/To Curve) and run the macro. It shows some code for CtrlC/V.
Sub infoKrivka3 'show info for CtrlC/V
initChar
dim oDoc as object, o as object, i&, s$, p(), minX&, minY&, ss$, sPodtr$, sSize$, sSize2$, sNazev$, ps() as string, sPole$, bPole as boolean, ss2$
sPodtr=", _" & chr(13) & chr(9) & chr(9)
oDoc=ThisComponent
o=oDoc.CurrentController.getSelection.getByIndex(0) 'selected Curve
'sNazev=inputbox("Name & UFT8 hexa code --- !!! SEPARATE BY SPACE!!!")
sNazev="example e202"
if sNazev="" then exit sub
ps=split(sNazev, char.mezera)
if ubound(ps)<>1 then 'only name so Code will be -1
if ubound(ps)=0 then
ss2="-1"
else
msgbox("Something is wrong - name or hexa code", 16)
exit sub
end if
else 'convert code to hexa
bPole=true
sNazev=ps(0)
ss2=CLng("&H" & ps(1))
end if
sSize=chr(9) & "with gTvary." & sNazev & chr(13) & chr(9) & chr(9) & ".Popis=""" & sNazev & """" & chr(13) & chr(9) & chr(9) & ".Code=" & ss2 & chr(13) & chr(9) & chr(9) _
& ".Width=" & o.Size.Width & chr(13) & chr(9) & chr(9) & ".Height=" & o.Size.Height & chr(13) & chr(9) & chr(9)
sPole=chr(13) & chr(9) & "gTvary.pole(gTvary." & sNazev & ".Code)=gTvary." & sNazev
sSize2=chr(13) & chr(9) & "end with" & iif(bPole, sPole, "") & chr(13) & chr(13)
s=s & sSize
ss=".Coordinates=array( array("
p=o.PolyPolygonBezier.Coordinates(0)
minX=p(0).X : minY=p(0).Y
for i=lbound(p)+1 to ubound(p) 'get smallest value
if p(i).X<minX then minX=p(i).X
if p(i).Y<minY then minY=p(i).Y
next i
for i=lbound(p) to ubound(p)
ss=ss &"createPoint("& p(i).X-minX & "," & p(i).Y-minY &")"
if i<ubound(p) then
if len(ss)>180 then 'string is very long so put the _ and new line
s=s & ss & sPodtr & chr(9)
ss=""
else
ss=ss & ", "
end if
end if
next i
s=s & ss & ") )" & chr(13)
msgbox s
End Sub
This way isn’t so glamorous, it is not so easy to write it with indents (Tabs, Enters, maximal lengths of lines), but then it is easy to use - of course you need “love” CtrlC/V :-).
But I think it could be also the solution or part of solution - I don’t know how difficult and large data @rickcosp wants to utilize.
if your example should demonstrate how to “string_ify” information, and use similar code to “build” .json??
no!
thats not the solution.