ODBC query results won't convert to table

[paul_taylor]

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.

 

[Norie]

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]

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]

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]

It's not a straight swap - you need to check the syntax for Listobjects.add in help as I can't recall it offhand.