Exporting Excel range to SQL

The_Kurgan

Active Member
Joined
Jan 10, 2006
Messages
270
I've written code to upload data into SQL from Excel, but it is row-by-row iterative and takes forever when there are many rows, so I started looking for a way to do a range in one pass. The code below was found from a very old post on another site and it looked like something I could manage. After some massaging, I thought I had it, but I'm getting an "Invalid object name 'TempRange'" error on the .Execute line. Any thoughts on what I'm doing wrong with this range?


VBA Code:
Sub UpdateTable3()
    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Workbooks.Open "X:\MyPath\MyExcelFile.xlsm"
    Set wbkOpen = ActiveWorkbook
    Sheets("Data").Select
    Set rngName = Range("A2:E10")  'range defined here
    rngName.Name = "TempRange"   'TempRange defined here
    strFileName = wbkOpen.FullName
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYDB"
    cnn.Open
    nSQL = "INSERT INTO Workarea.[ad hoc]"
    nJOIN = " SELECT * from [TempRange]"  'TempRange used in SQL statement
    cnn.Execute nSQL & nJOIN  '<----- ERROR WHILE EXECUTING THIS LINE
    MsgBox "Uploaded Successfully"
    wbkOpen.Close
    Set wbkOpen = Nothing
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
805
Office Version
  1. 365
Platform
  1. Windows
Have you tried using the Named Range without the square brackets ?

VBA Code:
nJOIN = " SELECT * from TempRange"
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
805
Office Version
  1. 365
Platform
  1. Windows
I consulted my Brains Trust on this one and as I understand it you can't have 2 connection strings for the one SQL statement, and the select statement needs something like XLSM connection shown below.
The connection string in the code will only work for the Insert component, and is looking at your SQL DB for the TempRange in the Select statement
I'm afraid I have not done enough work in this area so hopefully someone else can help you out.

XLSM version from Excel connection strings - ConnectionStrings.com
VBA Code:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

I also added these to your code when I was trying to figure it out:

VBA Code:
    Dim strFileName As String
    Dim nSQL As String
    Dim nJoin As String
 

The_Kurgan

Active Member
Joined
Jan 10, 2006
Messages
270
Thanks Alex. Apologies for the late reply, as I've had to wrestle with IT to set AdHocRemoteQueriesEnabled to TRUE.

Not sure I fully understand. It looks as thought this is the only connection string I used:
VBA Code:
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYDB"

Although... I have whittled the code to the following:
VBA Code:
Sub UpdateTable3_17_21()
    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MyServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDB"
    cnn.Open
    cnn.Execute "Insert into Workarea.[ad shoc] Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=X:\MyPath\MyExcelFile.xlsm', [Data$])"
End Sub

But now on the Execute line, I'm now getting this error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0'' has not been registered"

Any more thoughts from the brain trust about what I'm missing?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
805
Office Version
  1. 365
Platform
  1. Windows
Sadly as I understand it, its just not as simple as you are hoping for.
You are hoping to do it in a single SQL statement but that statement can only make one connection at a time and you need two.
You need a connection to Excel for the read / select and another to SQL server / the database for the Insert Into.

I have done some googling myself and the options seem to be to (for the Excel read) either read through the table record by record and cell by cell or read it into the a recordset and follow a similar process.

If your Excel columns match the Database table exactly it requires less code in that you can loop through the fields but most of the code I have seen have an assignment line for each field being exported.

This 6 min video is one of the simpler ones I have seen
It still seems to indicate that explicitly mapping the load field by field is more reliable.

 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,805
Messages
5,655,406
Members
418,198
Latest member
jad1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top