Site-to-Excel copying cycling

[JohnJ2717]

I tried to loop the recorded macro of copying data from the site to excel, but could not figure out how to replace keystrokes with a counter (on the site, data is entered through 0.01 to 2 and 0.1 after 2; in excel, the columns switch to the right 10 times, then skip column and repeat), and actually make the whole macro repeat. Unfortunately, I don't know Java and now I'm learning it on the go.https://drive.google.com/drive/folders/ ... share_link

[Dorian (MJT support)]

Looking through your screenshots it seems that your recording does the following :

Focuses an existing Chrome Session, selects and copies a table (CTRL-V)
Opens Word, pastes the data, then copies the data again
Opens Excel, pastes the data

MouseMoves/Copying/Pasting from browser to Excel is not the recommended way to do this. Certainly, it can work (even though I still don't recommend it) if you're wishing to perform exactly the same actions over and over, but this isn't what we're looking for. You're needing something a little more complex.

I recommend learning the way to do this programmatically. It will stand you in far better stead for the future, too.

Please make sure to visit the following links and read these resources thoroughly. Everything to need to know is there, including usage examples and detailed descriptions. Start with small and modest tests, starting a browser session, and navigating. Once that works, try entering data. Once you've conquered that, try extracting the results. Take a similar step-by-step approach as you learn the Excel and looping functions. Once you understand all of those, try putting it all together.

Macro Scheduler has built-in browser functions that can navigate to a page, enter data, and extract the results. If you still have Internet Explorer, we can use the Find IE Element Wizard to get us started, then use IEExtractTable or IEExtractTableByName.

There is not an equivalent command for Chrome or Edge, but with a little more work you can extract whatever you like. See Using Macro Scheduler 15’s Chrome Automation Functions

Then we move on to Excel : List of Macro Scheduler Excel Functions. The basics here would be XLOpen and XLSetCell.

Lastly, the looping :

How to Loop the Loop
Can I Loop or Repeat my Macro?
Tight Loops - Giving Back to the Processor

I certainly understand this is quite a learning curve. We've all been there.

[JohnJ2717]

Focuses an existing Chrome Session, selects and copies a table (CTRL-V)
Opens Word, pastes the data, then copies the data again
Opens Excel, pastes the data

right

There is not an equivalent command for Chrome or Edge, but with a little more work you can extract whatever you like. See Using Macro Scheduler 15’s Chrome Automation Functions

I seem to be starting to understand this, but not nearly as well as I would like

If you still have Internet Explorer

Unfortunately no
I don't quite understand the meaning of "value" and "res"

Code: Select all

XLSetCell>xlBook,SheetName,row,column,value,res

maybe because I translate all the manuals when reading it in English
How to check the presence of information in a cell without reading the contents?
How to build interaction with the Word and can this be avoided, if on site table columns made with "td" in both columns, when I need only right?

I certainly understand this is quite a learning curve. We've all been there.

To be honest, in this particular situation, at the very beginning, I would prefer to order a ready-made script, and then, as a personal initiative, study it (now I’m already interested in studying it), but there is a catch: I don’t have and never had any electronic means of payment - so I study everything I can

[Dorian (MJT support)]

I don't quite understand the meaning of "value" and "res"

Code: Select all

XLSetCell>xlBook,SheetName,row,column,value,res

As it says in the help file :

Result will contain zero if the operation was successful, otherwise will contain the Excel error message.
Value is the value to set

But if in doubt - try it. And see my note at the bottom of this post regarding debugging.

How to check the presence of information in a cell without reading the contents?

An Excel cell? XLGetCell, XLGetSheetDims

How to build interaction with the Word and can this be avoided,

It is not necessary at all. I fact copying and pasting to Word just to copy it out again is counter-intuitive.

if on site table columns made with "td" in both columns, when I need only right?

Once you have mastered the Chrome techniques, you can use those, and if required, some string handling.

I would prefer to order a ready-made script, and then, as a personal initiative, study it (now I’m already interested in studying it), but there is a catch: I don’t have and never had any electronic means of payment - so I study everything I can

I'm assuming you're still a trial user. You might struggle to learn all this and get it up and running before your 30 days run out. There are people here who would create a script for you, but of course they would charge for that.

I also recommend Using Macro Scheduler's Debugger to Aid Script Creation

[JohnJ2717]

As it says in the help file

Thanks, it helps

It is not necessary at all. I fact copying and pasting to Word just to copy it out again is counter-intuitive.

This was necessary due to the impossibility of copying the right column separately from the left one, and now I'm not sure if it's worth abandoning the Word at all, since trying to read through xpath gives an error (this and other errors in the script file excel1 folder on googledrive)

You might struggle to learn all this and get it up and running before your 30 days run out

I only have 4-5 days to figure this out.

[Dorian (MJT support)]

This is the Xpath you need. In my test it returned 27 values from "column 2".

Code: Select all

//Xpath to find 2nd cell on each row within table "crit_val"
Let>strXPATH=//table[@id='crit_val']//following-sibling::tr/td[2]

//Find the element
ChromeFindElements>session_id,xpath,strXPATH,strElementID

If you're experiencing file errors with files on google Drive, move them to a local drive (not OneDrive either). Of course that's if you've already checked the file path and know it's correct (must be the complete path and file name).

[JohnJ2717]

Это Xpath, который вам нужен. В моем тесте он вернул 27 значений из «столбца 2»

In my case, it returns <array> to the clipboard, and the command "get cell" does not work at all
Maybe I misunderstand how to paste it into the clipboard, or do something else wrong...
In addition, I still did not understand how to jump over a cell in excel if there is already a number there (just a number, and not just any content).

[Dorian (MJT support)]

That's because the returned data is in an array :

ChromeFindElements>session_id,strategy,value,elements_array
Finds matching elements and returns an array of element IDs

You can't just paste an array. Please forget about pasting for this particular project.

If you read the "Using Macro Scheduler 15’s Chrome Automation Functions" article I linked to in my first reply you'll see it shows how to extract data from a web page using ChromeGetElementData. You can use that in conjunction with the xpath I already provided.

If you need to learn how to use Macro Scheduler, then we are always happy to guide you. But we can't "learn it for you". I cannot emphasise enough how important it is to thoroughly read the articles we point you to, and to test out the examples until you fully understand the principles. Not doing so will just frustrate you, and lead to a great many "it doesn't work" scenarios. Particularly if you try to rush this and run before you can walk. Learn and test one thing at a time. Then gradually apply what you have learned to combine those into the script you need.

The articles and the manual are the definitive guide to learning Macro Scheduler. I cannot explain them any better than the manual - and the usage examples are enough to get you up and running in the vast majority of cases - but what I can do is help you understand what functions you need to be learning about and thoroughly understanding.

[JohnJ2717]

You can't just paste an array.

Oh,please forgive my carelessness (Besides,all i remember from programming is binary and hex)

Particularly if you try to rush this and run before you can walk.

I'm afraid that's exactly what I was trying to do

[JohnJ2717]

Well... the trial period is over, but I haven't completed my training yet. I don’t have money for a license, besides, the country is under sanctions, so I will gladly listen to any suggestions.