Extract Dynamic Data From Closed Workbook

thunder · May 11th 2007

Hi there, i have this code that could extract out Data from many closed workbook. I am extracting data from 2-3 thousand files and the Data in those files are rather dynamically positioned.

For e.g. My vba codings will extract data from cell B256 of the closed workbook. Sometimes, the data might not start from that cell which my vba would still extract that non-relevant data.

Is there anyway i can modify the codings to search for the required data and extract instead of extracting it dumbly?

Code

Sub getINFO()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Dim rCell As Range
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set wbCodeBook = ThisWorkbook
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
   .LookIn = Range("H4").Value
        .Filename = Range("H5").Value
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 
                 
                Set rCell = wbCodeBook.Sheets(1).Range("A50000").End(xlUp).Offset(1, 0)
                
                
                rCell.FormulaR1C1 = wbResults.Sheets(1).Range("C256")
                rCell.Offset(0, 1).FormulaR1C1 = wbResults.Sheets(1).Range("B256")
                rCell.Offset(1, 0).FormulaR1C1 = wbResults.Sheets(1).Range("C257")
                rCell.Offset(1, 1).FormulaR1C1 = wbResults.Sheets(1).Range("B257")
                rCell.Offset(2, 0).FormulaR1C1 = wbResults.Sheets(1).Range("C258")
                rCell.Offset(2, 1).FormulaR1C1 = wbResults.Sheets(1).Range("B258")
                         
                wbResults.Close SaveChanges:=True
                 
            Next lCount
            
        End If
    End With
    ActiveWorkbook.Save
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
   
End Sub

Display More

filo65 · May 11th 2007

Re: Extracting Dynamic Data From Closed Workbook

is/can the starting cell be identified with a particular sign ( header, etc. ) ?

could you post a couple of examples ( in the same workbook, different worksheets ) to see how this appears?

filippo

**iwrk4dedpr** · May 11th 2007

Re: Extracting Dynamic Data From Closed Workbook

So you are opening each of the 2 - 3 thousand xl files just to extract anywhere from 1 to 5 pieces of information???

Sounds like a lot of work to have to do. In the workbooks are the sheet names identical???

I would instead of opening each workbook. Create a worksheet in which you create the 5 links and then modify the linked formula to identify the particular workbook.

As far a picking the right cell to extract. You need to test each cell and see if it contains the value that you are looking for. You don't specify how to identify the cell that has the value the you want to extract. I'm going to assume that only 1 of the identified cells will contain data and the others would be blank. IN that case you would just look for the non blank cell....

More information is needed to give you further help.. Maybe a posting of a target workbook.

thunder · May 11th 2007

Re: Extracting Dynamic Data From Closed Workbook

This is one of the example files that i will be extracting data from. This file is actually in slk format.

There is a unique header for the particular data that i am suppose to extract. However, it is often not in the right cell as in the Data might be located at cell B250 in one file and the next file it would be in B255 and B350. They will always be in the same column but different row. The headers are unique.

this is because this file is consolidated from someone else over don't know how many god donkey years and its inconsistent in row but not for column.

thunder · May 11th 2007

Re: Extracting Dynamic Data From Closed Workbook

iwrk4dedpr:

The file contains many other unwanted information and i just want to filter out those information that i want and put it in an excel sheet so that i can sort it out to do some statistic for it. Actually i need to extract out at least 40 information that is in order of a list.

Thank you filo65 and iwrk4dedpr for the help.

Dave Hawley · May 11th 2007

Re: Extracting Dynamic Data From Closed Workbook

See if this helps;

Get Data From Closed Excel Workbooks

thunder · May 12th 2007

Re: Extracting Dynamic Data From Closed Workbook

Quote from Dave Hawley

See if this helps;

Get Data From Closed Excel Workbooks

Hi Dave, Thanks for the site but its not helping as
1) The data is generated by server
2) every data generated is hell lot of info
3) i can't extract all the info because all that will cause me to not have enough rows for the data
4) i need specific data and not all the data so that i can compile a statistic from the specific data collected and not redundant data.

Thanks & Rgds,
thunder

thunder · May 14th 2007

Re: Extracting Dynamic Data From Closed Workbook

bump

filo65 · May 14th 2007

Re: Extract Dynamic Data From Closed Workbook

try

