VBA Code Copy Data by Link

Excel&VBA · Jul 27th 2020

I am searching for code that copy data from google sheet link into Ms Excel with same format.

All the links will be available in column A row wise. If i run the code then code will copy data from google sheet link and will paste into new MS Excel sheet with sheet name.

Please share the code if that is possible via VBA.

Excel&VBA · Jul 30th 2020

Anyone up to help.

**royUK** · Jul 31st 2020

Try this

Code

Option Explicit
Sub Get_Data_GoogleDoc()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("A1").Value, Destination:=Range("$A$4"))
.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

Display More

sobi_256 · Jul 31st 2020

the above code will only get 100 rows, if u change the link in A1 to the following it will get all the data, but i don't know how to get cell background colours

Code

https://docs.google.com/spreadsheets/d/e/2PACX-1vRMXTf2g0XPMHCkbztVbC6btk6ewb0E1Js_4NwS4QXUpmL44WZTV8Qha1nVa1zWXw/pubhtml

**royUK** · Jul 31st 2020

It's importing data, not formats, etc. You need to manually add formatting or simply convert theGoogle Sheet to Excel.

Excel&VBA · Aug 3rd 2020

Thank you so much

VBA Code Copy Data by Link

Share

Similar Threads

Cell value to userform

Copy Sheet to New Workbook & Automatically Name Workbook

VBA Code to Obtain worksheet user friendly label or name

How to copy selected cells if condition met, paste as link, and sort when copied all together

Extracting Information From A Linked File

How to remove pivot table link with source

Tags