Results 1 to 7 of 7

Thread: Modify 310 files within one folder location

  1. #1

    Modify 310 files within one folder location



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to create a script which will locate all of the Excel files in one folder location on the network, then proceed to open the file, change any reference to '2014' to the new year of '2015', save the file and then close file, and open the next file, etc...etc....etc...

    I have the following code thus far, but I am having either an issue with Setting the file name to open, or saving the file. This is the code
    Code:
    Dim pathName
    pathName = "\\SERVER\FOLDER\FOLDER\FOLDER\2015 attendance\"
    If Right(pathName, 1) <> "\" Then pathName = pathName + "\"
    
    
    Dim fileExtension
    fileExtension = "*.xls"
    
    
    Dim fileName
    fileName = DIR(pathName & fileExtension)
    
    
    Dim objExcel
    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = False
    objExcel.Visible = True   'Set to False for Production runs; True for testing
    
    
    
    
    Do While fileName <> " "
    
    
        Dim wb	
        wb = objExcel.Workbooks.Open(pathName & fileName)
    	
        Application.ScreenUpdating = False
    
    
        ' Clear the Contents of these fields first
        Range("B19:AF30").Select
        Selection.ClearContents
        Range("A40:AF44").Select
        Selection.ClearContents
    	
        'Find and Replace all instances of 2014 - both in formulas and string data in fields.
        '  NOT WORKING at all.... 1/2/15
        '  GETTING OPENING AND SAVING TO WORK FIRST!!!!  
    
    
    '	Cells.Find What:="2014", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
    '	    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    '        ReplaceFormat:=False
    '	ActiveCell.Replace What:="2014", Replacement:="2015", LookAt:=xlPart, _
    '        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    '        ReplaceFormat:=False
    
    
    	'Save File
    	wb.SaveChanges:=True
    	'Close File
    	wb.Close
    	
    Loop
    Application.ScreenUpdating = True
    With the above, I receive a Expected Statement on the wb.saveChanges:=True line. When I remove this line, and replace the wb.close with
    Code:
    wb.close SaveChanges = True
    I end up with a Type Mismatch: 'DIR' on set fileName step.


    The files are all located within 1 folder location and all have a different name to them and are used to track the yearly calendar time tracking of employees. Each file has multiple worksheet, and within the worksheet there are multiple formulas. Any assistance in helping me understand where my mistake is would be greatly appreciated! Thank you for taking the time to look at the code as well.

  2. #2
    Code:
    Dim wb As WorkbookDim pathName As String
    Dim fileName As String
    Dim fileExtension As String
    
    
        pathName = "\\SERVER\FOLDER\FOLDER\FOLDER\2015 attendance\"
        If Right(pathName, 1) <> "\" Then pathName = pathName + "\"
        
        fileExtension = "*.xls"
        
        fileName = Dir(pathName & fileExtension)
        Application.DisplayAlerts = False
        
        Do While fileName <> ""
        
            Set wb = Workbooks.Open(pathName & fileName)
            
            Application.ScreenUpdating = False
        
            ' Clear the Contents of these fields first
            Range("B19:AF30").ClearContents
            Range("A40:AF44").ClearContents
            
            'Find and Replace all instances of 2014 - both in formulas and string data in fields.
            '  NOT WORKING at all.... 1/2/15
            '  GETTING OPENING AND SAVING TO WORK FIRST!!!!
        
        '   Cells.Find What:="2014", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        '       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        '        ReplaceFormat:=False
        '   ActiveCell.Replace What:="2014", Replacement:="2015", LookAt:=xlPart, _
        '        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        '        ReplaceFormat:=False
    
    
            wb.Save SaveChanges:=True
            wb.Close
        Loop
        
        Application.ScreenUpdating = True

  3. #3
    I continue to receive the error message of Expected end of statement on the line "Dim wb As Workbook".
    I was able to get past this error by removing the As statement and then using a Set command (kind of like shown in my code above). I am not sure what I am doing incorrectly, but it must be something small. Am I missing something on my computer? Does the version of Excel make a difference? Also, why do I not need to open the Excel Application/Object within the code? How can it know I want Excel?

  4. #4
    You shouldn't get any of those errors, and you don't need to open another instance of Excel. That suggests that you have something more fundamental wrong. Can you post both workbooks?

  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    You will have to store the code in an Excel workbook and run it from there.

  6. #6
    I was running it as a script, not within the Workbook. I will give a try to placing it within an actual workbook itself. Thanks for the tip.

  7. #7
    If you run it as a script, the only data type is variant, so just declare the variables, don't bother with a data type. Of course, you also need to create an Excel instance from within the script.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •