Excel Repeat Data Extraction

March 12, 2009 at 09:36:58
Specs: Windows XP
I need to know how to copy the same specific range of data from multiple individual Excel files to one single Excel file.

For example, the data range of A28:J32 needs to be imported from files Data1, Data2, Data3, etc. into file Total1.

Individual copying is not an option due to number of Data files. Linking Total1 to the data files, locking the cell references in each cell and then copying helps, but still leaves the business of updating links for each file imported, and it does not iteratively update the references back to the files from where the data is linked.

That is, in file Total1, the formulae within cells A7, A13, A19, A25, A31, etc. all read ='C:\Datapath\[Data1.xls]Sheet1'!$A$28, even though the data from A13 is from file Data2, A19 is from Data 3, A25 is from Data 4, A31 is from Data5, etc.

A macro would be ideal, but need method for it to iteratively go through my data files.

See More: Excel Repeat Data Extraction

Report •

March 12, 2009 at 10:35:36
Without getting down into the bowels of your requirements, I can offer the following code to build the formulae and put them in cells. The part I can't answer based on your post is how to tell the code which formula goes where. In this example, the formulae will be placed in A1:A10. i.e. A1 get the formula for Data1, A2 get Data2, etc.

Sub DataLoop()
  For DataSht = 1 To 10
   Range("A" & DataSht).Formula = "='C:\Datapath\[Data" & DataSht & ".xls]Sheet1'!$A$28"
End Sub

Report •

March 12, 2009 at 10:46:42
gets :)

Report •

March 12, 2009 at 11:17:06
This is an interesting solution. You have really gotten down to the root of my problem, which is good, because it seemed all hope was lost.

Let me pose this question as a follow-up, focusing on the [Data" & DataSht & ".xls] section of the code you provided. Will the code continue to operate correctly if the workbooks to be accessed are in the name format Data12009, Data22009, Data32009, Data42009, and so forth? In other words, if altered to [Data" & DataSht & "2009.xls], will the code still work? I ask because this is how my individual workbook filenames are structured. Thanks!

Report •

Related Solutions

March 12, 2009 at 13:15:52
As long as there is something to loop on, my suggestion will work - with the modification you suggested. All you are doing is building a text string with some portion of it being a variable. As long as the resulting text string matches what you would type into a cell, the formula should work.

There are, of course, other ways to accomplish your goal, such as looping through the actual directory and pulling the actual file name to build the formula. In some cases, that is the better solution because the filenames don't need to be named in any type of consecutive order.

For example, this code will loop through a given directory and build your formula based on the name of each file in the directory. Obviously I can't test this for your directory or filenames, but the concept works - I use something similiar all the time.

Sub UseFileName()
Dim sfol As String
Dim SearchString As String
Dim MyFileName As String
Dim DataSht As String
'Define Source Folders - make this yours...
  sfol = "C:\Documents and Settings\User\Desktop\Data Folder\"
'Set up Seach
   With Application.FileSearch
      .LookIn = sfol
      .FileType = msoFileTypeAllFiles
     If .Execute > 0 Then
'Loop Through Files
       For i = 1 To .FoundFiles.Count
          SearchString = "*Data*.*"
'Get Full Filename
          MyFileName = Dir$(sfol & SearchString, vbNormal)
'Strip off extension (.xls) from Filename
          DataSht = Left(MyFileName, Len(MyFileName) - 4)
'Put Formula in Column A
          MyRow = MyRow + 1
          Range("A" & MyRow).Formula = "='C:\Datapath\[" & DataSht & ".xls]Sheet1'!$A$28"
       Next i
     End If
   End With
End Sub

Report •

Ask Question