Translate english to german to do that we're going to take a quick look at the basics of using google translate so while i'm here i'm just going to quickly copy the url to the.
Clipboard and then we'll write the basic code to navigate to that in a fresh instance of chrome so i've already started by creating a new workbook saved it as a macro enable.
File and then in the visual basic editor i've inserted a new module if we're using google chrome as usual we'll use the selenium basic library so to set a reference to that we can enter.
The tools menu and choose references and then in the list look for selenium type library place a check in the box and then click ok.
And then we'll declare a variable which can hold a reference to an instance of a chrome driver so i'll say private cd as selenium.com then we'll create a subroutine which we.
We'll start the chrome driver and then we can get the url that i've just copied to the clipboard so cd.get and then paste in that url in a set of double quotes which i nearly forgot.
So let's just open and close the double quotes there and there's the very basic code we need to navigate to google translate one quick problem we'll need to solve.
Before we can get to the translating part is how to accept the cookies agreement that appears when we run the subroutine so i run this code because we're using chrome anonymously we get.
Presented with this form that asks us to consent to storing cookies and i have to click the i agree button to make that work now i want that to happen automatically.
There's a couple of ways we could get around that so one way would be to not use google chrome anonymously we could start chrome using a user profile if you're.
Interested in how to do that we do have
A video which explains how to start chrome using a user profile so i'd recommend having a quick watch of that video as an alternative solution.For this video i'm just going to write some code that's going to click the second of the two buttons that appears on the cookies consent form so what we're going to do is say cd dot find.
Elements by css and then we're going to search for buttons so we'll look for elements with the button tag name and then i'm going to refer to the.
Second button in the collection that's been returned and then apply the click method to that so if i run the submission again we'll see the consent message appears but i.
Click on the second button and then land on the translate page next we need to find out how to reference this element on the page so that we can enter some text to be.
Translated so i'm just going to maximize the window that i've opened up here and then right click on that element and choose to inspect it i think i'll need to do that again as well that's.
Right-clicking inspect so that in the elements panel we end up in the correct place so we can hopefully see there we've referenced something called a text area.
Element now i'm pretty sure there's only one of those elements on the page but just to make absolutely certain i'm going to copy the text area tag name to the.
Clipboard head back to the visual basic editor and then write a debug.print statement that's going to use the find elements by css method i'm going to try to find all.
The all the
Text area elements using the tag name that i've just copied and then i want to print out a count of those to check there's not more than one.If i run that subroutine again we'll go through the cookie consent message again and then hopefully we'll find that there is indeed only one of those items okay so having established that we can.
Alter this line now let's not try to find all the elements by css we'll just find the single text area element using find element by css and then i don't want to count those i.
Know there's only one of them i want to apply the send keys method to that element so this allows us to type some text into the box of course now we can just type.
In some literal text you may have noticed when we first run this in in my case the default translation language or output language is english so i want to type in.
Something that definitely isn't in english just to make sure this is actually working so in some double quotes i could i could write the phrase for thank you in a different language as.
I'm going to say asantesana and apologies for any mispronunciation of that so let's run that subreddit again and hopefully this time we'll see that we.
End up with some text typed in and it detects that that is indeed in swahili and it translates it for us as well now rather than typing in the text each time what we could also do is reference.
A cell in a worksheet so i'm just going to copy the phrase that i've typed in there and i'm going to paste it into cell a1 on this single worksheet that's in my workbook.
And then i'm going to replace this piece of literal text with a reference to range a1 and then we can reference either the value or the text property of that range.
So once again if we run the subroutine we'll find that it will read the content of the cell type that into the text box on the website and then show us the translation as well.
The next step is to work out how to extract the translated text so that we can send that back into excel to make use of it now we could take the approach of.
Attempting to work out how to identify this element and then extract the text from it or we could take a shortcut and just work out how to click on the copy translation button which is what i'm.
Going to do so let's just maximize the google translate window again i'm going to right click on the copy translation button and choose inspect again i might.
Need to just do that a second time just to get to the correct place i might have gone just a level two low here i think i'm just gonna scroll upwards in the elements list to.
Find the button element here so i know this this item is a button it looks like there are several other buttons on this page though as well so it's quite likely there's multiple.
Buttons here i don't want to have to work out which numbered element this copy translation button is so i'm going to try to find a button that's got some kind of unique property.
Or attribute and i think a fairly useful one to use a descriptive one at least is this property or attribute called area label which has the value of copy translation.
So i'm just going to double click on area label and then just extend my selection to get all of that copy translation and the open and close double quotes i'm just going to copy.
That to the clipboard for the moment then i'm going to head back to the visual basic editor and i'm going to say cd.find element by css and then in some round brackets and double quotes look at.
How to refer to a specific tagged element a specific type of element with a particular value for one of its attributes so i know i'm searching for a button.
And then in some square brackets i'm going to paste in the code i've just copied then i can close a double quotes at the end there.
And close the round brackets and then i can apply the click method to that now one small problem with this is that the nested double quotes here will break.
This line it makes the syntax invalid so the simplest solution here rather than using the double quotes for the attribute value copy translation we can exchange the double quotes with single.
Quotes now one other quick thing i'm going to add in just for a bit of safety you may have noticed when we executed this code the first time there was a bit of a.
Delay a bit of a pause between typing in the text into this box and the translation appearing here so i'm just going to introduce a short weight between entering the text and.
Then attempting to copy it so i'm going to say cd.wait i'll attempt to spell that correctly i'm going to wait for two seconds probably a little longer than i really need to but.
Just for safety so having copied this to the clipboard i'll then finally want to write it out somewhere paste it somewhere so i'm going to say range a2.
And then just paste the contents of the clipboard to that cell so we'll say paste special okay so having done all that let's run the subroutine again and we will.