CODEWITHSUNDEEP
excelvbarow
MrPatterns
MrPatterns

Reputation: 4434

Is it possible to automate the adding of 1 row to approx. 50 excel files in a folder?

I have a folder full of Excel files (ending in .xlsx). There are about 50 of them. Is it possible to iterate through each excel file in the folder and insert a blank row in row 1?

I'd hate to have to do this manually. Also, I'd like to add the word "NEW" in Cell A1 for all 50 files. Is this possible with VBA?

Upvotes: 0

Views: 258

Answers (2)

whytheq
whytheq

Reputation: 35597

You can create a control workbook in Excel - this will have your macro in it.

  • One possibility is to use the Dir function within a Do loop that will run through all the files in a particular directory
  • You can add in wildcards and searches for specific bits of text in the file names to check if they are the files you want to work on.
  • If the routine finds a file that satisfies your criteria it will use straight to open the file, make the changes and then close & save it.

Here's one example of looping though files in a target directory example

Here's a better example

  • Another possibility is to write a macro using the FileSystemObject. Example here.

Here's some code I use regularly that uses the fso to loop through files in a folder checking & deleting if any are old, and have a particular string in their title:

        Function DeleteFilesNotCreatedToday(myTargetFolder As String)

        Dim myFolder
        Dim myFile
        Dim YesterdayDate As Date

        YesterdayDate = Date
        Set myFolder = CreateObject("Scripting.FileSystemObject").GetFolder(myMMTargetFolder).Files
                For Each myFile In myFolder
                       If Left(myFile.Name, 13) = "Daily Summary" Then
                              If DateDiff("s", myFile.datelastmodified, YesterdayDate) < 0 Then
                                   Else
                                       On Error Resume Next
                                       myFile.Delete
                                       On Error GoTo 0
                                   End If
                       End If
                 Next
        Set myFolder = Nothing

        End Function            'DeleteFilesNotCreatedToday

Upvotes: 2

creativetechnologist
creativetechnologist

Reputation: 1462

You can use a VBA macro to do this, get it working in one of your files then use the Workbooks.Open function to open your other files and run the same code.

Upvotes: 1

Related Questions