Excel VBA - Google Translate Function in Excel - PART 1 | XLStudio |

[@xlstudiovba]

Hello Friends, Hope you are doing well !! Here is the another Google translate solution and this one is not with Excel VBA.. Its with using Power Query Custom Function. Please watch this video if you are looking for without VBA solution. Video Link: kzbin.info/www/bejne/jXvIgWNvjMeietE

[@charlielogan5760]

Fantastic! Thanks for doing this, very helpful.

[@suconnottu64]

Thank to xl studio one of the much the indian guy which esplain prefectly to entire world computer' s science. This program run with windows11 too with the same code

[@xlstudiovba]

Hi Guys !!! Hope you guys doing well. Part 2 is going to come on Translation to translate any language into any other language.. this video translate only English to any other language but part 2 video will do from any to any other language. Do stay tuned !!!

[@user-sh4tl9ji7q]

I made it work!!! I just followed everything you did. I added WorksheetFunction.EncodeURL on the SourceText so it would work for other Language to English and vice versa. Thank you thank you!

[@RojamZane]

THIS IS AWSOME!! Thank you for taking the time create this. It is exactly what I need for some on-going translation activity. I hunted high & low on the web... then, your video finally emerged. I couldn't believe my luck. So grateful. All very clearly explained.

[@xlstudiovba]

Hi Guys !! Hope you guys doing well !! Here is the Part 2 Video link as i promise, this part 2 video will show you how we can translate from any language to any language and also how we can automatically detect the language code of source text. Part 2 Video Link : kzbin.info/www/bejne/fHXEgKiJZqqSbbc

[@HawkFest]

Thank you man ! Super tutorial, clear and concise. I need to develop some processes with Excel, which requires me to refresh my memory (especially with macros, VB etc. since it's been a while, + some things have changed), and this is a very good and practical "case study", well-structured, and most importantly, without the usual personal and irrelevant chitchat-babbling some narcissit/egomaniac YT stooge throw at us (as those seem to actually nurture the goal of becoming "popular" and use the platform for that matter, rather than focus on creating quality content to truly help people in the best way). Sometimes it's hard to understand because of your accent, of which I'm personally not used to, but it doesn't matter so much when we can decipher those words we fumble onto by "rewinding" the cilp and listen at slower rate. Thumbs up and subscribe...

[@xlstudiovba]

Thank you so much... Looking for better gadget for recording

[@arneldeguito199]

Is it possible for us to see what is the detected language. Function like googlesheet's =DetectLanguage()?

[@LorenZo-ts3eg]

Hi, Thanks for creating. Really liked your approach. I followed and translation worked.

[@AlperSaritepe]

Thats it, perfect! Even though I don't have any coding background, it was fun to listen to and I got the result I wanted. The typos I made bothered me a lot, but it was worth it. That channel have to got more support. 🤜🤛

[@yousefmohammed6325]

Perfect lesson, Appreciate your work, Nooby Question, Is there a double comma missing on the first step?

[@xlstudiovba]

Thank you so much...can you please ping me the time stamp .... In first step for creating url we have any comma's

[@yousefmohammed6325]

@@xlstudiovba Oh, i was wrong troubleshooting, Am getting an Error msg (User-defined type not defined), and the first line when creating the function is all yellow highlighted

[@xlstudiovba]

Did u added Microsoft HTML Object Library ?? If not Click on Tools menu , then next click on Reference and find this library and enable

[@yousefmohammed6325]

@@xlstudiovba I just did, Its works Perfectly, Thank you very much

[@xlstudiovba]

@@yousefmohammed6325 Glad it's work for you..... Keep Sharing and Learning.....

[@bettinae5]

Hi thank you for this structured way explaining and writing the code this is a awsome idea and the code looks good without any debuging error. The issue I have is on EXCEL it returns an #VALUE!. Any hint what to do? Thank you so much.

[@xlstudiovba]

Thank you so much, on which network ur trying? If it's on company network then firewall will not allow you to call HTTP request, trying checking with ur home or mobile network

[@dalya5125]

have you found the solution for this ? because same happened to me I tried changing the network but still ):

