Get data from a list of CLOSED workbooks using VBA

Panthers0027

Board Regular
Joined
Apr 13, 2009
Messages
89
Here's a tricky one:

I have one Master Sheet that fetches information off of 30 other workbooks. The data is uniform accross all workbooks.

As of right now, I have code written that will update the master, but ONLY if the other workbooks are open.

Is it possbile to get the same data off of closed workbooks?

Here's what I'm using now:

Code:
Sub Master()
 
Windows("Master.xls").Activate
 
Dim Reps As Integer
Reps = Range("Reps").Column
 
Dim LastColumn As Long
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
 
Range(Cells(3, 1), Cells(50000, LastColumn)).ClearContents
 
Dim LastRepRow As Long
LastRepRow = Cells(Rows.Count, Reps).End(xlUp).Row
 
Dim i As Integer
For i = 3 To LastRepRow
 
Dim List As String
List = Cells(i, Reps)
 
[B]Windows(List).Activate[/B]
 
    Dim LastContact As Long
    LastContact = Cells(Rows.Count, 2).End(xlUp).End(xlUp).Row
 
    Range(Cells(3, 2), Cells(LastContact, LastColumn)).Copy
    Windows("Master.xls").Activate
 
    Dim LastMasterContact As Long
    LastMasterContact = Cells(Rows.Count, 2).End(xlUp).End(xlUp).Row + 1
 
    Range("B" & LastMasterContact).Select
    Application.Run "PERSONAL2.xls!Paste_Values"
 
    Dim LastMasterContact2 As Long
    LastMasterContact2 = Cells(Rows.Count, 2).End(xlUp).End(xlUp).Row
 
    Range("A" & LastMasterContact).Value = List
 
    Range("A" & LastMasterContact).AutoFill Range("A" & LastMasterContact & ":" & "A" & LastMasterContact2)
 
Next i
Columns("A").Replace What:=" Contacts.xls", Replacement:=""
 
End Sub

Variable "List" is the list of Workbook names that I'm using.

The code "Windows(List).Activate" is what I'm using to get the data from the other workbooks, but this only works when the workbooks are open

What can I replace this code with?


It may not even be possible, but I figured if anyone can figure it out, it's the geniuses on here!

Thanks,
Andre


- I'm using Windows XP and Office 2003
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

vandana.kene

New Member
Joined
Mar 18, 2008
Messages
6
Here is the code which will help you out .
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
-- You have to save your source/input files in to the data folder; this code will open the particular input file get the information then close automatically
<o:p> </o:p>
<o:p> </o:p>
Workbooks.Open Filename:="C:\Documents and Settings\username\data folder\SSSSSSS1.xls"<o:p></o:p>
<o:p> </o:p>
Windows("Master.xls").Activate<o:p></o:p>
Windows("SSSSSSS.xls ").Activate<o:p></o:p>
Range("E30").Select<o:p></o:p>

Paste here your code (for fetching the information)
.
.
.
<o:p> </o:p>
Windows("SSSSSSS1.xls").Activate<o:p></o:p>
Range("A1").Select<o:p></o:p>
ActiveWindow.Close
‘Then for second sheet

Workbooks.Open Filename:="C:\Documents and Settings\username\data folder\SSSSSSS2.xls"
<o:p> </o:p>
copy the same for the remaining ones.
 

Panthers0027

Board Regular
Joined
Apr 13, 2009
Messages
89
I guess that would work. I feel that if there were a way to not have to open the sheets, that the code would run much faster.
 

Panthers0027

Board Regular
Joined
Apr 13, 2009
Messages
89
I checked the link, but the code is very confusing to me. I just don't think am at that level yet... (though I've come a long way not knowing any of this a few months ago!)

Thanks for your help.
Andre
 

wilnexpc

New Member
Joined
Dec 27, 2011
Messages
5
Take a look at Ole Erlandsen's ADO/DAO page.

HTH,
I checked the ADO/DAO page, the ADO codes I found there work only if the worksheet is the first worksheet, or the range to be copied is defined. None of these things are true for me.
My worksheets are consistently named and my range is always the same (C10:X24), and I will need to deal with hundreds of workbooks, thus opening them will take too much time... so any help "getting data from closed workbook" where the worksheets can be anywhere in the workbook, and the range not being defined?

Thanks
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Why not just explicity reference the sheet name and range?

E.G. Sheets("Sheet1").Range("C10:X24").
 

wilnexpc

New Member
Joined
Dec 27, 2011
Messages
5
Why not just explicity reference the sheet name and range?

E.G. Sheets("Sheet1").Range("C10:X24").
Thanks a lot Smitty, I really appreciate your answer to this and answering that quickly.
I am fairly new with vba (let alone using SQL connection). I am not sure the procedure allows for me to put the sheet name in there.

This is what the codes look like with the following preceding comment:

'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", 'ActiveCell, False
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", 'Range ("B3"), True

Code:
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _ TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile' SourceRange must include the range headers'
Dim dbConnection As ADODB.Connection, rs As ADODB.RecordsetDim dbConnectionString As StringDim TargetCell As Range, i As Integer dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _ "ReadOnly=1;DBQ=" & SourceFile 
  Set dbConnection = New ADODB.Connection 
  On Error GoTo InvalidInput 
 
  dbConnection.Open dbConnectionString 
  ' open the database connection 
  Set rs = dbConnection.Execute("[" & SourceRange & "]") 
  Set TargetCell = TargetRange.Cells(1, 1) 
   
    If IncludeFieldNames Then 
      For i = 0 To rs.Fields.Count - 1 
        TargetCell.Offset(0, i).Formula = rs.Fields(i).Name 
      Next i 
      Set TargetCell = TargetCell.Offset(1, 0) 
    End If 
 
    TargetCell.CopyFromRecordset rs 
    rs.Close dbConnection.Close 
    ' close the database connection 
    
    Set TargetCell = Nothing 
    Set rs = Nothing 
    Set dbConnection = Nothing 
 
   On Error GoTo 0 
   Exit Sub
 
   InvalidInput: MsgBox "The source file or source range is invalid!", _ vbExclamation, "Get data from closed workbook"
 
End Sub
</PRE>

Again, thanks much!
</PRE>
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,090,239
Messages
5,413,250
Members
403,468
Latest member
GRamos

This Week's Hot Topics

Top