Data import
I am trying to create a function that extracts data from a
Sql Server into Excel. My current design which looks something like below, works O.K using "QueryTables.Add" but problem is that it can only return to a range on the worksheet, well as i want result returned to a variable which I can manipulate further. Does anyone know of a way of achieving this. Function xyz connstring = _ "ODBC;DSN=CSTW2K.SUNDB;UID=sa;PWD=" With ActiveSheet.QueryTables.Add( Connection:=connstring, _ Destination:=Range("a11"), Sql:=sqlstring) .RefreshOnFileOpen = False xlInsertDeleteCells .Refresh End With End Function N.B I tried using "SQLRequest" which returns an array but I could not solve problem with "XLOdbc" that it kept referring to. |
Data import
Try using ADO:
http://www.erlandsendata.no/english/vba/adodao/ Examples are to Access, but should be adaptable to SQL Server. -- Regards, Tom Ogilvy "gerald" wrote in message ... I am trying to create a function that extracts data from a Sql Server into Excel. My current design which looks something like below, works O.K using "QueryTables.Add" but problem is that it can only return to a range on the worksheet, well as i want result returned to a variable which I can manipulate further. Does anyone know of a way of achieving this. Function xyz connstring = _ "ODBC;DSN=CSTW2K.SUNDB;UID=sa;PWD=" With ActiveSheet.QueryTables.Add( Connection:=connstring, _ Destination:=Range("a11"), Sql:=sqlstring) .RefreshOnFileOpen = False xlInsertDeleteCells .Refresh End With End Function N.B I tried using "SQLRequest" which returns an array but I could not solve problem with "XLOdbc" that it kept referring to. |
All times are GMT +1. The time now is 08:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com