Extracting data from mutliple closed workbooks

[Dave]

Hi All:

I'm trying to setup the following code (courtesy of Dave Hawley) to pull data from several closed workbooks. It's works fine for one workbook, but when I try to pull data from more than one workbook using wild cards and the day of the week (i.e. *monday*.xls) it flips out and dies. Any thoughts?

Also (while I'm at it) can I use a command button on another sheet to set the day of the week (Dim Day as string, Day=Monday) and carry this variable foward to use in the extraction code so I don't have to have the code in This workbook 7 times (one for each day of the week)?

Thanks

Dave

----Start Code----

Private Sub Extract_Data()

'''''''''''''''''''''''''''''''
'Written By OzGrid Business Applications
'www.ozgrid.com

'Pulls in all data from sheet1 of a closed workbook.
''''''''''''''''''''''''''''''''

Dim AreaAddress As String


'Clear sheet ready for new data

Sheet3.UsedRange.Clear

'Reference the UsedRange Address of SheetX in the closed Workbook.

Sheets("Extracted_Data").Cells(1, 1) = "= 'S:MyNetworkMyFolder" & "[Dave Monday Dayshift.xls]Data_Address'!RC"

'Pass the area Address to a String

AreaAddress = Sheet3.Cells(1, 1)

With Sheets("Extracted_Data").Range(AreaAddress)

'If the cell in Sheet1 of the closed workbook is not empty the pull in it's content, else put in an Error.

.FormulaR1C1 = "=IF('S:MyNetworkMyFolder" & "[Dave Monday Dayshift.xls]Data_Extraction'!RC="""",NA(),'S:MyNetworkMyFolder[Dave Monday Dayshift.xls]Data_Extraction'!RC)"

'Delete all Error cells

On Error Resume Next

.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

On Error GoTo 0

'Change all formulas to Values only

.Value = .Value

Sheets("Extracted_Data").UsedRange.Cut Destination:=Sheets("Production_Sheet").Range("A65536").End(xlUp).Offset(5, 0)

End With

End Sub

----End Code----

 

[BabyTiger]

Hi Dave,

The attached codes will extract a list of files with names containing 'Monday', and ends with '.xls'. And puts all the files that meets the criteria into an array.

Then I suppose you can use that array to extract the data you wanted from each workbook.

Sub Testing()
Const Temp_Dir = "S:MyNetworkMyFolder"
Dim Count As Integer, iFileName(50) As String, FF As String
VBA.Reset
FF = Dir(Temp_Dir, 7)
Do While FF <> ""
If Right(FF, 4) = ".xls" And InStr(FF, "Monday") Then
Count = Count + 1
iFileName(Count) = FF
End If
FF = Dir
Loop
For i = 1 To 50
MsgBox (iFileName(i))
Next i
End Sub

------
HTH

 

[simonf]

Is there a way to specify which sheet in the workbook to extract from?