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
 

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:

Forum statistics

Threads
1,084,752
Messages
5,379,649
Members
401,618
Latest member
Tapper1326

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top