Results 1 to 3 of 3

Object required error

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Pakistan
    MS-Off Ver
    Excel 2013
    Posts
    256

    Lightbulb Object required error

    Dear ALL

    I trying to make a code that go through each excel file in folder and change background to no fill,the code runs but after some times having error object required

    Public Sub btnGetSheets_Click()
        Call GetSheets("c:\source")
    End Sub
        
    Public Sub GetSheets(ByVal strPath As String)
    On Error GoTo Proc_Error
        
    'MUST set reference to Windows Script Host Object Model in the project to use this code!
        
      Dim objFS       As FileSystemObject
      Dim objFolder     As Folder
      Dim objFile       As File
      Dim wksCurr       As Worksheet
      Dim wksTest       As Worksheet
        
      Dim strFilePath     As String
      Dim strFile       As String
      Dim strValidFile    As String
      Dim intSheet       As Integer
        
      Set objFS = New FileSystemObject
      Set objFolder = objFS.GetFolder(strPath)
      Application.EnableEvents = False
    '
    '  Look at each file in the folder
    '
      For Each objFile In objFolder.Files
      strPath = objFile.Path
      strFile = objFile.Name
          
      If (Right(strFile, 3) = "xls" _
      Or Right(strFile, 4) = "xlsx" _
      Or Right(strFile, 4) = "xlsm") Then
    '
    '  Update the name and date entries for the current worksheet
    '
        Application.Workbooks.Open strPath, False, False
      
    
        
    
        With Application
            .FindFormat.Clear
            .FindFormat.Interior.ColorIndex = 2
            .ReplaceFormat.Clear
            .ReplaceFormat.Interior.ColorIndex = xlNone
        End With
    
        For Each wksCurr In Workbooks(strFile).Worksheets
        For Each wksTest In Workbooks(strFile).Worksheets
            wks.Cells.Replace What:="", Replacement:="", _
                              LookAt:=xlWhole, MatchCase:=True, _
                              SearchFormat:=True, ReplaceFormat:=True
        
           Exit For
            'End If
           Next wksTest
           wksCurr.Name = "Sheet" & wksCurr.Index
        Next wksCurr
        Application.Workbooks(strFile).Close True
      End If
        
      Next objFile
        
    Proc_Exit:
        
      Application.EnableEvents = True
      Set objFile = Nothing
      Set objFolder = Nothing
      Set objFS = Nothing
        
      Exit Sub
        
    Proc_Error:
          
      Select Case Err
      Case Else
        MsgBox "Error " & CStr(Err) & ": " & Err.Description
      '  Resume Next_Sheet
      End Select
        
    End Sub
    Thanks
    farrukh
    Last edited by farrukh; 05-08-2011 at 04:20 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1