LearnExcel&VBA
Member
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
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