How to Retrieve Data from A Closed Excel Workbook?

Can we get data from a workbook even when it is closed? Yes, of course. In this section, we are gonna share with you how to retrieve data from an Excel workbook without opening it.

"Education is the most powerful weapon which you can use to change the world." - Nelson Mandela

Let's prepare something before we jump to the topic of today. First of all, create a new workbook named as "Example.xlsx". Save it in your local folder, e.g. "D:\Excel Magic World". Then you can open the workbook to add some data. In this example, we added some color codes in the spread sheet. These data will be retrieved by another Excel workbook later. Once you have done the data input, save and close the workbook.

Now let's create another workbook. You may add a command button anywhere on Sheet1. We changed the caption of the button to "Retrieve Data". After that, let's open VBAProject References to make sure "Microsoft ActiveX Data Objects 6.1 Library" is checked.

Aright, now double click the command button "Retrieve Data" and add the codes between Sub and End Sub. Refer to the following codes.

Private Sub CommandButton1_Click()

Dim conn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim i As Integer

With conn

.Provider = "Microsoft.ACE.OLEDB.12.0"

.Properties("Extended Properties").Value = "Excel 12.0; HDR=YES"

.Open "D:\Excel Magic World\Example.xlsx"

End With

rs.Open "Select * from [Sheet1$A:A];", conn, adOpenStatic, adLockReadOnly

i = 1

Do While Not rs.EOF

ThisWorkbook.ActiveSheet.Cells(i, "A") = rs(0)

rs.MoveNext

i = i + 1

Loop

End Sub

Here is a note about the extended properties setting in the codes. HDR=YES means there is a header in the selected record set. You may also use HDR=NO in the codes to tell Excel that the selected record set has no header. Try both and see the difference.