Solved

copy text to multiple excel files

Posted on 2014-02-19
7
222 Views
Last Modified: 2014-02-21
Hi - I need to copy a string (e.g. disclaimer) to the bottom of 100 Excel files that are located in the same directory.  The text verbiage (e.g. "my verbiage") would go to 3 lines below the last line of text, but if that is difficult I could settle of having the verbiage go to a specific cell in all files (A65).  Is there an easy way to do this using VBA other than manually open each Excel file and paste the verbiage?  Thanks,
0
Comment
Question by:eklin
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39871858
Sure that is possible, to let VBA open the files, write the text, save the file, and close.
If sheets are protected, some extra action must be performed to handle that.

I have set it to write to A65, (the easy solution), on each sheet in the workbook.
If it is only to be written to one specific sheet that is also possible.

It is difficult to detect the last cell.
The property UsedRange often contains more rows than is actually used, due to changes in the worksheet, and charts below last row is not part of it.
ws.Range("A65") could be replaced with ws.Cells(ws.UsedRange.Rows.Count+3,1)

Copy a few files to a new folder, and test.
The program asks you to select one of the files in the folder, any will do.

Allow macros to run when opening the file.
To run the program use Alt+F11 to open the VBA editor, select Module1, and Run

Here is the code

Option Explicit

Sub InsertTextInFile()
    Dim vDirSelect As Variant, iPosition As Integer, iPositionEnd As Integer
    Dim sTextToInsert As String, sFilename As String
    Dim wb As Workbook, ws As Worksheet
    
    sTextToInsert = "WRITE DISCLAIMER TEXT HERE"
    
    vDirSelect = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*),*.xls*", Title:="Select a file in the directory")
    If vDirSelect = False Then End
    
    Application.ScreenUpdating = False
    
    For iPosition = 1 To Len(vDirSelect)
        If Mid(vDirSelect, iPosition, 1) = "\" Then
            iPositionEnd = iPosition
        End If
    Next iPosition
    vDirSelect = Left(vDirSelect, iPositionEnd)
    
    sFilename = Dir(vDirSelect + "*.xls*", vbNormal)
    Do While Len(sFilename) > 0
        Do While FileLocked(vDirSelect + sFilename)
            MsgBox "File " + sFilename + " open"
        Loop
           
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        Set wb = Application.Workbooks.Open(Filename:=vDirSelect + sFilename)
        For Each ws In wb.Worksheets
            ws.Range("A65") = sTextToInsert
        Next ws
        wb.Save
        wb.Close
        Application.DisplayAlerts = True
        Application.EnableEvents = True
            
        sFilename = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   If Err.Number <> 0 Then
      FileLocked = True
      Err.Clear
   End If
End Function

Open in new window

Insert-text-in-files.xlsm
0
 

Author Comment

by:eklin
ID: 39873513
Thanks!  I tried the code above but get an error that says: "Run time error '1004'.  Application defined or object defined error".  Any ideas why?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39874482
At what line is the error?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:eklin
ID: 39875489
I don't know.  After the error it returns to the first line of the sub.  If I go line by line F8 and shift F8, the error doesn't appear but it goes in a "infinite" loop. without completing.
0
 

Author Comment

by:eklin
ID: 39875767
It works at home fine, where I have Excel 2007 and ran using Excel 2003 files.  However, at work I have Excel 2010 and Excel 2003.  The files I tried to update were in Excel 2003 format but produced with Excel 2010.  They had conditional formatting in it and some hidden rows.  When the program ran without errors (using Excel 2003), the output files became unreadable with garbage on them.  I am guessing it must be some type of compatibility issue.
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39877757
To avoid the infinite loop I have inserted a routine that reads the file names first, and then use the list for files to have the text inserted.
There is a known problem with the VBA Dir command, it only use 3 characters as extension, but it reads the file names correctly, so if you have 2 files with the same name both as xls, and xlsx, perhaps saving one of them causes the infinite loop, reading the file again.

Tested in Excel 2003, and got an error for xlsx files, "write protected", but they are not, so perhaps the converter to xlsx in Excel 2003 and VBA does not work together.
Could be Microsoft forgot it, like the extension problem for Dir.
Tested in Excel 2007 with files saved as xls from Excel 2007, no problem.
I think it is best to run in Excel 2007 or later, I use Excel 2007.

Hidden rows should not be a problem.
VBA can write to any cell, also hidden cells, and cells on hidden worksheets.
Insert-text-in-files-A.xlsm
0
 

Author Comment

by:eklin
ID: 39878075
It fixed the problem!  Thank you!!!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question