ODBC query results won't convert to table

paul_taylor

New Member
Joined
Feb 15, 2011
Messages
33
Two issues:

I'm pulling data from SQL server into Excel. That table is referred to throughout the workbook using Excel 2007 table structure. I created the table by manually accessing SQL server through Data > From Other sources > From SQL Server. The default is for Excel to convert the results into Excel 2007 table structure, which is good.

1. I'd like to refresh this connection through a macro, passing the username and password, so the user doesn't have to do this. Can this be done? I know how to do this as an ODBC query, but not through the native SQL server connection used to create the table.

2. I can create the table and refresh, using VBA macro, just fine if I do it as an ODBC query. BUT, the results are not formatted as an Excel 2007 table, and it's impossible to convert the resulting dataset into a table. Can I use ODBC and convert to table?

Either of these two would suffice.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Paul

Do you have code already to get the data into Excel?

How are you doing that?

I can't quite see how you couldn't use the Excel table formatting with it, it's only data isn't it.

Is the problem that you can't use the referencing you get when you convert too a table in formulas/calculations?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,378
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In VBA you need to create the table using Listobjects.Add rather than Querytables.Add if that's what you are doing now.
 

paul_taylor

New Member
Joined
Feb 15, 2011
Messages
33
In VBA you need to create the table using Listobjects.Add rather than Querytables.Add if that's what you are doing now.

When I replace querytables.add with listobjects.add I get error "Named argument not found".


With ActiveSheet.ListObjects.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh

this worked when I had it as

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,378
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It's not a straight swap - you need to check the syntax for Listobjects.add in help as I can't recall it offhand.
 

Forum statistics

Threads
1,141,850
Messages
5,708,982
Members
421,602
Latest member
jkpce1880

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