[@saadspawn]

@@xlstudiovba I am getting the same # value as I am working on my work laptop and office wifi. However, I just tried it using my mobile hotspot and connected it to my work laptop but still same #value issue. Please help. Does it have anything to do with this function? XMLHTTP.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.4;Windows NT 10.0))" as I am using Windows 10 and Google Chrome and not Mozilla?

[@relationship_matters]

Awesome explanation, Thanks dear, I. Working on a project where i need to translate english name in to the hindi. And today i will try this at my level, hope as you explain easily as it will work. Thanks

[@dushyantbahe4767]

Hi, I tried all the procedure you explained (even copy the code as typed in VBA), everything was fine until i use the function to test, but i also Found the Error "#value!". I have checked entire code, compared with your code, but it seems ditto copied, but i dont know why this error is coming. please solve my doubt. :)

[@xlstudiovba]

Are you connected on VPN? Or office network? If yes , then just connect to ur home network

[@relationship_matters]

@@xlstudiovba Hi, i have selected Home network and i have disable the VPN. But still the error is coming.

[@REPATHATHYALA]

Thank-you very much, your explanation is fantastic and the content is very good...

[@mohammedaltair3695]

thanks this is actually i needed right now... So helpful...Thanks a lot....

[@mahgoubshora]

Thank you for this wonderful lesson and way of explanation. I have a question please, I followed the steps exactly and it works well when I translate from English to any other language but when I change the source language to any other language than English I get strange characters as a result My operating system is windows 10 pro which I changed instead of window NT in your VBA code How can I fix this issue please? Thanx

[@deepubs6986]

Appreciate your Work , However will you help me as I am not getting the result when i enter the function and hit enter

[@xlstudiovba]

Did you written the code ?

[@knaptime363]

Amazing stuff, this is exactly what I was looking for!! Thank you so much.

[@chiptekelectronics]

Fantástico ! Só você disponibilizou estas informações. Todos outros usam somente o caminho fácil com o google sheets. Muitíssimo obrigado !!

[@SG10212]

Thank you for the above video.

[@linaseptiani2994]

Office 2019, Windows 10 Pro. Compile Error: User-defined type not defined. Error at number 4th "HTMLDoc As HTMLDocument"

[@aniruddhapundle2109]

Hello I am getting #value error, I am connected to mobile hotspot and having windows 11 please help in this regard...

[@appleanne5204]

Hi! Thank you for the tutorial, very helpful. I was just wondering if I encounter a "Compile error: User-defined type not defined" _ and pointing to this part of the VBA 'Microsoft HTML Object Library Dim HTMLDoc As HTMLDocument. What can I do next to fix it? Appreciate your response :)

[@xlstudiovba]

You have to enable HTML object library by going into Tools - Reference, look for HTML object library and enable

[@mojtababozorg]

Thanks for making such a valuable video. What is I am using Excel in MacBook Air M1? What should I put instead of "XMLHTTP.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; Windows NT 10.0))" in the 2nd step? please answer here as others might be having the same problem.

[@xlstudiovba]

I didn't tested in Mac ....

[@abdurrahim7977]

How can I copy your vba code and pest in my pc?

[@karlwork1]

Really good video and thanks for the walk through

[@manik1633]

Hi Bro, ran your code, but the output is empty. Can you help me on this its urgent? and also I dont have Mozilla so like will I need to change where you wrote Mozilla 5.0??

[@masterdavid23]

Thanks for this video :D My code is not running and I think it has to do with the header since I'm running on Windows 11. Do you know how I need to adjust the header in that case?

[@xlstudiovba]

It will work on windows 11 also with same header... I tested on windows 11 also.. share the screenshot of code and also let me at which line ur getting error

[@facturaone4588]

Great video :) .... i make a variation.... -> add URLEncode on part of text

