Need Code for Project

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
13taha33
Posts: 3
Joined: Thu Jul 06, 2017 8:49 pm

Need Code for Project

Post by 13taha33 »

Hello!

I am working on a project involving calc workbooks, and a code for data extraction would be extraordinarily helpful. I've evaluated exactly what I need this particular macro to do, and have made a very straightforward list of processes it would most likely need. I, unfortunately do not know enough about Basic or even VBA to appropriately guess the code let alone write it perfectly, so any insight into getting this to work would be so helpful.

The abstract is as follows...
I need this macro to search for a user input term, and once it locates that term, look to the cell directly to the left of it (i.f. if I'm looking for "Art1" and it's B13, I need the macro to copy the information in A13). Once it's copied the number in the lefthand cell, I would need it to paste it in first available cell of column G, and repeat through 52 sheets.
In a more manageable light, here is a rock bottom breakdown--

1. User input for search term appears
2. Search first sheet of workbook "Week-1" to find term
3. Once term is located, copy value in first, lefthand cell
4. Paste copied data in last sheet of workbook "Week-52" in first available cell of column G
5. Repeat on next sheet, "Week-2" through "Week-52"

Essentially, each sheet, or "Week-X" is indicative of sales throughout the year (52 weeks in a calendar year), so the search term will appear only once per sheet, and I need to pull the values for each sheet to make up a list of 52 values for use in a trend graph (unrelated in code). Having to go through sheet by sheet and search for the term, log the number, then return to repeat is going to rough, considering there's over 400 pieces of art documented, and I'll have to repeat the whole process 4 times over for each location I'm servicing. A macro to instantly pull the data I need per art name would be monumentally useful.
LibreOffice 5.3.3.2 with MacOS 10.11.06
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need Code for Project

Post by Zizi64 »

I, unfortunately do not know enough about Basic or even VBA
In this case there is a very important question: Who will write the macro code?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
13taha33
Posts: 3
Joined: Thu Jul 06, 2017 8:49 pm

Re: Need Code for Project

Post by 13taha33 »

Excellent question, and the largest part of this project concerning the code is locating someone to brainstorm with. I'm at a complete loss, and I'm looking for someone to assist in its creation. I have a loose understanding of how the language works, but not enough to formulate the code on my own. I suppose more accurately, this post should be geared more toward finding someone who can give me a hand with it all.
LibreOffice 5.3.3.2 with MacOS 10.11.06
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need Code for Project

Post by Zizi64 »

There is a section of this Forum, named: Paid support.

viewforum.php?f=53
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Need Code for Project

Post by Lupp »

First of all: Calc documents with 52 similar sheets, one for each week of a year, should be assumed to have a wrong structure.
Any software project should start with an open-minded look on the actual goals and an open-minded search for the most appropriate means to achieve them. A quick decision the means have to be spreadsheets and macro programming should be suspected to be wrong.
There should in specific be reconsidered if the project is better done with a database.

"1. User input for search term appears" Where? Surely the respective sheets will not just consist of cells waiting for such an input. What if a specific cell is used more than once for input?
"2. Search first sheet of workbook "Week-1" to find term" A Calc sheet does contain 2^20 rows per every one of 2^10 columns, and thus 2^30 cells (logically). You surely won't search 1073741824 cells for a specific content or a pattern. Assuming the routine might need only one microsecond to test a cell for a match, one lookup over a sheet would need roughly 20 minutes otherwise. (In fact a quick test looking through a range of1 000 000 cells by BASIC code needed about 30 s.)
...

Any lookup or matching in spreadsheets should know its ranges. in fact we never look for "anything", but for a specific kind of data, and any sheet of a somehow reasonable structure should contain this kind of data in one (by exception: very few) columns.

The standard way in spreadsheets is to match/lookup based on formulae. In fact formulae using standard functions coded in C++ as a part of the software itself should be assumed to be more effective than amateurish BASIC-or-whatever-script-language code. In fact a quick test of my user made myCOUNTIF against the standard function COUNTIF over the 1 000 000 cells again showed "a few milliseconds (unmeasurable fast with my means) as compared with about 30s" in favour of the standard function. This if most cells were empty. With one million cells containing a bit of text the race ended up 300ms for standard COUNTIF : 42s for the user function. You should use standard functions wherever possible! (This may be significantly different with VBA in Excel which has much more direct access to the inside of the sheets.)

Spreadsheets are made for calculations. Any data keeping in spreadsheets is a misuse in a sense. It may be justified under specific conditions if a clear and rather low limit concerning the number of data sets is assured.

As far as I can see there is no reliable advice based on the minimal information given.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply