How to import data from Google Sheets into Excel with VBA

Question

I am trying to import data from Google Sheets into Excel but I think the problem is with the security settings in my computer.

Already tried to create a new query > from other sources > from web and changing the shared link with https://docs.google.com/spreadsheets/d/export?format=xlsx but it's not showing me any information

Also tried to run a macro but it's not retrieving me any information

Sub Import_Data()
 Dim conn As String
 conn = "URL;https://docs.google.com/spreadsheets/d/edit#gid=0"
 With 
     ActiveSheet.QueryTables.Add(Connection:=conn, Destination:=Range("$A$1"))
    .WebTables = "1,2"
    .Refresh True
 End With
End Sub

I want to have all the information coming into this Google Sheet connected to an Excel sheet to auto populate where I can work with this data more easily without copy pasting it into Excel. About the security settings, I cannot log in into a google account directly, my company computer is set to log into everything you need inside with the Windows ID and password. I also tried to edit the Data Source Settings in the Edit Permissions window for https://docs.google.com/spreadsheets using my windows credentials, but I had no luck. Any ideas on a workaround?

Hello santi_space. Can you provide another google spreadsheet for this example? this link seems broken: docs.google.com/spreadsheets/d/export?format=xlsx — Ferd
Also, check this out: stackoverflow.com/questions/51048827/… — Ferd
hi @Ferd I'm not able to share the complete link because the security settings will not let you open the file unless you're inside the company's organization — sati_space
@Ferd actually, already went through that post, but it didn't resolved my issue — sati_space
Welcome to SO! When you place a question try to add a minimum content: input sample, expected output sample, what did you try, research and where are you stuck. Without these minimums, there is no way to replicate your problem and it is hard difficult to help you. — David García Bodego

MikeMoy MikeMoy · Accepted Answer · 2020-12-11T11:00:21

The original code above from Peh will only import the first 100 rows of data from the Google Sheet. Use the below code to import all rows into Excel. You only need to change the keyString and gidString values in the below code. keyString specifies the specific Google Sheet you want to pull data from. gidString specifies which tab on the Google Sheet that you want to pull the data from.

Sub importGsheetLads()


 Dim keyString As String
  
 Dim gidString As String
 
 
 
 '' change the below keyString and gidString values as required
 
 
 keyString = "14hOICbxkYfCPpwpf8E6kwQHjrp2b33KjWfjMzjqGG8E"
 
 gidString = "493421964"
 
 
 
 
 
  
     With ActiveSheet.QueryTables.Add(Connection:="URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=" & keyString & "&gid=" & gidString, Destination:=Range("$A$1"))
        .Name = "q?s=goog_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1,2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        
        
 End With


End Sub

How to import data from Google Sheets into Excel with VBA

2 Answers