Post by imad koussaHi all,
It seems that microsoft is no longer supporting ODBC connection to Visual
Foxpro 9. I was using the ODBC connection to connect excel 2003 to the
visual foxpro tables. Now, I am supposed to use OLE database provider. I
have created the connection information to be used with the OLE driver (UDL
file) but I can't find how to use it from within the pivot table "get data"
wizard. I can only see the ODBC connections. Can anybody help?
Thanks
Try a VBA macro using the QueryTables function
MSDN Please WaitMSDN Library Please WaitOffice Development Please WaitMicrosoft Office 2003 Please WaitOffice 2003 Please WaitVBA Language Reference Please WaitMicrosoft Excel Visual Basic Refere... Please WaitReference Please WaitCollections Please WaitQ Please Wait QueryTables Collection Object [Exce...
Office Excel 2003 VBA Language Reference
QueryTables Collection Object [Excel 2003 VBA Language Reference]
Worksheet
QueryTables
QueryTable
Multiple objects
A collection of QueryTable objects. Each QueryTable object represents a worksheet table built from data returned from an external data source.
Using the QueryTables Collection
Use the QueryTables property to return the QueryTables collection. The following example displays the number of query tables on the active worksheet.
MsgBox ActiveSheet.QueryTables.Count
Use the Add method to create a new query table and add it to the QueryTables collection. The following example creates a new query table.
Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
"ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("B1"), Sql:=sqlstring)
.Refresh
End With
Change the connection string to ("OLEDB;Provider=VFPOLEDB.1;Data Source="path\database or folder name";",oSheet.RANGE("A1");sqlstring)Here's an example of doing it from the VFP side: ************LOCAL oExcel AS Excel.APPLICATIONLOCAL oBook AS Excel.WorkbookLOCAL oSheet AS OBJECToExcel = CREATEOBJECT("Excel.Application")oBook = oExcel.Workbooks.ADDoSheet = oBook.Worksheets(1)* 'Create the QueryTable object.LOCAL oQryTable AS OBJECT, sNorthwind AS STRINGsNorthwind = HOME(2) + [NORTHWIND\NORTHWIND.DBC]oQryTable = oSheet.QueryTables.ADD ;("OLEDB;Provider=VFPOLEDB.1;Data Source="+sNorthwind+";",oSheet.RANGE("A1"),"Select * from Orders")oQryTable.RefreshStyle = 2 && xlInsertEntireRows = 2oQryTable.REFRESH(.F.)oExcel.VISIBLE=.T.************-Anders