• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Google sheet

Kojo Robertson

New Member
Please I have this code to pull data from Google Sheet in Excel, however it only import (pulls) 100 rows from the google sheet while the data is about 3000 rows.
So is there anyway to pull 3000 rows of data from google sheet?

>> Use Code -tags <<
Code:
Sub Basic_Web_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://docs.google.com/spreadsheets/d/1eYp2ZvlOAWCTK3inTnz84wxAUZX502FdPRpfgeQXehc/edit?fbclid=IwAR3ox5aHjGmEiQMYjXJusRNVNgN5BhzGA4bf3jEdItTbR7NmZFY2_D1gQ6Y#gid=0", 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
 
Last edited by a moderator:
C#:
Sub GetData()

    qry = Application.EncodeURL("SELECT A, C, G, B")
    myURL = "https://docs.google.com/spreadsheets/d/1ZCrQlBjfMmO9636npMth9ErDr4kSnK8LhKb7JXS2KxU/gviz/tq?tqx=out:csv&sheet=1&tq=" & qry

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myURL, Destination:=Range("$A$1"))
        .Name = "myTable"
        .TextFilePlatform = 65001
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Sub DeleteData()
    ActiveSheet.UsedRange.Cells.Clear
End Sub
 
Back
Top