Get data from a list of CLOSED workbooks using VBA

[Panthers0027]

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:

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]

Here is the code which will help you out .
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
-- 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> </o>
<o> </o>
Workbooks.Open Filename:="C:\Documents and Settings\username\data folder\SSSSSSS1.xls"<o></o>
<o> </o>
Windows("Master.xls").Activate<o></o>
Windows("SSSSSSS.xls ").Activate<o></o>
Range("E30").Select<o></o>

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

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

 

[Panthers0027]

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.

 

[Smitty]

Take a look at Ole Erlandsen's ADO/DAO page.

HTH,

 

[Panthers0027]

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]

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]

Why not just explicity reference the sheet name and range?

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

 

[wilnexpc]

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

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>