[@bijnor420]

what code should i use for windows 7 instead of windows 10

[@tiagohuve]

goodnight! I cannot translate from Portuguese to English, from English to Portuguese it works perfectly, but not the other way around. I use Excel 365 on Windows and I didn't find the EncodeUrl function. if you can help thanks.

[@SohaibKhan-tg8rx]

Thank you so much. It was super helpful

[@danielrivaldo7756]

this is very great but I have concern, why i cant translate thai --> english, the trasnlator is break, i think for unique language like thai doesnt work

[@xlstudiovba]

Check the language code in language support list

[@mulrajgala-kf6uf]

This is good . However, I have observed for Hinglish i.e. hind words written using English aplhabets the translation is better on the google translate page directly then in excel . Any way around same. Please do also email this file to test

[@xlstudiovba]

On page when you type Hindi words then what is the source language page selected automatically that source language code you have enter in Excel file.. like mostly when you type Hinglish then source language will be Hindi... Try out and let me know

[@mulrajgala-kf6uf]

same thing is also seen in google sheets ...

[@mealimmunisation6752]

Thank you !

[@JantjeP1]

Thanks. Check the code very well, if it is not working. I missed an "&" In some countries the separator is not the comma but the semicolon ";" I have a tip : put "en" in cell "A1" "fr" in cell "B1" your English text in "A2", the function in "B2" like =GoogleTranslate($A2,$A$1,B$1) or with semicolon =GoogleTranslate($A2;$A$1;B$1) and copy down or to the right if you have more languages.

[@JantjeP1]

If it works you can insert a new row on top and put the language names there. I put a list of 109 languages there so I can see which language I need. It looks very impressive to see Hello world in 109 languages. It take some calculation time so copy the languages you need to a separate workbook :-)

[@ashishpahal3421]

This is great stuff! Worked for me, thankyou! I tired English to Chinese as well and working just fine. However not working other way around ( Chinese to English ) Could you please help as I'm getting strange symbols as output in excel cell :p For trial I used - "how are you?"

[@danielrivaldo7756]

are your program works now ? mine cant translate too, thai ---> english the translator break

[@ashishpahal3421]

@@danielrivaldo7756 not yet, it is not working for some languages .. tried on Korean as well... didn't work.. from English to 'intended language' it works.. but not working vice-versa for some like - Chinese, Korean etc

[@xlstudiovba]

Yes where will have unicode language like Hindi, Urdu, Korean etc for those it will not work as this type of value will not store exactly same in variable in VBA code however Spanish, french language will save in variable

[@timmareddy2073]

Hi, Is it possible to write macros to automatically detect the language & convert it into English or any other language

[@xlstudiovba]

Yes it's possible, Part 2 is going to come translation on that I will explain how we can detect the language automatically

[@stephensibert7719]

Thank you very much!!!

[@burakkayahan1730]

Thanks Dude..

[@abdulnayeem7862]

Nice

[@marcus-un6qt]

Can you please post a link to a already-scripted excel file? because I got #value!, i wrote exactly the same as yours. Although I saved, but when i come back, the function has gone

[@xlstudiovba]

Make sure you not on company network, email me will share the file

[@ararattisri1916]

No me aparece la opcion de: As HTMLDocument

[@jakubskarka5461]

Hello, I have just one issue. I don't get help when entering HTML tags (eg HTMLDocument). Even if I write it correctly, it throws me as an error after checking (I assume as if it doesn't know it) PS" I have the HTML object library visible (in References - VBAProject) Any Idea ? thanks sorry im really beginner

[@JohnDoe-k3r]

Can we do this with office script code ? for using in browser

[@xlstudiovba]

I think no, as result of that url is not json format, it's UI.. but don't worry part 3 going to come, without writing VBA code I will show you how we can translate

[@naaaassriii93]

Hi, how to arrange the line : XMLHTTP.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; Windows NT 10.0))" when we have macOS? Thanks a lot :)

