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.
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.
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.
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.
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.
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.
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.
Once you will complete the UserForm designing and properties, it will look like below form 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.
Sub Reset()
With frmForm
.txtEmpID.Value = ""
.txtEmpName.Value = ""
.optFemale.Value = False
.optMale.Value = False
.cmbDesignation.Clear
.cmbDesignation.AddItem "Executive"
.cmbDesignation.AddItem "Sr. Executive"
.cmbDesignation.AddItem "Team Leader"
.cmbDesignation.AddItem "Manager"
.cmbDesignation.AddItem "Sr. Manager"
.cmbDesignation.AddItem "Associate Director"
.cmbDesignation.AddItem "Director"
.txtAddress.Value = ""
End With
End SubIn 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.
Private Sub cmdReset_Click()
Dim i As VbMsgBoxResult
i = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset")
If i = vbNo Then Exit Sub
Call Reset
End SubNow, 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.
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.
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.
Change the Form title from ‘Untitled Form’ to ‘Employee Data Entry Form’. Also, add the description as ‘Form to capture employee details’.
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.
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.
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.
Understanding Pre-filled link of Google Form
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.
Private Sub cmdAdd_Click()
Dim i As VbMsgBoxResult
i = MsgBox("Do you want to transfer the data?", vbYesNo + vbQuestion, "Transfer")
If i = vbNo Then Exit Sub
Call SendToGoogle
End SubSub SendToGoogle()
'This Macro Requires Reference to "Microsoft XML, v6.0" (VBA Editor > Tools > References, find & select from list)
Dim URL_First As String 'Assign the first part of URL to send the data
Dim URL_Last As String 'Assign the last part of URL where we will update the information
Dim Form_URL As String 'To store the Form URL after merging Beginning and End URL
Dim HeaderName As String 'Variable to store the header type i.e. Content-Type
Dim SendID As String 'To store the information required to send a particular information to Google form
'Variables to store user inputs from Excel UserForm
Dim EmpID As String
Dim EmpName As String
Dim Gender As String
Dim Designation As String
Dim Address As String
'Assign User inputs to variables
EmpID = frmForm.txtEmpID.Value
EmpName = frmForm.txtEmpName.Value
Gender = IIf(frmForm.optFemale.Value, "Female", "Male")
Designation = frmForm.cmbDesignation.Value
Address = frmForm.txtAddress.Value
'Variable to store what we need to send to server
Dim TicketInfo As MSXML2.ServerXMLHTTP60 'XML variable to send the information to server
'Content-Type is actually a header type which tells the client what the content type of the returned content actually is. Google recognizes this header type
HeaderName = "Content-Type"
'SendID required to send a particular information to Google Form
SendID = "application/x-www-form-urlencoded; charset=utf-8"
'In actual link, we need to replace viewform? with formResponse?ifq&
'need to find the “name” attributes for the text boxes and the value for them
'add at the end &submit=Submit and use it, it must post all the data you specified in one step.
'formRespose is used to get the response from Google Form after submitting the details
'Submit - it is a command to submit the filled form
URL_First = "https://docs.google.com/forms/d/e/1FAIpQLSegAIkkFc1EoIaF5WQIxchEyrVLW3zsjbkP5WDHQB2sIIQK3Q/formResponse?ifq"
URL_Last = "&entry.837233042=" & EmpID & "&entry.413407046=" & EmpName & "&entry.1383004734=" & Gender & "&entry.356904377=" & Designation & "&entry.479173200=" & Address & "&submit=Submit"
'Creating the Final URL
Form_URL = URL_First & URL_Last
Set TicketInfo = New ServerXMLHTTP60 'Setting the reference of new server xmlhttp 60
TicketInfo.Open "POST", Form_URL, False ' Posting the entire link
TicketInfo.setRequestHeader HeaderName, SendID 'Specifies the name of an HTTP header.
TicketInfo.send 'Send all the information over google
'StatusText is provide the status of data submission. It will show OK if data will be successfully submitted
If TicketInfo.statusText = "OK" Then 'Check for successful send
Call Reset 'Call Reset procedure to reset form Excel Form after submitting the data
MsgBox "Thank you for submitting data!"
Else
MsgBox "Please check your internet connection & required details"
End If
End SubLet’s move to code window of module and write a sub procedure to show the user form.
Sub ShowForm()
frmForm.Show
End SubAssigning 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.
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.
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.
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
