• Home
  • App Store
    • International Users
    • Indian Users
  • Subscribe
  • About Us
  • Productivity
    • Excel
      • VBA
      • Tiny Tips
    • Android
    • WordPress
  • Social Media
  • Misc
    • Cricket
      • IPL Fixtures
      • VIVO IPL 2016 New Face
      • T20 World Cup
      • Cricket World Cup
    • India Elections
      • How Not To Prepare Yourelf For Polling
Menu
  • Home
  • App Store
    • International Users
    • Indian Users
  • Subscribe
  • About Us
  • Productivity
    • Excel
      • VBA
      • Tiny Tips
    • Android
    • WordPress
  • Social Media
  • Misc
    • Cricket
      • IPL Fixtures
      • VIVO IPL 2016 New Face
      • T20 World Cup
      • Cricket World Cup
    • India Elections
      • How Not To Prepare Yourelf For Polling
  • Latest News
    Export Outlook Emails To Excel – VBA Code – 5,700+ Free Downloads
  • Latest News
    Excel To VCF Converter 1.1 – Excel To Vcard – 68,000 Downloads
  • Latest News
    Extract Web Page with Tables – Parse HTML Table – Data Mining Tool
  • Latest News
    Send Mass Email from Excel VBA 1.3 – Multiple Recipients – In Outlook
  • Latest News
    Web Scraping – Extract Data from Website – To Excel VBA
  • Latest News
    VCF To Excel Converter – Open VCF File – Free 28,000+ Downloads
  • Latest News
    Print Screen Automation – Take Screenshot, Save to File and Attach in Excel
  • Latest News
    Excel VBA – Import Data from Another Workbook – Macro to Query Excel
  • Latest News
    How to Telnet from Excel & Autorun Commands
  • Latest News
    Excel VBA – FTP Transfer – Files Download Upload
  • Latest News
    Microsoft Office – Communicator API – Verify Availability Status
  • Latest News
    Excel IP Address Lookup – Bulk GeoIP Lookup
  • Latest News
    Extract URL Links from Sitemap.xml file using Excel
  • Latest News
    Trace Lost Phone – Even Without Internet – Using SMS
  • Latest News
    Universal VCF to Excel and Excel to Vcard Converter
  • Latest News
    Download Facebook Videos – How To Get YouTube Video Downloader?
  • Latest News
    Block Game Requests On Facebook & Candy Crush Invites
  • Latest News
    How to Get stock prices in Excel? – Best Live Stock Ticker
  • Latest News
    How to Get Historical Stock Quotes In Excel – Google Finance
  • Latest News
    JSON Parser – Convert JSON To CSV – Excel VBA Macro
  • Latest News
    How To Screen Record – in IOS, iPhone, Android, Windows, Mac ?
  • Latest News
    How To Remove Duplicate Values in Excel, Google Sheets?
  • Latest News
    Best Bitcoin Trading Platforms – Cryptocurrency Market
  • Latest News
    Lock System at Scheduled Time with Excel
  • Latest News
    How to control Facebook Addiction? & Whatsapp Addcition
  • Latest News
    2018 – Hack Whatsapp Friends – Status Photos
  • Latest News
    Making SORT More Simple and Faster in Excel with Advanced Options
  • Latest News
    WordPress – Recent Posts Widget with Thumbnail – Without Plugins
  • Latest News
    Compare Two Excel Sheets – Highlight Differences – Macro VBA Code
  • Latest News
    Display Related Posts with Thumbnails in WordPress without Plugins
  • Latest News
    Post to Facebook Timeline through Email
  • Latest News
    100 CryptoCurrency Price – Ticker in Excel – Using VBA
  • Latest News
    Engineering Unit Converter Download – Excel Table – Feet To Meters
  • Latest News
    Android ICS File Viewer – Calendar file View in Exce;
  • Latest News
    Download – Backup Facebook Twitter Gplus Linkedin – Data To PC
  • Latest News
    How to remove #n/a in Excel? – Fixing Vlookup #n/a
  • Latest News
    #NoForwardsDay – New WhatsApp Texting Pattern that Would Make You Go Crazy
  • Latest News
    Sudoku Excel Download – Puzzle Solver – Free Game Help
  • Latest News
    Top 4 Whatsapp Tips and Tricks You Didn’t know Existed
  • Latest News
    Mortgage Calculator – Loan EMI Scheduler
  • Latest News
    Learn How To Code JavaScript in VBA
  • Latest News
    How To Open VCF File – Online VCF Viewers – 4 Easy Ways
  • Latest News
    How To Block Unwanted Text Messages in Iphone?
  • Latest News
    How To Scrap A Website Keywords? – Keyword Scraping Excel
  • Latest News
    Login to Facebook, Twitter, Google+ in IE from Excel
  • Latest News
    Excel Instant Messenger – Microsoft Office Communicator API Library
  • Latest News
    Skyrocket Website Traffic With Reddit
  • Latest News
    Excel Games Free Download – 2048 Tile Game
  • Latest News
    How to Speed Up Google Chrome
  • Latest News
    VBA Code – Create Index of Worksheets – with Links to each Sheet

