VBA & Macro Problems

How to Access Teradata Query Data from Excel VBA

Camila MilosovichCamila Milosovich Nov 3, 2026 869 views

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.

How to Access Teradata Query Data from Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Enable ADODB References

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.

2
Declare Connection and Recordset Variables

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.

3
Open the Teradata Connection

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;".

4
Execute Query and Output Data

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 an ADODB Connection and Recordset in VBA
QueryTables Compatibility: If you are using newer versions like Microsoft 365, using the Range.CopyFromRecordset method might be cleaner than QueryTables.Add for outputting simple ADODB recordsets.

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. 1. Open WPS Spreadsheets: Launch WPS Spreadsheets and open a new or existing macro-enabled workbook.
  2. 2. Access the VBA Editor: Navigate to the 'Developer' tab and click on 'Visual Basic' to launch the integrated VBA Editor.
  3. 3. Enable ADODB Library: Go to Tools > References, check 'Microsoft ActiveX Data Objects Library', and click OK.
  4. 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.
Free and lightweight spreadsheet softwareHigh compatibility with Microsoft Excel VBA formats (.xlsm, .xlsb)Supports external database connections via ADODBFamiliar user interface for immediate productivity
QA img-9

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.