[Solved] Macros help

[Ciakimba]

Hello I'm new to Macros and want to create a macro whit some functions. Basically i wonder if it is possible and solution how to to this.

I use OO for importing my product barcodes and prices into web shop to renew my stocks and prices daily. Here is what I do:
1.Export all data from my accounting system into excel work sheet (cube.xls) there is no other option to export only xls
2.In file is around 60k products for 6 different shops all in one sheet
3.I drag xls file to OO and OO opens it only with 16k products :/ I solved this by opening xls file and saving it as xlsx (maybe there is other simple way ?)
4.Than I drag xlsx file to OO i there my main question starts:
5. Have some cells that must be deleted for example from 1-3 delete for A1:AMJ3
6. SUM B and H cell and paste result to B cell B1:B1048576+H1:H1048576
7. Delete H, I, G, E cells H1:H1048576, I1:I1048576, G1:G1048576, E1:E1048576
8. find all "`" and replace " " (blank) remove apostroful from a barcode start
9. stand on F2 cell and make this function: =C2*0,9
10. Make above formula for this column-> F2:F1048576
11. Stand on G cell copy F1:F1048576 and paste as numbers
12. Delete F and G cells
13. rename A1 cell to b.kodas, rename B1 cell to cent, rename C1 cell to sand1, rename D1 cell to sand2 and E1 to kaina
14. Select all and make as text
15 save as as csv file utf8 format

So here is my question is it possible to make this with macros ? (doing it daily make some pain in a...)

Thank you for your solutions and answers and sorry if this question was already on forum (I could not find the key word how to search an answer).
Regards Karolis S.

[Lupp]

Basically "next to everything" can be done by custom code. The relevant question is if YOU can write the needed code. If you want to try it based on the "Macro Recorder": Poor odds.

To do something relevant in OpenOffice Basic you need to study the guide to Basic and the API - and at least as urgently - the famous texts by Andrew Pitonyak "OOME" (the "book") and "Useful Macro Information" (a kind of a brain dump).

Commenting on your 15 detailed questions might just cause a waist of time for anybody not sure about having correctly understood the questions/intentions. To help any contributor to understand, you should first create a reduced example of what you have to open in AOO (the first version of your .ods). Commenting on .xls and .xlsx conversion is always more of a guessing for somebody neither having the respective source files nor the MS software.

The next step should be to prepare the according files (.ods and .csv) showing what you want to get from the above mentioned source.

Then attach the three files to your next post.

"Reduced" should mean: Heavily shortened, but not leaving out examples for relevant exceptions that might occur.

[Ciakimba]

Thank you for your detailed reply, basically you answered what i wanted to hear by custom code it`s possible. So my next question would be if it`s hard to find person who could help me write this code ? (reading all mentioned information for me is not possible due lack of time). Thank you one more time.

[JeJe]

Ciakimba - you've set out what you need to do in step - so take each one and google to find an example that does it.

I just googled - openoffice macro delete cells

and found this simple tutorial:

https://www.debugpoint.com/2015/02/dele ... ing-macro/

You can lean how to do that in a couple of minutes with that - and create a macro for that.

This forum is another place to search. You don't necessarily need to understand the code to use it - look for a bit that does what you want and experiment with it in a copy of one of your documents (so you can't lose data in the ones that matter)

Do this for all the liitle bits and see how far you can get... then come back and post the bits you can't work out (in separate threads maybe)... people will be much more likely to help when you're presenting one of these bits of your problem that you've tried to solve but can't - than they are to want to write all your code for you.

[Ciakimba]

Ciakimba - you've set out what you need to do in step - so take each one and google to find an example that does it.

I just googled - openoffice macro delete cells

and found this simple tutorial:

https://www.debugpoint.com/2015/02/dele ... ing-macro/

You can lean how to do that in a couple of minutes with that - and create a macro for that.

This forum is another place to search. You don't necessarily need to understand the code to use it - look for a bit that does what you want and experiment with it in a copy of one of your documents (so you can't lose data in the ones that matter)

Do this for all the liitle bits and see how far you can get... then come back and post the bits you can't work out (in separate threads maybe)... people will be much more likely to help when you're presenting one of these bits of your problem that you've tried to solve but can't - than they are to want to write all your code for you.

Thank you

[robleyd]

Is your "xls" file really an Excel format file or a csv file in disguise? If it is csv you can use the Text Import feature of Calc to import and perhaps do some of the conversion you require.

[Ciakimba]

Is your "xls" file really an Excel format file or a csv file in disguise? If it is csv you can use the Text Import feature of Calc to import and perhaps do some of the conversion you require.

My accounting system generates xls file and then by help of AOO i save this file as CSV for e-shop reasons (it only accept csv file type for import)

[robleyd]

What is the accounting package you use?

[Ciakimba]

What is the accounting package you use?

It`s Lithuanian based only "Agnum"

[robleyd]

Unfortunately I can't read the language of the website; my thought was that the software may actually create a comma separated data file and label it as .xls - I believe Excel will happily open such files.You would need to examine the file with say a text editor to see if this is the case. If so, you might be able to eliminate a few steps in your process.

[Lupp]

7. Delete H, I, G, E cells H1:H1048576, I1:I1048576, G1:G1048576, E1:E1048576

This kind of "detailing" is one of the reasons for what I don't like to answer in detail to such a question.
Shall the cells actually be "deleted" in the sense of being removed from the sheet (and replace by something else depending on the CellDeleteMode) what is feasible using the Spreadsheet method removRange(RangeAddress, CellDeleteMode), or shall they be "deleted" in the sense of getting cleared some kinds of contents or attributes (depending on the CellFlags set in the nContentFlags parameter) what is feasible using the SheetCellRange method clearContents(nContentFlags).

Similar lack in clarity may be found in the descriptions of other steps whether I have noticed that or not.
Stepping in on such a task means a risk of wasting time if not things are made definitely clear in advance.

Another question that surely needs an answer:

5. Have some cells that must be deleted for example from 1-3 delete for A1:AMJ3

What shall ("delete" again and) "for example" mean here. A line of code can act on a specific range, but how should a change in that range be introduced? Parameters? How? An extra Sub for every occurring case? How many cases under what conditions?

I wolud need the explaining example files I asked for in my first post here to prepare the next step in finding advice.