Excel VBA – Import Data from Another Workbook – Macro to Query Excel

  • Excel Import Data From Another Workbook – VBA Codes

    To pull data from an external Excel file, use on of these scenarios.

    1. Closed Excel file: Using VBA Import data with Workbook object
    2. Opened Workbook: Using VBA Read Excel file.
    3. External Reference within Worksheets.
    4. ODBC Data Import from another workbook.

    Excel – Pull data from another Workbook using VBA

    To understand how to get data from another Excel file, lets assume these 2 workbook names.

    • Source: In this workbook, VBA code will be executed to write or import data from/to Target file.
    • Target: This workbook has the data that the Source Workbook will read or modify through VBA.

    1. VBA To Update Closed Workbook

    This Excel vba import data from another workbook without opening the file manually.

    First we will create an Workbook object to refer the external Excel file.  And use that object to import data into our Active workbook or Source Workbook.

    Let’s see the actual VBA code for this purpose. Copy paste the below code to VB Editor and execute the code by pressing F5. Make sure that the Target file exists in correct path as mentioned in Target_Path in the code, before executing the code.

    Excel VBA - Import data from Another Workbook - Without Opening file
    Visual Basic
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    Sub VBA_Read_Data_Another_External_Workbook()
     
        '''''Define Object for Target Workbook
        Dim Target_Workbook As Workbook
        Dim Source_Workbook As Workbook
        Dim Target_Path As String
        
        '''''Assign the Workbook File Name along with its Path
        '''''Change path of the Target File name
        Target_Path = "D:\Sample.xlsx"
        Set Target_Workbook = Workbooks.Open(Target_Path)
        Set Source_Workbook = ThisWorkbook
        
        '''''With Target_Workbook object now, it is possible to pull any data from it
        '''''Read Data from Target File
        Target_Data = Target_Workbook.Sheets(1).Cells(1, 1)
        Source_Workbook.Sheets(1).Cells(1, 1) = Target_Data
        
        '''''Update Target File
        Source_data = Source_Workbook.Sheets(1).Cells(3, 1)
        Target_Workbook.Sheets(1).Cells(2, 1) = Source_data
        
        '''''Close Target Workbook
        Source_Workbook.Save
        Target_Workbook.Save
        Target_Workbook.Close False
        
        '''''Process Completed
        MsgBox "Task Completed"
        
    End Sub

    Also Read: Excel VBA Convert - Amount (Numbers) To Words - Both US & India Formats

    2. VBA Read Excel file or Write To Open Workbook?

    If a workbook is already in opened and executing, then you can reference that Excel with its name through ‘Workbooks’ collection. You have to use the workbook name itself inside the code to read or write content as mentioned in this sample code.

    Excel VBA - Read Data from Opened Workbook
    Visual Basic
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Sub Write_To_Open_Excel()
        Dim wb As Workbook
        
        'Reference Workbook with its name
        Workbooks("Book2").Worksheets("Sheet2").Activate
        Workbooks("Book3.xls").Worksheets("Sheet2").Activate
        
        'Search for Each Opened Workbook
        For Each wb In Workbooks
            If wb.Name = "Book2" Then
                wb.Sheets(1).Cells(1, 1) = "Writing To Open Excel Worksheet - Testing"
            End If
        Next
    End Sub

    3. External Reference to Import Data from another Workbook

    With this technique, in the Excel we pull data from another cell by using references. For example, in Cell A1 if we need to get date from Cell B1, we enter “=B1” in cell A1. This is a reference that is made within the scope of current workbook.

    In our example if we need to refer the Target sheet, use the command as below.

    =’D:\[sample.xlsx]Sheet1′!A2

    This will fetch the data from the external workbook.

    Reference from Microsoft: How to create External reference and pull data from another excel?

    Also Read: Excel VBA - Loop Through All Open Workbooks - Get Names

    4. Data Import Option or ODBC in Excel VBA

    This is similar to Data Import facility available in Excel. To do this, the Target workbook should be having some table defined in it.

    To import the data from Target, Go to Source Workbook, Data->From Other Sources ->From Microsoft Query and Give the path of the Target Workbook or use the below code by modifying the File path and Column Header Details.

    Excel Query - Fetch Data from Another Workbook - VBA code
    Visual Basic
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    Sub Data_Import_Recorded_Macro()
     
        '''''Change File path and Column Headers to Use this code
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=Excel Files;DBQ=D:\Sample.xlsx;DefaultDir=D:;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandText = Array( _
            "SELECT `Sheet1$`.Column1, `Sheet1$`.Column2, `Sheet1$`.Column3" & Chr(13) & "" & Chr(10) & "FROM `D:\Sample.xlsx`.`Sheet1$` `Sheet1$`" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_Query_from_Excel_Files"
            .Refresh BackgroundQuery:=False
        End With
        
    End Sub

    These are some of the methods that are available to import from an external workbook to current active workbook. But still, this is not the limit.

    Note: To read a Plain Text file or binary file, use VBA File Handling commands as in this link. How to Read contents from a Text File using VBA?

    There might be other feasible methods known to others. If there is any, please post a reference as you find.

    Author: Kumarapush

    Kumarapush has experience in IT industry for 12+ Years including 10 Years in MNC. Has vast expertise in Microsoft Excel automation using VBA Macro. Excel to CSV converter is one of his famous tool that has been downloaded & used by 68,000 users around the globe.

    View all posts by Kumarapush | Website


    Previous Post: VBA Merge Text Files – Combine Data Files Into One with Excel
    Next Post: Lock System at Scheduled Time with Excel


    • rowbigred26

      Using the first VBA option, I see my data download into my target workbook however then it disappears shortly after. Any thoughts?

      • https://officetricks.com/ kumarapush

        Email me or paste the exact code that you executed in your system to debug the issue.

    • Delora Bulger

      Thoughtful discussion – my colleague yesterday located http://goo.gl/vshUfg to merge pdf . It’s kind of effortless to use and it’s convenient – I found out that they are giving a 7 day promotion currently

    • Prihlasovak

      Thank you. This post was very helpful.

      Would it be somehow possible to access a closed worksheet locked with a password?

      My idea is to have a sheet for each station at a production plant, where the attendants would have access only to some positions. The manager would have a “master file” that would download all necessary informations from the stations.

      I have a vague idea how to do it with a workaround, but accessing passworded files would be much better.

      • Prihlasovak

        Nevermind, I have found the answer. It just needs another parameter (,Password:=”Password”) in Workbooks.Open.

    • Samuel

      hi
      can u please say how t export data from working sheet which is in raw data

    • LIZZY

      Exроѕіtоrу!
      But I thіnk уоu ѕhоuld еmрlоу tо uѕе оf grарhісѕ іn еxрlаіnіng ѕоmе of thе
      ѕtерѕ tо fоllоw. Fоr іnѕtаnсе,” Tо іmроrt the dаtа frоm Tаrgеt, Gо tо Sоurсе
      Wоrkbооk, Dаtа->Frоm Othеr Sоurсеѕ ->Frоm Mісrоѕоft Quеrу аnd Gіvе thе
      раth оf thе Tаrgеt Wоrkbооk оr uѕе thе bеlоw соdе bу mоdіfуіng thе Fіlе раth
      аnd Cоlumn Hеаdеr Dеtаіlѕ” wоuld hаvе bееn еаѕіlу undеrѕtооd іf ассоmраnіеd by
      grарhісѕ еxрlаnаtіоn.

    • Paul Pascal

      Specifically with the Data Import option, this can be a real alternative to Power BI or one of the o there high level dashboard that are available on the market today. If you’re a small business owner and have metrics for instance, this is way more affordable and with just a bit of effort you can have an excel dashboard of everything you need for your KPIs.

    • Kinerd

      excellent, thank you

      • https://officetricks.com/ kumarapush

        Thank you.

Post Categories
Popular Posts
Export Outlook Emails To Excel...
Excel To VCF Converter 1.1 –...
Extract Web Page with Tables –...
Send Mass Email from Excel VBA...
VCF To Excel Converter –...
Web Scraping – Extract Data...
Print Screen Automation –...
Excel VBA – Import Data from...
How to Telnet from Excel &...
Excel VBA – FTP Transfer...
Microsoft Office – Communicator...
Excel IP Address Lookup –...
Recent Posts
Enable Insert button in Excel greyed disabled show
Excel Insert Button is Disabled...
Excel VBA Find String in Range Return Row Change Background color Remove Duplicates
Excel VBA - Find String in Range...
Excel VBA Convert Amount in Numbers to Words US Dollar Million Indian Rupee Lakhs Crores finance
Excel VBA Convert - Amount (Numbers)...
Excel VBA Code Get Names of All open workbook path Loop through
Excel VBA - Loop Through All Open...
Name Numerology Lucky Number Calculator Baby names Business name
Name Numerology Calculator - Lucky...
Similar Pages
  • Excel VBA Find String in Range Return Row Change Background color Remove Duplicates
    Excel VBA – Find String in...
  • Excel VBA Convert Amount in Numbers to Words US Dollar Million Indian Rupee Lakhs Crores finance
    Excel VBA Convert – Amount...
  • Excel VBA Code Get Names of All open workbook path Loop through
    Excel VBA – Loop Through All...
  • Name Numerology Lucky Number Calculator Baby names Business name
    Name Numerology Calculator –...
  • How To type Subscript in Excel Superscript strikethrough underline font
    How To Type Subscript in Excel –...
  • Excel VBA Merge Cells Unmerge Range Selection objects
    Excel VBA – Merge Cells –...
Popular Tags
  • Email Marketing Block Apps in FaceBook Task Manager print screen VCF HTML Table Import Data Candy Crush Outlook Record Macro android app development SEO Convert URL to IP HTML/ Javascript QR Code Generation VCARD Timer in Excel Countif Google+ bitcoin vba color index vba color codes Data Mining Android social media Twitter bulk email Google Adsense Tips Excel VBA Calendar in Excel Excel Macro WhatsApp Facebook mass email outlook vba Wordpress
FREE Tech Updates

Home

  • Home
  • App Store
    • International Users
    • Indian Users
  • Subscribe
  • About Us
  • Productivity
    • Excel
      • VBA
      • Tiny Tips
    • Android
    • WordPress
  • Social Media
  • Misc
    • Cricket
      • IPL Fixtures
      • VIVO IPL 2016 New Face
      • T20 World Cup
      • Cricket World Cup
    • India Elections
      • How Not To Prepare Yourelf For Polling

Trending Topics

    • Learn How to Access WhatsApp in Web
    • Compare Data In Two Excel Sheets
    • Find IP Address Location – GeoIP Lookup
    • Lock a Folder In Windows Using This Simple Excel Macro Trick
    • Download Free Tool to Convert VCF Contacts to Excel

Join Us

Copyright © 2018 OfficeTricks.com