Alternative to search and replace filename

Topic

New Reply

I would like to pull data from the same cells from multiple worksheets; e.g. from cells g2 through j2 for files arm1.xls through arm180.xls, with all worksheets having the same sheet name. Other than a laborious search and replace, row by row, is there a way to bring in the correct filename for each row?

First couple of rows have been done on attached spreadsheet.

Thanks.

Reply | Quote

Replies

J.E.,

Do you want to maintian links to the original workbooks or only copy the data values?
With this many workbooks to consolidate I would recommend coping values only.
This can be accomplished with some simple VBA to loop through the file names and copy the data to successive rows.
I’ll bang out some code when I have your answer. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

copying values only, although I’d be interested in knowing how to maintain links, should I have a small data set in the future
thanks for the help

Reply | Quote

J.E.,

Here’s some code that may do what you need.

Code:

Option Explicit

Sub Consolidate()

   Dim wbTarget    As Workbook
   Dim wbSource    As Workbook
   Dim zWkBkToOpen As String
   Dim iCellCntr   As Integer
   Dim iCntr       As Integer
   Dim lRowCntr    As Long
   Dim iShtCnt     As Integer
   Dim bNoFile     As Boolean
   
   Application.ScreenUpdating = False
   Set wbSource = ActiveWorkbook
   lRowCntr = 2   'First data to be copied to row 2
   iShtCnt = 4    'Set equal to highest numbered workbook name.
   On Error GoTo NoFileErrorHandler
   
   For iCntr = 1 To iShtCnt
   
      bNoFile = False
      
      'Note: if worksheets are in different directory preceed arm
      '     with the d:path
      
      zWkBkToOpen = CurDir() & "arm" & Format(iCntr) & ".xls"
      Set wbTarget = Workbooks.Open(zWkBkToOpen, , True)
      If Not bNoFile Then
        wbSource.Activate
        'Fill cells A:D with values from G:J
        For iCellCntr = 7 To 10
           Cells(lRowCntr, iCellCntr - 6) = wbTarget.Sheets(1).Cells(2, iCellCntr).Value
        Next iCellCntr
        wbTarget.Close
        lRowCntr = lRowCntr + 1 'Increment the row pointer
      End If  'Not bNoFile
      
   Next iCntr
   
   On Error GoTo 0   'Cancel Error Handler
   
GoTo GetOut

NoFileErrorHandler:

  If Err() = 1004 Then
    bNoFile = True
    Resume Next
  End If
  
GetOut:

End Sub   'Consolidate()

Notes:
1. This was developed with 2003 but should work with 2010 if you change the .xls reference to .xlsx also the consolidation sheet containing the macro must be of type .xlsm and stored in a “Trusted Location”.

2. Missing file names in the sequence will be ignored via the error handler.

3. I’ve attached the sample .xls file I used you can just change the type to .xlsm via Save As.

Post back with any questions. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

worked.
Thanks!

Reply | Quote