Transfer Data from Microsoft Excel to Google Sheet

MS Excel To Google Sheet
MS Excel To Google Sheet

Most of the time, we use Microsoft Excel to process and store the data. We always face challenges if multiple users are sitting at different locations and using MS Excel or VBA UserForm to transfer and save the records in a particular database. In multi-user environment, we need a shared drive where we can keep our database or Excel file to get the user’s input in a centralized way. Getting a designated shared drive is costly and not possible in small companies or for individuals. To handle this scenario, we need a free and secure solutions.

Google Drive, Google Sheet and Google Form provide a dynamic solution to store data in a centralized way without worrying about user platform and location. It’s free of cost and fully secure hence, we don’t need to think about data protections and server maintenance.

All the features of Google Drive and Google Sheet raise a question in our mind, Can we use Google Drive or Google Sheet as a database? And the answer is Yes, we can.

In this post, we will learn how to Use Google Drive and Google Sheet to transfer data from MS Excel.

So without any delay, let us start developing this interesting application from scratch.

Develop Excel Based Form to Transfer Data from Excel to Google Sheet

Open a new Excel Workbook and save the file with the name “MS Excel To Google Sheet”. Make sure the extension of this file is .XLSM.

Saving Excel file with Macro enabled extension

Create a Home page (worksheet) and design a button so that user can click on that button to launch form and submit data. Design the landing sheet as available in below image.

Creating Home Sheet and Launch Form button in Excel Sheet

Move to Visual Basic Application Window

Let us move to Visual Basic Application window to design the User Form and write the code to automate the entire process.

To open VBA window, just click on Developer Tab, select Visual Basic icon available in Code group.

Open VBA window

Inserting a UserForm and Module

Now, we are in Visual Basic for Applications window. Let us insert a UserForm first.

To insert a blank form, click on Insert menu then select UserForm from menu items.

Inserting a blank user-form

To write sub procedure to Reset the UserForm and Transfer data to Google sheet, we need to add a Module in our project.

To insert a module, click on Insert menu then select Module from the available menu items.

Inserting a Module

Adding Microsoft XML, v6.0 Reference

As we need to write the code to transfer data from Excel Sheet to Google Sheet, this macro requires reference to “Microsoft XML, V6.0”.

This particular reference will help us in performing web queries to get and submit data. We will understand the coding thoroughly in later part of our tutorial.

To add the Microsoft XML, v6.0 reference, click on Tools menu then click on References. Find & select it from available references list in Reference window and then click on OK button.

Adding XML reference

Designing UserForm and Setting Properties

Now, we have done with adding UserForm, Module and XML Reference in this project. Let us design the user interface and set the properties of of TextBox, Option Button and CombBox.

Please refer the below image to design the UserForm and set the properties. We will refer the same name and controls in our coding.

Designing Form and setting required properties of controls and UserForm

Once you will complete the UserForm designing and properties, it will look like below form in running mode.

UserForm in running mode

Writing VBA code to Reset the form

To write a sub procedure to reset and initialize the user form, double click on Module1 in Project Explorer window and move to code window available in left side.

Write the below Sub Procedure in code window.

In this procedure, we are clearing values and selection from input controls. We are also adding the required elements in ComboBox (cmbDesignation).

Assigning Reset() to Form Initialization

Visit to code window of UserForm and write the below code.

Private Sub UserForm_Initialize()
Call Reset
End Sub

Adding code on click event of cmdReset button

Write the below code in form code window.

Now, the only one Sub Procedure is pending and that is for Transferring data from Excel to Google Sheet. We will complete this coding after creating a Google Sheet and Form in Google Drive. So, let’s move to the next phase of our development.

Creating Google Sheet and Google Form in Google Drive

Sign in to your Google account.

Sign in Google Account

Click on Google App icon available on the top-right side in window and select the Google Drive.

Creating a Folder in Google Drive

Create a new folder. To do that just click on + icon available on Top-left side in Google Drive and then click on Folder in available menu and then give the folder name as ‘Google Sheet Folder’ and then click on Create button.

Adding a new Google Sheet to the folder

Let us add a new Google Sheet to this folder and give the title as ‘Google Sheet Database’ and rename the ‘Sheet1’ to ‘Data’.

To do that just right click in Folder and select ‘Google Sheet Database’. It will open a blank Google sheet in new tab. Just visit to Google Sheet Tab and change the ‘Untitled Spreadsheet’ to ‘Google Sheet Database’. Also, rename the ‘Sheet1’ to ‘Data’. Now close this tab. Once you close the Google Sheet, you can see a Google Sheet available in folder.

Creating a Google Sheet and Changing the title and sheet name

Create a new Google Form in selected folder

Let us create a Google Form in same folder. To insert a Google Form, just right click in Folder then select More from the Pop-up menu, click on Google Forms and then select Blank Form. It will open a blank form with title ‘Untitled form’ in a new tab of your browser.

Creating a new Google Form

Change the Form title from ‘Untitled Form’ to ‘Employee Data Entry Form’. Also, add the description as ‘Form to capture employee details’.

Title and Description in Google Form

