How to use ADO and VBA to Read from Worksheets
Why Stack Overflow recommends it
Stack Overflow users link it when answering how to pull data from closed or hidden Excel workbooks in VBA without opening them.
Recommended by 1 people across 1 different months, 2020 to 2022.
What it is
A tutorial from Excel Macro Mastery showing how to use ADO (ActiveX Data Objects) with VBA to read data from Excel worksheets. It focuses on reading from closed workbooks and walks through several ADO approaches with examples and downloadable source code.
What people said
Recommends Munim Rashid · 2021-01 · Stack Overflow · on “Open Excel file for reading with VBA without display”
Using ADO ( AnonJr already explained) and utilizing SQL is possibly the best option for fetching data from a closed workbook without opening that in conventional way. Please watch this VIDEO [this video] . OTHERWISE, possibly GetObject(<filename with path>) is the most CONCISE way. Worksheets remain invisible, however will appear in project explorer window in VBE just like any other workbook opened in conventional ways. Dim wb As Workbook Set wb = GetObject("C:\MyData.xlsx") 'Worksheets will remain invisible, no new window appears in the screen ' your codes here wb.Close SaveChanges:=False If you want to read a particular sheet, need not even define a Workbook variable Dim sh As Worksheet Set sh
Cites michal roesler · 2020-09 · Stack Overflow · on “Microsoft.ACE.OLEDB.12.0; possible connection string formatting error causes "Could not find installable ISAM file" error?”
The macro I'm having problem with is written in Word VBA Editor and it's lunched from Word. It's goal is to link into Excel .xlsm file (treated as a customers database) via Microsoft.ACE.OLEDB.12.0; connection and, without even opening the file, read whole sheet from that file (sheet name = "data") into a dynamic array which will be searched later. I have some code written to do this, but it crashes on the connection string, probably because I don't know how to format it - these quotes I mean, I just don't understand how are they placed and why. Most important thing is that the code is written in Word VBA Editor and it's lunched from Word .docm file. Option Explicit Private Sub UseADOSelect(
Cites GWD · 2022-12 · Stack Overflow · on “Retrieve data from multiple workbooks without opening them”
Depending on the Workbooks and the data you are trying to read it might be possible to use ADO [this video] to get the data instead. It should be way faster than the formula approach you are using. If that's not possible an easy way to probably speed up your code significantly would be to take the two lines `.Copy` and `.PasteSpecial Paste:=xlPasteValues` out of the two loops. Of course you have to change the argument of the `Range` method then, in order to copy-paste everything at once.