Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import XML data...Is there a size limit on the import? | Excel Discussion (Misc queries) | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
Import external data, data includes Excel function | Excel Worksheet Functions | |||
Tool bar: Data/Import external data/New database query | Setting up and Configuration of Excel | |||
how to Import data in excel office2003 with data more 65000 rows | Excel Worksheet Functions |