Excel VBA - Google Translate Function in Excel - PART 1 | XLStudio |
Vložit
- čas přidán 30. 12. 2021
- Must Watch !!!
If you would like to translate any text into different language then this video is for you.
Learn how to create Google Translate function in excel using VBA User Define Function (UDF), this video will help you understand how google translate function works and how to create similar type of other UDF functions in Excel.
Part 2 Video Link: • Excel VBA - Google Tra...
Translate using Power Query Solution Video Link: • Google Translate Using...
Must Watch video to translate english to other languages or other languages to english
Language supports by google: cloud.google.com/translate/do...
Google Translate (m): translate.google.com/m
Translated URL: translate.google.com/m?sl=en&...
==========================================
If you love the content please do like the video and Subscribe to my channel and hit the bell icon to get notified with my upcoming videos.
===========================================
#XLStudio #GoogleTranslate #Excel
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: czcams.com/video/WJeP09WboDo/video.html
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.
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. 🤜🤛
Hi, Thanks for creating. Really liked your approach. I followed and translation worked.
Fantástico !
Só você disponibilizou estas informações. Todos outros usam somente o caminho fácil com o google sheets.
Muitíssimo obrigado !!
Really good video and thanks for the walk through
Thank you for the above video.
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!
Amazing stuff, this is exactly what I was looking for!! Thank you so much.
Thank you so much. It was super helpful
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
thanks this is actually i needed right now... So helpful...Thanks a lot....
Thank-you very much, your explanation is fantastic and the content is very good...
Thank you !
Thank you very much!!!
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 !!!
Thanks Dude..
Hello I am getting #value error, I am connected to mobile hotspot and having windows 11 please help in this regard...
Nice
Office 2019, Windows 10 Pro.
Compile Error:
User-defined type not defined.
Error at number 4th "HTMLDoc As HTMLDocument"
Nice Thanks :)
🤝🤝
thank you
Great video :) .... i make a variation.... -> add URLEncode on part of text
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
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. :)
Are you connected on VPN? Or office network? If yes , then just connect to ur home network
@@xlstudiovba Hi, i have selected Home network and i have disable the VPN.
But still the error is coming.
what code should i use for windows 7 instead of windows 10
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
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
How can I copy your vba code and pest in my pc?
No me aparece la opcion de: As HTMLDocument
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??
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.
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 :)
You have to enable HTML object library by going into Tools - Reference, look for HTML object library and enable
I am getting a blank cell while executing. Can anyone help?
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 :)
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.
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
have you found the solution for this ? because same happened to me I tried changing the network but still ):
@@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?
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?
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
Perfect lesson, Appreciate your work, Nooby Question, Is there a double comma missing on the first step?
Thank you so much...can you please ping me the time stamp .... In first step for creating url we have any comma's
@@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
Did u added Microsoft HTML Object Library ?? If not Click on Tools menu , then next click on Reference and find this library and enable
@@xlstudiovba I just did, Its works Perfectly, Thank you very much
@@yousefmohammed6325 Glad it's work for you..... Keep Sharing and Learning.....
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?"
are your program works now ?
mine cant translate too, thai ---> english the translator break
@@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
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
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.
I didn't tested in Mac ....
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!"
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
Make sure you not on company network, email me will share the file
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
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?
Appreciate your Work , However will you help me as I am not getting the result when i enter the function and hit enter
Did you written the code ?
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.
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 :-)
Hi, Is it possible to write macros to automatically detect the language & convert it into English or any other language
Yes it's possible, Part 2 is going to come translation on that I will explain how we can detect the language automatically
zh Chinese does not seem to work. Any help would be appreciated
It's Return 0 value. How will we resolve this. Do the needful sir
Why is the formula returning unicode gibberish for chinese, turkish and japanese?
Sir please give me solution of hTMLDocument... It is giving error user-defined type not defined
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
Check the language code in language support list
In step number 4, I have a Debug problem with line number 3 which is (Dimb HTMLDoc...
I am facing an error in HTMLDocument, there is no document showing after html
Try Tools -> References -> Microsoft HTML Object Library
Hello friend,
During the last video session for Compile, there was a BUG, namely:
Dim HTMLDoc As HTMLDocument
Can you help me
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
Make sure you not on company VPN, try with home or mobile internet
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 : czcams.com/video/FDaOuS1E_7U/video.html
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...
Thank you so much... Looking for better gadget for recording
Is it possible for us to see what is the detected language. Function like googlesheet's =DetectLanguage()?
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.
Can u please send me the screenshot of the code..
Trying saving and re-opening the workbook, and also notice if there is any errors such as macros not enabled, if yes then enable.
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
Make sure not on company VPN
@@xlstudiovba we do not have VPN. Me I send you screenshot of my code for double check please?
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?
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
@@xlstudiovba can you share your excel file for reference
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
Hi, error it appears #VALUE!, Please add this .xlsm to your description for us to download, i can't make it work
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
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
same thing is also seen in google sheets ...
Hello @XL studio
I did all the steps and errors was found in the code, yet the function =googletranslate is not there.
did you written the code in VBA Editor ?
@@xlstudiovba sorry, I mean no errors were found.
The I have written it in the VBA Editor.
Then Google translate function will reflect only in that file or workbook where you have written the code
@@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
Can we do this with office script code ? for using in browser
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
How to do image translation? Without google
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
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?
compile error: User-defined type not defined
I am getting #Value in excel output.Could you please help me on this on urgent
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 ..
HTMLDocument is not working
THank you for the vedio but it does not work for translating from arabic to other kanguages!
Check out my part 2 video , it has that
@@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?
No, you have to be connected to internet as it's doing web call
@@xlstudiovba ok, thank you so much
And what if the language is not listed in Google, but I have a spreadsheet of the language?
If language code doesn't support then result will be empty
Can you provide code to avoid typing
Sure , can you please share ur email ID... I will recommend you to type code....
Can you share the code?, I'm getting confused with the commas, quotes and ampersands as my screen is too small to see, Thanks
Mine is not translating and getting same language as result
Check the Target language,
@@xlstudiovba check with fr, hi, ar language still the result is the same
"User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; Windows NT 10.0))" Can this cause issue
which OS you have ?, if its not resulting any info then you might entered code wrongly somewhere
@@xlstudiovba Win 10 Professional.....it is resulting but the same language and no translation
showing error "
Value"
What type of error ur getting and on which line of code ?
@@xlstudiovba when trying to translate getting #Value instead of the output. I am using office 365
@@xlstudiovba same here... I get a #¡VALUE! error... I'm using Win 11 and Microsoft 365, can you help me? Need this ASAP
Check the connection, you should not be on VPN or company network
unfurtunatelly ObjClass is Nothing.
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.
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?
Please share your mail id i will send the formula
I have created formula but it doesn't work
not able to get "HTMLDoc.getElementsByClassName" kindly advice