Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Extracting data from mutliple closed workbooks

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Tennessee
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •