CODEWITHSUNDEEP
excelazure-table-storageadodb
Alper K. Tunç
Alper K. Tunç

Reputation: 71

Is it possible to connect to Azure Table Storage from Excel?

We have an Excel file where we connect to our SQL Server to retrieve sales data using the below VBA code. Now we want to use Azure Table Storage to store sales data and feed our Excel from there. Is it possible to do that? I mean is it possible to connect to an Azure Table Storage with ADODB?

Sub UpdateSales()

  Dim objMyConn As New ADODB.Connection
  Dim objMyRecordset As New ADODB.Recordset

  objMyConn.CommandTimeout = 720
  objMyConn.Open "Provider=SQLOLEDB;Data Source=10.20.1.100;Initial Catalog=ofix;User ID=xxx;Password=yyy"
  objMyRecordset.Open "SELECT * FROM [OFIX].[dbo].[OFIX_SALES]", objMyConn, adOpenForwardOnly, adLockReadOnly, adCmdText

  Set ActiveSheet.PivotTables("Sales").PivotCache.Recordset = objMyRecordset
  ActiveSheet.PivotTables("Sales").PivotCache.Refresh

  objMyRecordset.Close
  objMyConn.Close

End Sub

Upvotes: 4

Views: 2787

Answers (1)

QHarr
QHarr

Reputation: 84465

Have you considered using powerquery? This is a From operation.

Excel 2016 +

On the Data tab, click Get Data > From Azure > From Azure Table Storage. If you don't see the Get Data button, click New Query > From Azure > From Microsoft Azure Table Storage.

Excel 2010 (some editions) & 2013

In the Power Query ribbon tab, click From Azure > From Microsoft Azure Table Storage. Follow the additional steps below.

Full details

If you are interested in pulling into Azure can you provide more details? There are services with more info in documentation here.

Interesting article here.

Upvotes: 2

Related Questions