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

Комментарии • 43

  • @lmcerbo
    @lmcerbo 3 месяца назад +2

    That video made my day!!! Thanks a LOT!! :)

  • @muksinjaina3201
    @muksinjaina3201 Год назад +1

    Tks 😇😇
    God bless u

  • @Mr9Inchez3
    @Mr9Inchez3 Год назад

    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
      @ExcelMacroMania  Год назад +1

      Sure, I will add it to my list along with many other things I want to show about the interaction with Google Sheets.

  • @ChucksBasix
    @ChucksBasix Год назад +2

    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
      @ExcelMacroMania  6 месяцев назад

      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.

  • @simplydoing9596
    @simplydoing9596 2 месяца назад

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

    • @ExcelMacroMania
      @ExcelMacroMania  2 месяца назад

      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

  • @user-bs1hn7kj6d
    @user-bs1hn7kj6d Год назад

    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
      @ExcelMacroMania  Год назад

      Find step-by-step instructions in this article:
      excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html

  • @GoldenDoodleProductions
    @GoldenDoodleProductions 6 месяцев назад

    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
      @ExcelMacroMania  6 месяцев назад

      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

  • @edwardrambanpinem1359
    @edwardrambanpinem1359 11 месяцев назад

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

    • @ExcelMacroMania
      @ExcelMacroMania  11 месяцев назад

      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.

  • @user-pp7sq2sc1z
    @user-pp7sq2sc1z Год назад

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

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

  • @BrentBBlevins
    @BrentBBlevins 3 месяца назад

    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
      @ExcelMacroMania  3 месяца назад

      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
      @BrentBBlevins 3 месяца назад

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

  • @dragoesdeolivenca
    @dragoesdeolivenca 12 дней назад

    I get this error:
    Compile error:
    User-defined type not defined
    How to fix?

    • @ExcelMacroMania
      @ExcelMacroMania  9 дней назад

      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

  • @thepoolstatguy
    @thepoolstatguy 11 месяцев назад

    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
      @ExcelMacroMania  11 месяцев назад

      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
      @simplydoing9596 2 месяца назад

      @@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
      @ExcelMacroMania  2 месяца назад

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

  • @vutrungson262
    @vutrungson262 4 месяца назад

    how to remove all data in members form?

    • @ExcelMacroMania
      @ExcelMacroMania  4 месяца назад

      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.

  • @marchauge4115
    @marchauge4115 Год назад

    Can you do this with a smartsheet web form?

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

  • @krisis5997
    @krisis5997 Год назад

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

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

  • @paulsteel8739
    @paulsteel8739 13 дней назад

    No Option Explicit? 😞

    • @ExcelMacroMania
      @ExcelMacroMania  9 дней назад

      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

  • @jay-cgumabay7727
    @jay-cgumabay7727 Год назад

    i got error on ServerXMLHTTP

    • @simplydoing9596
      @simplydoing9596 2 месяца назад

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

  • @donasolliro6644
    @donasolliro6644 6 месяцев назад

    How to delete

    • @ExcelMacroMania
      @ExcelMacroMania  6 месяцев назад

      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.

  • @Novometix
    @Novometix Год назад

    does not work the way you did it

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