Excel VBA Import Data: How to & Common Fixes

VBA & macros · Updated November 22, 2026

To import data in Excel using VBA, the quickest method is to use the Workbooks.Open method for external files or the QueryTables.Add method for pulling data from a database or web source. For a simple CSV or text file, you can use Workbooks.Open with the file path, then copy the data into your destination sheet. For more control, use QueryTables to specify delimiters, formatting, and refresh behavior. Below is a complete guide covering the main techniques, how to choose between them, common problems, and practical examples.

Why Use VBA for Importing Data

Excel’s built-in import tools (Data > Get Data) work well for one-time imports, but they don’t automate. VBA lets you import data with a single button click, refresh automatically, and handle repetitive tasks like merging multiple files. It also allows you to clean and transform data right after import, without manual steps. If you regularly receive CSV files, export reports from a database, or need to pull data from a website, VBA saves time and reduces errors.

A common misconception is that VBA import is only for programmers. In reality, you can record a macro while using the built-in import wizard, then modify the generated code. That gives you a working starting point without writing everything from scratch.

Methods to Import Data with VBA

There are several ways to import data, each suited to different scenarios. The main ones are:

  • Workbooks.Open – Opens an external Excel file, CSV, or text file as a new workbook, then you copy the needed range into your target sheet.
  • QueryTables.Add – Imports data from a database (like SQL Server), a web page, or a text file into a specific worksheet, with options for delimiters and formatting.
  • ADO (ActiveX Data Objects) – Connects directly to databases or even other Excel files to read data without opening them, useful for large datasets.
  • Power Query (Get & Transform) – Although not strictly VBA, you can trigger a Power Query refresh via VBA using ThisWorkbook.RefreshAll or by creating a query with VBA.

For most users, Workbooks.Open and QueryTables cover 90% of needs. ADO is more advanced and requires adding a reference to the Microsoft ActiveX Data Objects library.

How to Import a CSV or Text File with VBA

The simplest approach for a comma-separated file is to open it directly. Here’s a macro that opens a CSV file and copies its contents into the active sheet:

Sub ImportCSV()
    Dim filePath As String
    Dim targetSheet As Worksheet
    Dim importedWb As Workbook

    ' Change the path to your file
    filePath = "C:\Data\sales.csv"
    Set targetSheet = ThisWorkbook.Sheets("Data")

    ' Open the CSV file (Excel treats it as a workbook)
    Set importedWb = Workbooks.Open(filePath)

    ' Copy the used range from the CSV to the target sheet
    importedWb.Sheets(1).UsedRange.Copy _
        Destination:=targetSheet.Range("A1")

    ' Close the imported workbook without saving
    importedWb.Close SaveChanges:=False
End Sub

Save a copy of your workbook before running any macro, because macros cannot be undone with Ctrl+Z. This code assumes the CSV is in the default format (comma-delimited). If your file uses a different delimiter (e.g., semicolon), you need to use QueryTables instead, as shown below.

Using QueryTables for Delimited Text Files

When you need more control over delimiters or want to refresh the data later, use QueryTables.Add. This example imports a tab-delimited text file:

Sub ImportTextFile()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim filePath As String

    Set ws = ThisWorkbook.Sheets("Data")
    filePath = "C:\Data\report.txt"

    ' Clear any existing query table in the sheet
    ws.Cells.Clear

    ' Create a query table
    Set qt = ws.QueryTables.Add( _
        Connection:="TEXT;" & filePath, _
        Destination:=ws.Range("A1"))

    ' Set properties
    With qt
        .TextFileParseType = xlDelimited
        .TextFileTabDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileOtherDelimiter = "|"   ' change as needed
        .TextFileStartRow = 1
        .RefreshStyle = xlOverwriteCells
        .Refresh
    End With
End Sub

Note that QueryTables is a legacy feature but still works in Excel 365. For new projects, Microsoft recommends Power Query, but VBA can trigger a refresh of an existing Power Query connection.

Importing Data from a Database (ADO Example)

To pull data directly from a SQL database without opening a separate file, use ADO. First, in the VBA editor, go to Tools > References and check “Microsoft ActiveX Data Objects 6.1 Library” (or similar). Then use this code:

