Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

copy text to multiple excel files

Posted on 2014-02-19
7
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 23

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 23

Expert Comment

by:Ejgil Hedegaard
ID: 39874482
At what line is the error?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

597 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