Code

Sub look_for_range()
  Dim begArea As Range, endArea As Range
  Range("A1").Select
  Set begArea = Columns(1).Find(What:="URLs", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(3, 1)
  Set endArea = begArea.End(xlDown).Offset(0, 1)
  
  Range(begArea.Address & ":" & endArea.Address).Select
End Sub

filippo

thunder · May 16th 2007

Re: Extract Dynamic Data From Closed Workbook

Code

Sub testing123()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Dim rCell, selectedcells As Range
    Dim begArea As Range, endArea As Range
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set wbCodeBook = ThisWorkbook
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
   .LookIn = "C:\test"
          .Filename = "Traffic*.slk"
        If .Execute > 0 Then 
            For lCount = 1 To .FoundFiles.Count 
                 
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 
               
                Range("A1").Select
                Set begArea = Columns(1).Find(What:="User Agents (top 100)", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(3, 1)
                Set endArea = begArea.End(xlDown).Offset(0, 1)
                selectedcells = Range(begArea.Address & ":" & endArea.Address).Select.Copy


                Set rCell = wbCodeBook.Sheets(1).Range("A50000").End(xlUp).Offset(1, 0)
                       
                rCell.FormulaR1C1 = selectedcells       
 
                wbResults.Close SaveChanges:=True
                 
            Next lCount
            
        End If
    End With
    ActiveWorkbook.Save
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
   
End Sub

Display More

hmmm... this also doesn't seem to work ... filo thanks for the coding...

filo65 · May 16th 2007

Re: Extract Dynamic Data From Closed Workbook

thunder,

instead of

Quote

selectedcells = Range(begArea.Address & ":" & endArea.Address).Select.Copy

try

Code

selectedcells = Range(begArea.Address & ":" & endArea.Address).Copy

where it doesn't work?, do you get an error message? which one?

filippo

thunder · May 16th 2007

Re: Extract Dynamic Data From Closed Workbook

Hmm... i do not get any error.. neither do i get any results...

i went to do a breakpoint and i find that it does open the closed workbook but it just doesn't select the selected info that i want and copy into the workbook that is suppose to consolidate all the info

thunder · May 17th 2007

Re: Extract Dynamic Data From Closed Workbook

bump

thunder · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

bump

shg · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

In this line, rCell is declared (implicitly) as a variant:

Code

Dim rCell, selectedcells As Range

What is this line of code trying to do?

Code

selectedcells = Range(begArea.Address & ":" & endArea.Address).Select.Copy

And this one?

Code

rCell.FormulaR1C1 = selectedcells

thunder · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

This line is trying to copy those cells from closed workbook

Code

selectedcells = Range(begArea.Address & ":" & endArea.Address).Select.Copy

and paste it in the open workbook

Code

rCell.FormulaR1C1 = selectedcells

thunder · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

Code

Sub testing123()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    Dim rCell, selectedcells As Range
    Dim begArea As Range, endArea As Range
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set wbCodeBook = ThisWorkbook
     
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\test"
        .Filename = "Traffic*.slk"
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 
                wbResults.Activate
                Range("A1").Select
                Set begArea = Columns(1).Find(What:="User Agents (top 100)", After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Offset(3, 1)
                Set endArea = begArea.End(xlDown).Offset(0, 1)
                Range(begArea.Address & ":" & endArea.Address).Copy
                
                
                wbCodeBook.Activate
                Set rCell = wbCodeBook.Sheets(1).Range("A50000").End(xlUp).Offset(1, 0)
                               
                rCell.FormulaR1C1 = Range(begArea.Address & ":" & endArea.Address).pastespecial
                               
                wbResults.Close SaveChanges:=True
                 
            Next lCount
            
        End If
    End With
    ActiveWorkbook.Save
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
   
End Sub

Display More

I've changed the codings and now after the pastespecial the only thing that appear on my excel file is TRUE. Don't know what is happening

**Reafidy** · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

Thunder,

Please do not use quote tags for code. I have fixed a couple of your posts for you.

thunder · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

okay.. understood ... and sorry about that ...

filo65 · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

I suggest you post two workbooks, the target and the source one with some consistent data. In the previous attachment it was not included what your were looking after

filippo

Extract Dynamic Data From Closed Workbook

Participate now!

Share