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

Подписаться 1,3 тыс.

Просмотров 40 тыс.

50% 1

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.c...
Google Translate (m): translate.goog...
Translated URL: translate.goog...
==========================================
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

Опубликовано:

28 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 145

@xlstudiovba 2 месяца назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WJeP09WboDo.html

@RojamZane 2 года назад

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.

@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. 🤜🤛

@xlstudiovba 2 года назад

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 !!!

@linaseptiani2994 2 года назад

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

@mealimmunisation6752 Год назад

Thank you !

@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

@burakkayahan1730 2 года назад

Thanks Dude..

@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 ....

@aniruddhapundle2109 2 года назад

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

@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!"

@timmareddy2073 2 года назад

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

@xlstudiovba 2 года назад

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

@robinskaria9029 Год назад

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

@enigma533 2 года назад

How to do image translation? Without google

@xlstudiovba 2 года назад

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

@inderjitwassi1420 2 года назад

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

@bijnor420 2 года назад

showing error " Value"

@xlstudiovba 2 года назад

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

@vizznan 2 года назад

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

@antonio.medina69 2 года назад

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

@xlstudiovba 2 года назад

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

@manik1633 2 года назад

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??

@marcus-un6qt 2 года назад

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 2 года назад

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

@abdurrahim7977 2 года назад

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

@maxadventure5858 9 месяцев назад

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?

@bettinae5 2 года назад

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 2 года назад

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 2 года назад

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?

@ararattisri1916 2 года назад

No me aparece la opcion de: As HTMLDocument

@anupnair7036 2 года назад

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

@LorenZo-ts3eg 2 года назад

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

@appleanne5204 2 года назад

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 2 года назад

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

@deepubs6986 2 года назад

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

@xlstudiovba 2 года назад

Did you written the code ?

@mohammedaltair3695 2 года назад

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

@facturaone4588 2 года назад

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

@suganthiperumal2662 2 года назад

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

@yousefmohammed6325 2 года назад

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

@xlstudiovba 2 года назад

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

@yousefmohammed6325 2 года назад

@@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 2 года назад

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 2 года назад

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

@xlstudiovba 2 года назад

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

@csababojti2880 Месяц назад

unfurtunatelly ObjClass is Nothing.

@rahulpansare3579 2 года назад

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

@tamirsteinberg 2 года назад

Try Tools -> References -> Microsoft HTML Object Library

@gustavocarrera7028 2 года назад

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 2 года назад

Make sure not on company VPN

@gustavocarrera7028 2 года назад

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

@ismaelkourouma5558 2 года назад

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 2 года назад

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

@chiptekelectronics 2 года назад

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

@danielrivaldo7756 2 года назад

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 2 года назад

Check the language code in language support list

@JantjeP1 2 года назад

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 2 года назад

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 :-)

@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?

@SohaibKhan-tg8rx 2 года назад

Thank you so much. It was super helpful

@SG10212 2 года назад

Thank you for the above video.

@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

@darrellskilton2046 2 года назад

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 2 года назад

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

@ashishpahal3421 2 года назад

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

@darshanfr4207 Год назад

HTMLDocument is not working

@anusha88ch 4 месяца назад

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

@xlstudiovba 4 месяца назад

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 ..

@hahhah2754 Год назад

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

@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

@bijnor420 2 года назад

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

@masterdavid23 2 года назад

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 2 года назад

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

@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.

@mahmoudbadr9335 2 года назад

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

@xlstudiovba 2 года назад

Check out my part 2 video , it has that

@mahmoudbadr9335 2 года назад

@@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 2 года назад

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

@mahmoudbadr9335 2 года назад

@@xlstudiovba ok, thank you so much

@shridharbadiger9293 10 месяцев назад

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

@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

@ashishpahal3421 2 года назад

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 2 года назад

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

@ashishpahal3421 2 года назад

@@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 2 года назад

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

@JohnDoe-k3r 6 месяцев назад

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

@xlstudiovba 4 месяца назад

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

@relationship_matters 2 года назад

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 2 года назад

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 2 года назад

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

@relationship_matters 2 года назад

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

@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 ...

@naaaassriii93 2 года назад

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 :)

@darshanfr4207 Год назад

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

@karlwork1 2 года назад

Really good video and thanks for the walk through

@amgadshatat9063 Год назад

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

@abdulnayeem7862 2 года назад

🤝🤝

@samruomsoeun4788 Год назад

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

@stephensibert7719 3 месяца назад

Thank you very much!!!

@suconnottu64 9 месяцев назад

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

@REPATHATHYALA Год назад

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

@abdulnayeem7862 2 года назад

Nice

@knaptime363 Год назад

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

@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

@hanihassan7406 2 года назад

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

@xlstudiovba 2 года назад

did you written the code in VBA Editor ?

@hanihassan7406 2 года назад

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

@xlstudiovba 2 года назад

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

@tillnienberg6367 2 года назад

@@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

@ashwinitiwari7634 2 года назад

Can you provide code to avoid typing

@xlstudiovba 2 года назад

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

@mauriciocampos7373 2 года назад

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

@xlstudiovba 2 года назад

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 : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FDaOuS1E_7U.html

@HawkFest 2 года назад

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 2 года назад

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

@arneldeguito199 2 года назад

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

@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

@durgeshshirke2822 2 года назад

Mine is not translating and getting same language as result

@xlstudiovba 2 года назад

Check the Target language,

@durgeshshirke2822 2 года назад

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

@durgeshshirke2822 2 года назад

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

@xlstudiovba 2 года назад

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

@durgeshshirke2822 2 года назад

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

@hayderxyz0 7 месяцев назад

thank you

@dexitk Год назад

Nice Thanks :)

@AlexanderAddams 2 года назад

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

@xlstudiovba 2 года назад

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

@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!