Sub ImportFromSQL()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ws As Worksheet
    Dim connString As String
    Dim sql As String

    Set ws = ThisWorkbook.Sheets("Data")
    connString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;"
    sql = "SELECT * FROM Orders WHERE OrderDate > '2024-01-01'"

    Set conn = New ADODB.Connection
    conn.Open connString
    Set rs = conn.Execute(sql)

    ' Copy the recordset to the worksheet
    ws.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

This method is efficient for large datasets because it doesn’t open a separate workbook. However, it requires knowledge of connection strings and SQL. If you’re not comfortable with that, use QueryTables with a connection string.

Common Problems and How to Fix Them

Even with correct code, you may encounter issues. Here are the most frequent ones:

  • “File not found” error – Check the file path, including drive letter and folder. Use full paths, not relative ones, unless the file is in the same folder as the workbook. You can use ThisWorkbook.Path to build a relative path.
  • Data appears in one column – Your delimiter is not set correctly. If using Workbooks.Open, Excel usually detects delimiters automatically, but if not, use QueryTables and specify the delimiter.
  • QueryTables refresh fails – The connection string may be outdated, or the source file is locked. Ensure the file is closed and the path is correct.
  • Macro runs but imports nothing – Check the sheet name and range. The destination may be wrong, or the source range is empty. Use UsedRange carefully; it may include formatting-only cells.
  • “Compile error: User-defined type not defined” – This happens when ADO is not referenced. Go to Tools > References and enable the ADO library.

Another common issue is that importing via Workbooks.Open changes the active workbook. Always set the target worksheet explicitly, as in the examples, to avoid errors.

Comparing Import Methods: Which One Should You Use?

Choosing the right method depends on your source and needs:

  • CSV/Text files – Use Workbooks.Open for simple one-time imports, or QueryTables if you need to refresh or handle complex delimiters.
  • Excel files – Workbooks.Open is easiest. You can also use GetObject to read without opening, but that’s more advanced.
  • Databases (SQL, Access) – ADO is best for performance and flexibility. QueryTables also works but may be slower for very large datasets.
  • Web pages – Use QueryTables with a URL, or use Internet Explorer automation (not recommended for new projects). Power Query is a better modern alternative.

For most everyday tasks, starting with Workbooks.Open and gradually moving to QueryTables gives you a solid foundation. ADO is worth learning if you work with databases regularly.

Safety and Best Practices

Before running any import macro, always save a backup copy of your workbook. Macros can overwrite data, and you cannot undo changes made by VBA with Ctrl+Z. Also, be careful with external data connections—they may contain malicious code if the source is untrusted. Only run macros from files you trust, and disable macros if you’re unsure about the source.

When importing data, consider the data types. If you import numbers as text, sorting and calculations may fail. Use Range.TextToColumns or format the destination cells after import. Also, if you import large files, Excel may become slow; consider using Application.ScreenUpdating = False at the start of your macro and turn it back to True at the end.

Frequently Asked Questions

How do I import multiple CSV files into one Excel sheet?

Loop through a folder using the Dir function, open each file, copy its contents, and paste it below the previous data. Use a variable to track the last used row. Example: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row before each paste.

Can I import data from a website using VBA?

Yes, use QueryTables with a URL as the connection. For example: Connection:="URL;http://example.com/data.csv". However, many modern websites use JavaScript, which won’t load with this method. In that case, use Power Query or a web scraping tool.

Why does my imported data show as ####?

This usually means the column is too narrow to display the numbers. After import, you can auto-fit columns with ws.Columns.AutoFit. If the data is text that looks like numbers, convert it using Range.TextToColumns or multiply by 1.

Is it possible to refresh imported data automatically?

Yes, if you use QueryTables, you can set .RefreshPeriod to a number of minutes, or call .Refresh in a timed event. For Power Query, use ThisWorkbook.RefreshAll in the Workbook_Open event.

What is the difference between Workbooks.Open and QueryTables?

Workbooks.Open opens the file as a new workbook, giving you full access to all its contents, but it’s slower and uses more memory. QueryTables imports only the specified data into a worksheet and supports refresh, but it’s limited to text, database, and web sources.

Can I import data without showing the Excel interface?

Yes, set Application.ScreenUpdating = False and Application.Visible = False at the start, but remember to restore them at the end. However, be careful: if your macro crashes, Excel may remain hidden. Always use error handling to reset these properties.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.