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

Integration of Google Sheet and Excel

I am trying to get data from google Sheet to MS Excel via below code.

and i have also tried to make it automate that i would not have to initiate the code to get the data and that is not working.

I have been trying to change the some of codes unnecessary actions such as writing Abc upto Z on top of row and then 123 upto 100.
Code above actions are indicating that the data will be copied upto Col"z" and Upto row "100" and i expanded the data in google sheet ahead to row 100 and col"z" but those data which was out of the limit could not pasted in MS Excel.

I've explored to know that how this code will be automate that i would not run this code and when any data will update in Google sheet (specific sheet with name) that data will be auto fetch and displayed in MS Excel.

Any help would be highly appreciated





Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://docs.google.com/spreadsheets/d/1ZCrQlBjfMmO9636npMth9ErDr4kSnK8LhKb7JXS2KxU/edit?usp=sharing", Destination:=Worksheets("Input").Range("$A$1:$M$1000"))
        .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,1"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Worksheets("Sheet1").Columns("A:Z").AutoFit


End Sub
 
Any help would be highly appreciated and please consider this reply for solution.

The solution i am looking for is to remove the first row and first column from the sheet because these are not available in google sheets.

or is there any way to make it automate that i would not have to press the button to get the updated result from google sheet. It should update it automatically.

Any help would be highly appreciated
 

Attachments

  • Employee.xlsm
    28.1 KB · Views: 8
Follow these steps to link Google Sheets to Excel
1. Go to your Google Sheet containing the data you want to export.
Click on:
a. File (top left)
b. Publish to the web
c. Link
d. Sheet1 (or whatever sheet your data is in)
e. Comma-separated values (.csv)
f. Published content & settings
g. Automatically republish when changes are made is ticked
h. Publish (blue button)
i. Copy the link
You have now created a link that downloads your sheet as a CSV file. If you copy the link into a browser, it will download the file. All we want now is Excel to automatically download this whenever you hit the “refresh” button, similar to a pivot table. The link will automatically update roughly every 5 minutes. So if you make a change to your data, the link will update in real-time.

I hope this helps!
Ben Martin
 
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