notawahoo2
asked on
Does an Excel 2007 table look like a real table to SQL Server?
I have a named Table in Excel 2007 called ACCT_List. It's a list of account numbers. I want to use the Excel Table to join to tables in our SQL Server 2005 environment to retrieve some data about these accounts.
With VBA, I can generate a long string containing all the account numbers and then query "WHERE SQLTable.Acct in (x,x,x,x,x,x,x,x,x,x,x,x,x
I was hoping I could tell my VBA code that the Excel table is an actual "table", and be able to join the Excel Table with the SQL table with an inner join clause.
Is that possible? Maybe in Excel 2010??
Thanks!
With VBA, I can generate a long string containing all the account numbers and then query "WHERE SQLTable.Acct in (x,x,x,x,x,x,x,x,x,x,x,x,x
I was hoping I could tell my VBA code that the Excel table is an actual "table", and be able to join the Excel Table with the SQL table with an inner join clause.
Is that possible? Maybe in Excel 2010??
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ValentinoV
You should be able to use the OPENROWSET() function as explained in this article: https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_3025-Retrieving-Data-From-Excel-Using-OPENROWSET.html
Good points Zorvek.
notawahoo2: please note that the SQL Server needs to be able to access your Excel file somehow using that OPENROWSET function.
notawahoo2: please note that the SQL Server needs to be able to access your Excel file somehow using that OPENROWSET function.
ASKER
Thanks so much, very logical explanation of how it actually works!