How to retrieve data from excel online?

Question

I manage an excel file that is used by hundreds of users in different location (where the internet connection is often unreliable).

This file contains one "master data sheet" where few times a year data the users need to update some data used in several calculation.

Today the data in this sheet is updated manually (we send via email another workbook with the information that is copy-pasted in this sheet).

I would like to automate this process and put the information from my master data sheet on a public excel online file and use VBA to connect to this file and download the data to the "master data sheet".

I've quite easily managed to achieve this with google spreadsheets by creating a web connection to the google doc file but I really struggle to do the same with excel online (we have company accounts on microsoft and would be simpler to stick to excel online).

the users are all on win7 + excel 2010

the code I'm using to retrive the data from google is the following

Sub GetDataFromGoogle()


Dim i As Integer
With Sheet1
.Cells.Clear
    With .QueryTables.Add(Connection:="URL;https://docs.google.com/spreadsheets/addressXYZ", Destination:=Range("$A$1"))
        .Name = "MasterData"
        .PreserveFormatting = True
        .BackgroundQuery = False
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With
    DoEvents
    End With
    For i = 1 To ThisWorkbook.Connections.Count
        If ThisWorkbook.Connections.Count = 0 Then Exit Sub
        ThisWorkbook.Connections.Item(i).Delete
    i = i - 1
    Next i

End Sub

thanks!

How to retrieve data from excel online?

0 Answers