Now, we have done with Form title and description. Let us add the questions and required fields in our form. To add questions, just click on + icon available in floating menu. Also, choose the field type from the drop-down available to the right side of question title.

Here, we need to add fields for Employee ID, Employee Name, Gender, Designation and Address. See the below image and follow the steps.

Adding question and fields in Google Form

Now, we have done with adding questions and respective fields in Google Form. Let us select the response destination to store input data in Google Sheet.

By default, Google Forms create a Google Sheet to store its data but here, we are taking control in our hand and going to utilize the Google Sheet, which we created i.e. Google Sheet Database.

To select the response destination, click on ‘Responses’ available beside the ‘Questions’ button available on top of Google Form. It will open the ‘Responses’ window. Now, click on ‘More’ icon (three dots) then click on ‘Select response destination’ in pop-up menu. In select response destination window, just click on ‘Select existing spreadsheet’ and the click on ‘Select’, choose the ‘Google Sheet Database’ from the available Google sheets and then click on ‘Select’.

Now, Google Form linked with the Google sheet. Here, Google sheet will store all the responses submitted by either Google Form or Microsoft Excel. It will work like a centralized database to store entire data transferred by users.

Linking Google form with a Google Sheet

So, we have completed the Google Form and Google Sheet. Now, it’s time to get pre-filled link of Google Form so that it can be used in our VBA coding to transfer data from MS Excel to Google Sheet.

Getting Pre-filled Link of Google Form

To get the pre-filled link, just click on more icon available on top-right of Send button. Once you click on more icon (triple dots) then it will open the form in a separate tab. Just fill all the fields with required values, then click on ‘Get Link’ and again click on ‘Copy Link’. It will copy the link of pre-filled form. Just open Notepad and paste the link over there for further use.

Getting Pre-Filled link of Google Form

Understanding Pre-filled link of Google Form

https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/viewform?usp=pp_url”&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City”

This URL the pre-filled URL which we can use to fill and then submit the data. This URL has two different parts. Let’s break it and understand

First Part of URL

https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/viewform?

This is the first part of the URL, which is to view the google form. Here, we need to make some changes in the first part so that we can utilize in our coding to transfer the data. In this URL, just change viewform? with formResponse?ifq

Here, formRespose has been used to get the response from Google Form after submitting the details through VBA code.

Second Part of URL

“&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City”

Second part of URL contains “name” attributes for the text boxes, option button and combobox used in Google Form. Here, entry.837233042 and entry.413407046 are the name value for two text boxes, Employee ID and Employee Name. Similarly, entry.1383004734 is for Gender, entry.356904377 for designation and entry.479173200 is for Address.

For coding we need to replace the field values (bold text in below link) with respective variables name and then at the end of the link just add &submit=Submit to post all the data to Google Form.

“&entry.837233042=1254&entry.413407046=Dilip+Kumar&entry.1383004734=Male&entry.356904377=Manager&entry.479173200=New+City& “&submit=Submit”

Now, we understood the technicalities and changes required in URL. Let us write the pending Sub Procedure to transfer the data from MS Excel to Google Sheet.

Write Sub Procedure to send the data to Google Sheet

To understand the purpose of code, I have put comments wherever required. Please go through the code and comments to understand how it works.

Let’s call this Sub Procedure on click event of cmdAdd command Button. To write the code, just move to UserForm code window and write the below code.

Let’s move to code window of module and write a sub procedure to show the user form.

Assigning Macro on ‘Launch Form’ button available in ‘Home’ worksheet

Now, we have done with all the coding to handle this automation. Let us move to Excel window and assign macro on ‘Launch Form’ button available in ‘Home’ worksheet.

To assign the macro, just right click on ‘Launch Form’ button, select ‘Assign Macro’ and from Assign Macro window, just select the ‘Show Form’ from the available macros in ThisWorkbook macro list and then click on OK.

Assign macro

Testing the Form

So, our application is ready. Let’s open the Form and Google Sheet simultaneously in the same screen to test this automated Data Entry Form.

Live Data Transfer from Microsoft Excel to Google Sheet

Here, this application is working perfectly. We can transfer the data from Microsoft Excel to Google Sheet without facing any issues. This tool will work in multi-user environment.

Please watch this complete step by step tutorial on YouTube.

Part 1 – Creating UserForm in Excel and Write code to reset the form

Part 2 – Creating Google Sheets, Google Form, linking Google Form with Google Sheets and generating pre-filled link of Google Form

Part 3 – Understanding Pre-Filled Link of Google Form and Writing Code to Transfer Data from Excel to Google Sheets

Please click on below button to download the Excel file used in this tutorial. Password to open the VBA code is thedatalabs.

Download
Click to download

Keywords

Automating Google Sheet from Excel

Transferring Data from Microsoft Excel To Google Sheet

How to use Google Drive and Google sheet as a database for Excel Application?

Using cURL commands to transfer data from Excel to Google Sheet

VBA code to transfer data from Excel sheet to Google Sheet

Saving Excel data to Google Sheet automatically

How to create a Google form?

Getting pre-filled link of Google Form

LEAVE A REPLY

Please enter your comment!
Please enter your name here