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----
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----