Uploading data from Excel 365 to Azure Sql Server using VBA only

Question

Although I used to be pretty advanced using VBA many moons ago, apparently that guy has left the building. I just recovered some very large commodity and equity data sets I had in excel files from years ago. I have compiled them all together into one file and am trying to upload the data to my Azure SQL server. I cannot find ANYTHING anywhere on how to load data TO Azure, but I find many articles on loading FROM Azure.

Inside excel you can only create a connection to Azure SQL to IMPORT data into excel, one way street, no way to make a connection you can load from.

Any help appreciated. I can always use the old CSV's, read them in using c# in my web app, but I can update the data to current each week in this one file and I'd prefer to write the whole routine in VBA to get the data, check it, and load it to Azure.

Note: Yes, I know how to use ADODB connections but I cant find any drivers specific to using Azure SQL

Further update:

Using the following and I've tried 2 dozen variations, produces this error each time. And I installed the Sql Native Client Driver #12

Here is the function with the Driver, which I believ eis the issue, or the permissioning, because the azure sql db exists. This is the connection string from the portal

Public Function ExportExcelDataToAzureDb(wsSource As Worksheet) As Boolean

    'I am using Activex Data Object 2.8 reference
    Dim connectionString As String
    Dim oConn As ADODB.Connection
    Dim record As ADODB.Recordset
    Dim cmd As ADODB.Command

    connectionString = "Driver={SQL Server Native Client 12.0};Server=tcp:myazuresqlserver.database.windows.net,1433;Initial Catalog=myappdb;Persist Security Info=False;User ID=myuser;Password={mypass};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    Set oConn = New ADODB.Connection
    oConn.connectionString = connectionString

    oConn.Open

    'load the data
    ExportExcelDataToAzureDb = True

End Function

Obviously, I have substituted for the server, db, user and password

So you cannot INSERT INTO... from vba, but you can SELECT * FROM..? — Scott Craner
– Scott Craner, Commented Sep 30, 2019 at 20:01
@Scott Craner...I could if I could find what Driver to use, specifically for Azure Sql Db — dinotom
– dinotom, Commented Sep 30, 2019 at 20:04
did you see the answer here? social.msdn.microsoft.com/Forums/en-US/… — Scott Craner
– Scott Craner, Commented Sep 30, 2019 at 20:10
@Scott Craner...Well there is proof that alphabet is skewing results, how does that link not come up on the first two pages of this search "Export data to Azure Sql Db using Excel VBA"... Is the latest driver still 2012? — dinotom
– dinotom, Commented Sep 30, 2019 at 20:20

Md. Suman Kabir · Accepted Answer · 2020-09-17 15:51:26Z

After a LOT of searching for various connection strings examples AND about 100 different tests of string variations.

Here is the proper connection string format to use with Excel VBA to import/export data from Azure SQL Database. I am providing my whole function for inserting data, with some parts removed for security concerns.

Option Explicit
Public dbConn As ADODB.Connection
Public adodbRecordSet As ADODB.recordSet
Public rsc As ADODB.recordSet
Public Const AzureDataSource As String = "tcp:yourazuredbservername.database.windows.net,1433"
Public Const AzureDbName As String = "yourazuredbname"
Public Const dbDriverProvider As String = "SQLNCLI11"
'I am using Activex Data Object 2.8 reference, SQL Native Client 11

Public Function ExportExcelDataToAzureDb(wsSource As Worksheet, dataRange As Range) As Boolean
    
    Dim connectionString As String, sql As String
    Dim cnt As Integer
    Dim cCell As Range
    
    connectionString = "Provider=" & dbDriverProvider & ";" & "Password=yourpassword;User ID=yourusername; " & _
                    "Initial Catalog=" & AzureDbName & ";" & _
                    "Data Source=" & AzureDataSource & ";"""
                    
    Set dbConn = New ADODB.Connection
    Set adodbRecordSet = New ADODB.recordSet

    dbConn.connectionString = connectionString
    dbConn.Open
  
    ' do work here
    cnt = 0
    With wsSource
        For Each cCell In dataRange                    
        
            sql = "your insert statement here"
          
             adodbRecordSet.Open sql, dbConn, adOpenDynamic, adLockReadOnly, adCmdText
             cnt = cnt + 1
        Next cCell
    End With

    'clean up
    dbConn.Close
    ' log count of uploaded data records
    Call PrintLog("Records loaded on " & Now & ": " & cnt & " symbol records")
    ExportExcelDataToAzureDb = True

End Function

If anyone knows how to get this working with later versions of the ActiveX Data Object or the SQL Native Client Library please post another answer.

Jun Liu · Accepted Answer · 2020-02-05 19:52:30Z

0

After trying many solution, i have come to the conclusion that the most important thing is

using Activex Data Object 2.8 reference, SQL Native Client 11. using the lower version seems to work best

for my connection string i am using what is below format

connection = "Driver={SQL Server Native Client 11.0};" & _
                        "Server=tcp:YourDataBase.net,1433;" & _
                        "Database=YourDatabaseName;" & _
                        "Uid=XXXX;" & _
                        "Pwd=XXXXXXX;" & _
                        "Encrypt=yes;Connection Timeout=30;"

Hope this help other people. This was super frustrating for me

answered Feb 5, 2020 at 19:52

Jun Liu

251 silver badge7 bronze badges

1 Comment

dinotom Over a year ago

Thanks for posting, But I noted in the code (as a comment) I posted as the answer, that those two libraries were the only ones I got it to work with.

Collectives™ on Stack Overflow

Uploading data from Excel 365 to Azure Sql Server using VBA only

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related