backpasob.blogg.se

#Odbc excel vba how to
#Odbc excel vba code

NOTE: First line of the range of data will become the Column Names of the Table. Say that this is the created file with name “DB Data.xlsx” Store data in your excel file and save it some location in your system.

#Odbc excel vba how to

To Read about how to use this Data Source for reading the Data Click Here

Browse and select the Excel file which you have created at step 1 and click ok.

Give the Data Source Name and description.

Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose ‘Excel Files’ from the list -> Press ‘Configure’.

Store data in your excel file and save it some location in your system.

In order to make your excel file ODBC Data Source, please follow the steps below Better yet, you might want to purchase a book that deals exclusively with SQL.To use your Excel file as Database for your project, First you might need to make your excel file as ODBC source using Microsoft Excel Driver. For more information, consult the online help. SQL is a standard language for performing database queries. After the data is stored in the pivot cache, the pivot table is created by using the CreatePivotTable method. This information is passed to the PivotCache object by setting the Connection and CommandText properties. In this example, the entire Budget table is selected. ♦ A query string: This is a Structured Query Language (SQL) statement that determines which records and fields are returned.

In this example, the connection string specifies an Open Database Connectivity (ODBC) data source that is a Microsoft Access file named budget.mdb. ♦ A connection string: This describes the type of data source and the filename. The PivotCache object needs the following information to retrieve the data from the external file: In the example in the previous section (which used data in a worksheet database), the SourceType argument was xlDatabase. Notice that the SourceType argument for the Add method of the PivotCaches collection is specified as xlExternal. PivotFields("ACTUAL").Orientation = xlDataField End With End Sub PivotFields("BUDGET").Orientation = xlDataField. PivotFields("DIVISION").Orientation = xlPageField. PivotFields("MONTH").Orientation = xlColumnField. PivotFields("DEPARTMENT").Orientation = xlRowField. TableDestination:=Sheets("PivotSheet").Range("A1"), _ TableName:="BudgetPivot") QueryString = "SELECT * FROM BUDGET" With PTCache Set PTCache = _ (SourceType:=xlExternal)ĭBFile = ThisWorkbook.Path & "\budget.mdb"ĬonString = "ODBC DSN=MS Access Database DBQ=" & DBFile ' Delete PivotSheet if it exists On Error Resume Next Application.DisplayAlerts = False Sheets("PivotSheet").Delete On Error GoTo 0 Sub CreatePivotTableFromDB() Dim PTCache As PivotCache Dim PT As PivotTable Listing 17-3: Generating a Pivot Table from an External Database It assumes that the budget.mdb database file is stored in the same directory as the workbook.

#Odbc excel vba code

The code that creates the pivot table is shown in Listing 17-3. The Access database consists of a single table that is identical to the data used in the previous example. The example in this section demonstrates how to write VBA code to create a pivot table based on data stored in an Access database file. As you probably know, Excel also enables you to use an external data source to create a pivot table. In the preceding example, the source data was in a worksheet.