Excel/filename to range

Advertisement


Question
Hey tom hope you are well.

i have a worksheet with rows

Im trying to insert a new column to the left and then insert the filename into the new column for all rows of the sheet

I have had a go at the code and the code below inserts a new column but only populate the first row with the filename.

I was wondering if you would be able to advise where i am going wrong with the inserting the filename into the new column

cheers

RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim i As Long


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:   esty"
         .FileType = msoFileTypeExcelWorkbooks
         'Optional filter with wildcard
         '.Filename = "Book*.xls"
         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)

' insert column
Columns(1).Insert

'insert filename in range

Dim rRange As Range
Dim rCell As Range
Set rRange = Range("A1")

  For Each rCell In rRange
      rCell.Copy
    Range("A:A").End(xlDown).Offset(1, 0).Select
  .Value = wbResults.Name
   
  Next rCell
         
   wbResults.Close SaveChanges:=True
     Next lCount
         End If
       End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Answer
Paul,

I assume your a looping through all workbooks in a folder, opening the workbook, in the activesheet for that workbook, insert a new column A and then put the name of that workbook next to each entry in column B from B1 down to the last filled cell in column B.

Once you establish the range in column B, then you can offset to the left one column and fill all the cells in column A next to that range with one command (no need to loop).

That is the change I have made to your code.  You were trying to fill column A, but column A was empty.

Note that the filesearch method has been deprecated starting in Excel 2007 or Excel 2003 (can't remember which version).


RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim i As Long


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: esty"
 .FileType = msoFileTypeExcelWorkbooks
 'Optional filter with wildcard
 '.Filename = "Book*.xls"
 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)

    ' insert column
    Columns(1).Insert

    'insert filename in range

    Dim rRange As Range
    Set rRange = Range("B1", cells(cells.rows,2).End(xlup)).offset(0,-1)
    rRange.Value = wbResults.Name

    wbResults.Close SaveChanges:=True
   Next lCount
 End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

I would test this on a small folder with just two workbooks just to make sure it does what you want.

Let me know if this doesn't work for you - I couldn't test it because I don't have an older version of excel (with filesearch) installed.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.