How to Access Teradata Query Data from Excel VBA
Question details
The user wants to connect Excel VBA directly to a Teradata database to run SQL queries and fetch data automatically without relying on manual exports.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data retrieval from a Teradata database using SQL queries within an Excel VBA macro.
- Observed behavior
- The user is currently exporting Teradata tables manually but aims to use an ADODB connection to query the table directly into Excel via VBA.
Ensure you have the appropriate Teradata ODBC driver installed on your system and that you have configured a valid Data Source Name (DSN) in your Windows ODBC Data Source Administrator.
Use an ADODB Connection and Recordset in VBA
Establish a connection to your Teradata database using ADODB in VBA, execute your SQL query, and output the recordset into your worksheet.
This method uses Microsoft ActiveX Data Objects to communicate with the Teradata driver. It bypasses the need for manual data exports and writes the query output directly into a specified Excel range.
Open the VBA Editor (Alt + F11). Navigate to Tools > References from the top menu. Scroll down and check the box for 'Microsoft ActiveX Data Objects x.x Library' (choose the highest version available), then click OK.
Create a new module and define your variables. You will need variables for the ADODB.Connection, ADODB.Recordset, and a string for your SQL query.
Instantiate the connection and open it using your Teradata DSN credentials: conn.Open "DSN=your_dsn;Database=your_db;Uid=your_userid;Pwd=your_password;".
Set your SQL string (e.g., "SELECT Name FROM Rate_Card"), open the recordset using your connection, and use Sheet1.QueryTables.Add or the Range.CopyFromRecordset method to place the results in your desired cell.

Use Excel's Built-in Data Query Tools
If you do not strictly require VBA, you can use Excel's native Get & Transform Data (Power Query) features to query Teradata directly.
Automate Database Queries with WPS Spreadsheets
WPS Spreadsheets provides robust compatibility with Excel macros, including ADODB database connections. You can seamlessly run your Teradata query VBA scripts within WPS Office to retrieve data effortlessly.
- 1. Open WPS Spreadsheets: Launch WPS Spreadsheets and open a new or existing macro-enabled workbook.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'Visual Basic' to launch the integrated VBA Editor.
- 3. Enable ADODB Library: Go to Tools > References, check 'Microsoft ActiveX Data Objects Library', and click OK.
- 4. Run Your Teradata Script: Paste your Teradata connection and SQL query script into a module, then press F5 to run and fetch the data into your sheet.

Frequently Asked Questions
Why am I getting a 'User-defined type not defined' error in my VBA script?
This error occurs if the Microsoft ActiveX Data Objects Library is not referenced in your project. To fix it, open the VBA Editor, go to Tools > References, and check the box for the Microsoft ActiveX Data Objects Library.
What information do I need to establish a Teradata ADODB connection?
You need a pre-configured Data Source Name (DSN) on your computer, along with the specific database name, your Teradata user ID, and your password. These must be formatted correctly in the connection string.
Can I output a Teradata recordset without using QueryTables.Add?
Yes, an easier and more robust method in newer versions of Excel is to use the CopyFromRecordset method. For example: Worksheets("Sheet1").Range("A1").CopyFromRecordset rec1.