[@swapnilhedau4560]

Hi I was tried same thing in my PC but its not working Can you please help to find what is issue? its showing me"#VALUE!"

[@robinskaria9029]

Why is the formula returning unicode gibberish for chinese, turkish and japanese?

[@amgadshatat9063]

In step number 4, I have a Debug problem with line number 3 which is (Dimb HTMLDoc...

[@anupnair7036]

I am getting a blank cell while executing. Can anyone help?

[@hahhah2754]

Hello friend, During the last video session for Compile, there was a BUG, namely: Dim HTMLDoc As HTMLDocument Can you help me

[@hayderxyz0]

thank you

[@darshanfr4207]

Sir please give me solution of hTMLDocument... It is giving error user-defined type not defined

[@ismaelkourouma5558]

Hello, thanks. I have done everything but getting the error like this in the formula: #VALUE! Everything is ok and no error with the debug. I cannot translate from Frech to English. Thank you

[@xlstudiovba]

Make sure you not on company VPN, try with home or mobile internet

[@darrellskilton2046]

I am getting no errors in my code but I am using office 365 and my web browser is Edge, the workbook is getting a #Value! error in the spreadsheet, How can I fix this? Appreciate your assistance.

[@xlstudiovba]

Can u please send me the screenshot of the code..

[@ashishpahal3421]

Trying saving and re-opening the workbook, and also notice if there is any errors such as macros not enabled, if yes then enable.

[@maxadventure5858]

Hi, I copied your formula into Excel which I use "Polish language version", Google Chrome browser and Windows 10Pro, the function is visible but does not work. After entering it, a #ARG comment appears! Do you have any suggestions why it's not working?

[@enigma533]

How to do image translation? Without google

[@xlstudiovba]

We can do , but we have to use Google vision API , or we can use tessaract , but best is Google vision API, use python code for easy translation

[@gustavocarrera7028]

I am getting no errors in my code but I am using office 365 and my web browser is Mozilla V 104.0.2 (64 - bits), installed on Windows 10 Pro, the workbook is getting a #Value! error in the spreadsheet, How can I fix this? Thanks for your assistance

[@xlstudiovba]

Make sure not on company VPN

[@gustavocarrera7028]

@@xlstudiovba we do not have VPN. Me I send you screenshot of my code for double check please?

[@dexitk]

Nice Thanks :)

[@rahulpansare3579]

I am facing an error in HTMLDocument, there is no document showing after html

[@tamirsteinberg]

Try Tools -> References -> Microsoft HTML Object Library

[@hanihassan7406]

Hello @XL studio I did all the steps and errors was found in the code, yet the function =googletranslate is not there.

[@xlstudiovba]

did you written the code in VBA Editor ?

[@hanihassan7406]

@@xlstudiovba sorry, I mean no errors were found. The I have written it in the VBA Editor.

[@xlstudiovba]

Then Google translate function will reflect only in that file or workbook where you have written the code

[@tillnienberg6367]

@@xlstudiovba Hello, I have the same problem and it is not showing up the function =GoogleTranslate in Excel, I also tried the version of Daniah Kumar and I would love to use the function cause I have lots of excel files to translate. Thanks

[@abdulnayeem7862]

🤝🤝

[@tanszehui19]

Hi, may I ask, if I using Chrome Version 113.0.5672.127 (Official Build) (64-bit) & Window 11 Home then after "User-Agent" how do I type? Thanks

[@inderjitwassi1420]

zh Chinese does not seem to work. Any help would be appreciated

[@suganthiperumal2662]

It's Return 0 value. How will we resolve this. Do the needful sir

[@samruomsoeun4788]

Hi, error it appears #VALUE!, Please add this .xlsm to your description for us to download, i can't make it work

[@mahmoudbadr9335]

THank you for the vedio but it does not work for translating from arabic to other kanguages!

[@xlstudiovba]

Check out my part 2 video , it has that

[@mahmoudbadr9335]

