• 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

Top