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

@ExcelMacroMania

Жыл бұрын

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

@ExcelMacroMania

5 ай бұрын

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.

Can you do this with a smartsheet web form?

@ExcelMacroMania

Жыл бұрын

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.

kindly tell me if want to delete all data from google form using vba then possible or not?

@ExcelMacroMania

Жыл бұрын

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.

what is IFQ in URL ??? doing? what does it do ? ITs constants or seperate for us ?

@ExcelMacroMania

Ай бұрын

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

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?

@ExcelMacroMania

9 ай бұрын

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.

@simplydoing9596

Ай бұрын

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

@ExcelMacroMania

Ай бұрын

@@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: kzread.info/dash/bejne/nqV60bieftOnXZc.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.

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

@ExcelMacroMania

Жыл бұрын

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?

@ExcelMacroMania

5 ай бұрын

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: kzread.info/dash/bejne/iKGgtbBmc5Wtd9I.htmlsi=UqCAunCDrwXFVGGK

Sir, if each time new data line come, automatic loading to google form, how it declare in VBA code . Thank you

@ExcelMacroMania

9 ай бұрын

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.

please, make a video vba excel to create file i/o to a folder in google drive. thk

@ExcelMacroMania

Жыл бұрын

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.

how to remove all data in members form?

@ExcelMacroMania

2 ай бұрын

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.

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?

@ExcelMacroMania

2 ай бұрын

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

@BrentBBlevins

2 ай бұрын

@@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 got error on ServerXMLHTTP

@simplydoing9596

Ай бұрын

first goto tools references and hit check next to MSXMLHTTP as reference follow video closely !

How to delete

@ExcelMacroMania

5 ай бұрын

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

@ExcelMacroMania

Жыл бұрын

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