Announcement

Collapse
No announcement yet.

File System Object

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • File System Object



    Hi Experts

    I am trying to write some code so that it will loop through all the files in the folder and do certain things is the file name is like...

    My Code is

    Code:
    Dim wb as workbook
    Dim cmMyPath As String
    Dim cmMyFile As String
    Dim MyExt As String
    Dim file As Variant
    Dim fso As Scripting.FileSystemObject
    Dim fsopath As Scripting.Folder
    
    MyExt = "*.xlsx"
    MyPath = Sheets("Sheet1").Range("I3") '"C:\Users\Owner\Dropbox\Accounts Miqlat\"
    cmMyPath = Sheets("Sheet1").Range("I3") & "Maureen\"
    MyFile = "Cash Management v1.4.xltx"
    cmMyFile = Dir(cmMyPath & MyExt)
    Set fsopath = fso.GetFolder(cmMyPath) - 'Error Here "Object Variable or with block variable not set"
    
     For Each file In fsopath.Files
        If cmMyFile Like "*Cash Management*" Then
            Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)
           '  DO stuff
           ' close
        End If
     Next file
    I am sure I am missing something, but if someone could point the way, I would be grateful.



  • #2
    Hello,

    If you do not mind ... a few remarks ...

    1. When posting a macro ... please post the whole macro ..

    2. Within your code ... MyExt = "*.xlsx" ... but why since you are using MyFile = "Cash Management v1.4.xltx"

    3. What is exactly stored in cell I3 .... Sheets("Sheet1").Range("I3") ... potential problem between MyPath ( not declared ... ) and cmMyPath ...

    Hope this will help ... a little ...
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Hi Carim Thanks for your comments, and no I don't mind, we are all learning, and as I am self taught in VB, even more so.

      Your 3rd point Cell I3 is looking at the folder location - it reads:
      Code:
      =CONCATENATE("C:\Users\",I2,"\Dropbox\Accounts Miqlat\")
      I2 being the username.
      This line works, as I use it in other codes within this project.

      Your 2nd Point, I refer to MyFile = "Cash Management v1.4.xltx", is what I need to do on the xlsx file I also do within this xltx file - Please note that that the xltx file is in folder refered to in I3, and the xlsx files will be in a subfolder "Maureen).

      The xltx and the mentioned xlsx workbooks both have 2 sheets ("Expenses" and "Validate") the template become and xlsx workbook, when completed.

      As requested, please find the entire code.

      Please note that when I posted the original yesterday, I noticed I had forgotten to declare fso as an object.

      Code:
      Private Sub CommandButton1_Click()
      
      Dim Sht As Worksheet
      Dim tbl As ListObjects
      Dim F As String
      Dim n As Integer
      Dim wb As Workbook
      Dim MyPath As String
      Dim MyFile As String
      Dim cmMyPath As String
      Dim cmMyFile As String
      Dim MyExt As String
      Dim count As Integer
      Dim file As Variant
      Dim myobj As Object
      Dim MySource As Object
      Dim fso As Object
      Dim fsopath As Scripting.Folder
      
      MyExt = "*.xlsx"
      MyPath = Sheets("Sheet1").Range("I3") '"C:\Users\Owner\Dropbox\Accounts Miqlat\"
      cmMyPath = Sheets("Sheet1").Range("I3") & "Maureen\"
      MyFile = "Cash Management v1.4.xltx"
      cmMyFile = Dir(cmMyPath & MyExt)
      'MySource = myobj.GetFolder(cmMyPath)
      Set fso = FileSystemObject
      Set fsopath = fso.GetFolder(cmMyPath)
      
      
      '**** Find Data and Change ****
      If OptionButton1 = False And OptionButton2 = False Then
      MsgBox "Select Option"
      Frame1.SetFocus
      Exit Sub
      End If
      
      'F is looking at the correct combobox
      'n is looking for the correct column
      If OptionButton1 = True Then
      F = ComboBox1.Value
      n = 5
      Else
      F = ComboBox2.Value
      n = 6
      End If
      
      ' ***** This line of code down to "Next File" will go to the bottom when sorted ****
      'Also when I do the 2 sheets as an array, it only makes the changes within the sheet "Expenses" - even though "Validate" requires the change too
      For Each file In fsopath.Files
      If cmMyFile Like "*Cash Management*" Then
      Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)
      With Worksheets("Expenses") 'ActiveWorkbook.Worksheets(Array("Validate", "Expenses"))
      .Cells.Replace what:=F, replacement:=TextBox1.Value, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
      SearchFormat:=False, ReplaceFormat:=False
      End With
      With Worksheets("Validate")
      .Cells.Replace what:=F, replacement:=TextBox1.Value, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
      SearchFormat:=False, ReplaceFormat:=False
      End With
      ActiveWorkbook.Save
      ActiveWorkbook.Close
      
      
      End If
      Next file
      
      Exit Sub
      
      'This is the main workbook where the VB is.
      For Each Sht In Worksheets(Array("Centre", "Consolidated", "Validate1"))
      Sht.Cells.Replace what:=F, replacement:=TextBox1.Value, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
      SearchFormat:=False, ReplaceFormat:=False
      Next Sht
      'Check Tables and Column 5 or 6 and replace
      For Each Sht In Worksheets(Array("Dollar", "KHCCash", "KHCSavings", "KHCConCash", "KHCConstruction", "KHCChecking", _
      "THCCash", "THCSavings", "THCConCash", "THCConstruction", "THCChecking", _
      "AdminCash", "AdminSavings", "AdminConCash", "AdminConstruction", "AdminChecking"))
      Sht.Columns(n).Replace what:=F, replacement:=TextBox1.Value, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
      SearchFormat:=False, ReplaceFormat:=False
      
      Next Sht
      
      'Open CashManagement Template and change in the Validate sheet
      'Only the Validate sheet will have any changes required.
      
      Workbooks.Open Filename:=MyPath & MyFile, Editable:=True
      With ActiveWorkbook.Sheets("Validate")
      .Cells.Replace what:=F, replacement:=TextBox1.Value, _
      LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
      SearchFormat:=False, ReplaceFormat:=False
      ActiveWorkbook.Save
      ActiveWorkbook.Close
      End With
      
      'Open any UnImported Cash Management sheets and change
      'The code mentioned above will go here
      
      Unload Me
      End Sub
      Any pointers and help would be greatly appreciated.

      Comment


      • #4
        Forgo to ask you ..

        Within your VBA editor ... did you add reference to Microsoft Scripting Runtime: .. ?

        From Menu : Tools > References > Microsoft Scripting Runtime
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Hi Carim, Yes I have done that. 1st thing I checked. However, I can now confirm by referencing fso like this:
          Code:
          Dim fso As Object
          Dim fsopath As Scripting.Folder
          
          Set fso = New FileSystemObject
          Set fsopath = fso.GetFolder(cmMyPath)
          I now do not get any errors, but it keeps opening up the same file, it is not scrolling through to the next.

          so
          Code:
          cmMyFile = Dir(cmMyPath & MyExt)
          appears to be always the 1st file in the sub folder Maureen. (for testing purposes there are 4 files, and only 2 are like "Cash Management"

          Do I need to reference fso.getfiles or something?

          Thanks

          Comment


          • #6
            Hello again,

            You should simplify your life and have building blocks fully operational ... before merging everything into a single UserForm command button ...

            Have a go with following portion of your code ...

            Code:
            Set fso = New Scripting.FileSystemObject
            Set fsopath = fso.GetFolder(cmMyPath)
            
                For Each file In fsopath.Files
                  If file.Name Like "*Cash Management*" Then
                    Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)
                      With Worksheets("Expenses")
                        .Cells.Replace what:=F, replacement:=TextBox1.Value, _
                        LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
                        SearchFormat:=False, ReplaceFormat:=False
                      End With
                      With Worksheets("Validate")
                        .Cells.Replace what:=F, replacement:=TextBox1.Value, _
                        LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
                        SearchFormat:=False, ReplaceFormat:=False
                      End With
                    ActiveWorkbook.Save
                    ActiveWorkbook.Close
                  End If
                Next file
            Exit Sub
            Hope this will help
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Thanks Carim

              I tested this, as you suggested in a module of its own, and first instead of opening workbook etc, I called a msgbox to give the file name. This worked, however...

              When I implemented the line

              Code:
                Set wb = Workbooks.Open(Filename:=cmMyPath & cmMyFile)
              again it opens the 1st file (Cash Management FEBRUARY 2019 TEST) on each next file.

              Almost there, if you could suggest the issue I really would be grateful.

              Thanks

              Comment


              • #8
                Hello,

                Have you tried to replace cmMyFile by the variable file ...
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9
                  Carim

                  Thank you for your comment, I changed
                  Code:
                   Dim cmMyFile as String
                  to
                  Code:
                   Dim cmyMyFile as variant
                  that did not change the outcome, so maybe I misunderstood.

                  Look forward to the explanation that will solve this for me.

                  Comment


                  • #10
                    Code:
                    For Each file In fsopath.Files
                          If file.Name Like "*Cash Management*" Then
                            Set wb = Workbooks.Open(Filename:=cmMyPath & file)
                              With Worksheets("Expenses")
                                .Cells.Replace what:=F, replacement:=TextBox1.Value, _
                                LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
                                SearchFormat:=False, ReplaceFormat:=False
                              End With
                              With Worksheets("Validate")
                                .Cells.Replace what:=F, replacement:=TextBox1.Value, _
                                LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, _
                                SearchFormat:=False, ReplaceFormat:=False
                              End With
                            ActiveWorkbook.Save
                            ActiveWorkbook.Close
                          End If
                      Next file
                    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                    Comment


                    • #11
                      HI Carim

                      Thankyou so very much for your patience and helpful guidance.

                      It worked, but by changing the line
                      Code:
                      Set wb = Workbooks.Open(Filename:=cmMyPath & file)
                      to
                      Code:
                       Set wb = Workbooks.Open(Filename:=file)
                      ,

                      as the first line now had the path twice (I guess because of fsopath and cmMyPath)?

                      Thanks again for aiding my learning.

                      Comment


                      • #12
                        Glad you could fix your problem ..

                        Thanks for your Thanks ...
                        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                        Comment


                        • #13
                          Sometimes ones thinking gets stuck in a loop, and you miss the obvious.

                          Comment


                          • #14


                            Originally posted by mar050703 View Post
                            Sometimes ones thinking gets stuck in a loop, and you miss the obvious.
                            Do you mean the obvious ...of the Loop ...???
                            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                            Comment

                            Working...
                            X