@@xlstudiovba perfect, it works well now :) one more question please, is there anyway to make it work if i am not connected to the internet?

[@xlstudiovba]

No, you have to be connected to internet as it's doing web call

[@mahmoudbadr9335]

@@xlstudiovba ok, thank you so much

[@raphaelmuniz4862]

I have no compilation problem with this code. Even though it appears " #VALUE " in the excel cell when I test the function. I tried separating the variables with " , " and " ; " and none of them are working. Do you know what could it be?

[@xlstudiovba]

It's working fine for me in windows 10 and 11.. not sure why it's not working for you guys... I tested in others system too

[@swapnilhedau9534]

@@xlstudiovba can you share your excel file for reference

[@bazdmegricsi]

So to the ones who receive #value! error. I uploaded the .xlsm file to my google drive, opened it there and the function works! The no company network, no VPN didn't help me. This is a quick fix, I am sure there is also one that you can use without uploading, but I had not time to explore any further.

[@wimbballm]

This worked for me too, but I'd like to be able to use the function on my company network. Do you have any idea how to enable this?

[@gihankottapola]

When it debug there is an error in 4thg step highlighted as underline phase in below. What is the reason I cannot proceed? '4th-Read the translated from HTML file using HTML web elements 'Microsoft HTML Object Libarary Set HTMLDoc = objHTML Dim ObjectClass As Object Set Objclass = HTMLDoc.getElementsByClassName("result-container")(0) If Not Objclass Is Nothing Then Google translate = Objclass.innertext End If 'relasing the memory setobjclass = Nothing Set objHTML = Nothing Set XMLHTTP = Nothing

[@sebastianmse]

It give me an error in 4th: '4th- Read the translated from HTML File using HTML web elements 'Microsoft HTML Object Library Dim HTMLDoc As HTMLDocument Set HTMLDoc = ObjHTML" the 'dim' why it could be?

[@sebastianmse]

compile error: User-defined type not defined

[@ashwinitiwari7634]

Can you provide code to avoid typing

[@xlstudiovba]

Sure , can you please share ur email ID... I will recommend you to type code....

[@mauriciocampos7373]

Can you share the code?, I'm getting confused with the commas, quotes and ampersands as my screen is too small to see, Thanks

[@darshanfr4207]

HTMLDocument is not working

[@durgeshshirke2822]

Mine is not translating and getting same language as result

[@xlstudiovba]

Check the Target language,

[@durgeshshirke2822]

@@xlstudiovba check with fr, hi, ar language still the result is the same

[@durgeshshirke2822]

"User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; Windows NT 10.0))" Can this cause issue

[@xlstudiovba]

which OS you have ?, if its not resulting any info then you might entered code wrongly somewhere

[@durgeshshirke2822]

@@xlstudiovba Win 10 Professional.....it is resulting but the same language and no translation

[@bijnor420]

showing error " Value"

[@xlstudiovba]

What type of error ur getting and on which line of code ?

[@vizznan]

@@xlstudiovba when trying to translate getting #Value instead of the output. I am using office 365

[@antonio.medina69]

@@xlstudiovba same here... I get a #¡VALUE! error... I'm using Win 11 and Microsoft 365, can you help me? Need this ASAP

[@xlstudiovba]

Check the connection, you should not be on VPN or company network

[@AlexanderAddams]

And what if the language is not listed in Google, but I have a spreadsheet of the language?

[@xlstudiovba]

If language code doesn't support then result will be empty

[@csababojti2880]

unfurtunatelly ObjClass is Nothing.

[@shridharbadiger9293]

Please share your mail id i will send the formula I have created formula but it doesn't work

[@rajukaduskar7017]

not able to get "HTMLDoc.getElementsByClassName" kindly advice

[@anusha88ch]

I am getting #Value in excel output.Could you please help me on this on urgent

[@xlstudiovba]

Hi Anusha, I have to review your code... But don't worry part 3 going to come .. I am going to explain how we can translate without writing VBA code ..