Upload/Export Data To Google Sheets Excel VBA Macro
HTML-код
- Опубликовано: 22 авг 2024
- Excel macro/VBA code available in this post: excelmacroclas...
In this video we see how to upload or export data from Excel to Google Sheets using VBA macros. The macro sends a HTTP request to POST the data through a Google Form into the Google spreadsheet. For that reason, we need to add the Microsoft XML 6.0 library to the VBA project. Another thing we need to do is to link the recipient Google spreadsheet to a Google Form, which needs to have as many fields as data columns to be exported from Excel. Additional details about the Google Form response URL can be found in this other article: excelmacrofun....
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclas...
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun....)
Excel Macro Business (excelmacrobusi...)
Excel Macro Sports (excelmacrospor...)
That video made my day!!! Thanks a LOT!! :)
Tks 😇😇
God bless u
Got it to work 😅
Can you please also do a video on how to do it with the google API?
The forms draw back is that if there are a few hundred rows it’s much faster to copy paste
Sure, I will add it to my list along with many other things I want to show about the interaction with Google Sheets.
Not sure why, but I had to use MSXML2.ServerXMLHTTP60 instead of MSXML2.ServerXMLHTTP... Is there a signifigant reason for this? I am on Office 365, but writing this to be used on Office 2019 (Both 32bit and 64bit flavors...)
It's the same, but it seems Office365 , and maybe also 2019 have ServerXMLHTTP60, and I was probably using Excel 2013 when I made the video. We should simply use the latest version of ServerXMLHTTP. I believe I have seen and used also another ServerXMLHTTP..6.0 and the older version ServerXMLHTTP.3.0 a long time ago.
what is IFQ in URL ??? doing? what does it do ?
ITs constants or seperate for us ?
ifq is just a parameter of the google url that indicates the following parameters belong to the various entries in the google form. You do not need to change it or do anything with it, just keep it in the main url that concatenates with each of the entries in the form as explained here: excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html
I tried a lot based on the guidance you gave, but it's not fetching the data from my Excel to Google spreadsheet. I tried using your example still it's not transferring my data. Even the time stamp is not entered.
Guide me
Find step-by-step instructions in this article:
excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html
I need to append data to the Google Sheet that already has many lines of data. This code inserts the new data from Excel on line 2. How would I modify it to insert the new data after the last existing line?
The macro uploads via Google Forms, and therefore, it adds the data from row 2 to skip the headers and/or after previous responses. This is useful when you start a process to upload data from the beginning or when you just upload once per sheet. To update existing data, you need to implement OAuth2.0 to use Google Sheets API or create your own Google API to do that. I have covered an example of a custom Google API to get restricted data from Google Sheets in this other video: ruclips.net/video/VmlSN-C2GGo/видео.htmlsi=UqCAunCDrwXFVGGK
Sir, if each time new data line come, automatic loading to google form, how it declare in VBA code . Thank you
Yes, there is a new line or row with data in Google sheets for each response. If you are testing and want to start all over, you need to manually clear the responses in the google form. It is not possible to do that with Excel VBA, you would need a Google script instead.
kindly tell me if want to delete all data from google form using vba then possible or not?
Difficult with Excel VBA only, it probably needs some sort of authentication. But possible using Excel VBA in combination with Google Apps Script, just sending a HTTP request similar to the one shown in this video to a google script that clears the form.
I have duplicated your code and when I try to execute the code runs but data is not being posted. In With block I added "Debug.Print .Status" and "Debug.Print .StatusText" I am getting 401 Unauthorized. Should this method still work or has Google made a change?
The method is still working well from my end. I also noticed some data uploaded to my "test" Google spreadsheet as of yesterday 2-May-2024, so it's also working for other users. Give it another try by creating your own Google form + spreadsheet and check carefully the instructions in the video or posts below:
excelmacroclass.blogspot.com/2022/09/upload-data-to-google-sheets-excel-vba.html
excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html
@@ExcelMacroMania so I tested this outside of my company environment on my personal account/ personal laptop and it works fine. When I try using my Google Account on my company laptop I get the 401 Unauthorized. Perhaps they have found a way to block this and impede automation and productivity....
I get this error:
Compile error:
User-defined type not defined
How to fix?
You've probably mistyped some variable or object. Please check and use the correct VBA code here: excelmacroclass.blogspot.com/2022/09/upload-data-to-google-sheets-excel-vba.html
is there a maximum number of "fields"? i have 2 rows and once i get to field 25 its fine. but past that it only sends 1 row. any ideas?
There is certainly some limit, but I don't think is as low as 25. You will probably have to check the settings of your google form and may need to extend the limit. I read somewhere there can be forms with over a hundred fields.
@@ExcelMacroMania hey buddy, Google might have some cage for hackers, so there might be a Time Catch, so if we add a Delay like in real world submitting a form takes time typing it... so if we add delay, will it do?
bcoz real world datasets are far more than 5000 rows !
Also Guide us how to sync i.e update values from excel to google sheets .
i.e already saved data in google sheet, but want to change the same row in google sheet.
May be creating a macro, select row 1 to find new data set row.
-1 Select All. Delete old data set.
might this work as Updating google sheets with same data ?
@@simplydoing9596 Hello, I do not think there is any time limit within google forms, I recall having forms open for quite some time while testing and did not have any problem. But there might be some mechanisms I am not aware of.
To fully sync Excel with Google sheets you would rather need to implement Google sheets API. Check this video to see how to import with sheets API and other methods: ruclips.net/video/lqFoVeNpA-4/видео.htmlsi=8iWDjlQuSzzBSJm-
Then you would identify the fields you want to update or delete, etc. It is more challenging to do that with sheets API though. A good alternative would be creating your own google API. That's also covered at the end of that video and in some other video about importing restricted google sheets.
how to remove all data in members form?
You need to do it manually from within Google Forms (under Responses) while logged in your Google account. Automating that is complicated and requires implementing Google OAuth in Excel.
Can you do this with a smartsheet web form?
Probably yes, because you can send a HTTP request to any web resource. But I do not know that technology (smartsheet), so you will need to do some research on your own.
please, make a video vba excel to create file i/o to a folder in google drive. thk
If I understand correctly, you are looking for a macro to save file in google drive from Excel, right? Not sure why you would use Excel for that, but it could be interesting to have a macro that saves the current Excel workbook in google drive after making some changes... I will look into that and add it to my list.
No Option Explicit? 😞
Sure, feel free to use Option Explicit, specially for longer macros or big VBA projects with lots of variables. I do not use it in my videos as macros are short with few or no variables, and do not want to confuse the audience. I talk about Option Explicit in tutorial for beginners here: ruclips.net/video/YMjbuA2g8AQ/видео.htmlsi=BhbCaft0Vqnzdk0D
i got error on ServerXMLHTTP
first goto tools references and hit check next to MSXMLHTTP as reference
follow video closely !
How to delete
You cannot delete using this approach. This is supposed to be used just to feed data. You can initialize your google sheet and clear the data by deleting all the responses in the google form. If you want to be able to add, update, delete, etc, you need a different solution altogether.
does not work the way you did it
Where's the issue then? You need to be careful when getting your Google Form URL and entry values for each form field. Here's explained step-by-step how to build the URL and send the HTTP POST request to upload the data: excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html