help to exclude a file

[josephgunter163]

Hey All,
I have code that loops through excel files in a file folder and edits those files. My trouble is that the code is also editing the workbook where the code is saved. Can you take a look at this code and tell me how I can exclude my workbook from the editing loop?

Private Sub CommandButton2_Click() ' third step edit work sheets
Dim Myfile As String
Dim FilePath As String
Dim Q As Long
Dim NewYear As String
Dim ThisYear As String
Dim myobject As Worksheet
Dim path As String
Dim Folderyear As String ' added
Dim Folder As String ' added

Folderyear = Range("B11") ' added
Folder = Range("B12")
NewYear = Range("B9")
ThisYear = Range("B10")

FilePath = Folder + Folderyear
Myfile = Dir(FilePath)



Do While Len(Myfile) > 0
If Myfile = "*programcleanup*" Then
Exit Sub
End If


Workbooks.Open (FilePath & Myfile)
For Q = 1 To Application.Worksheets.Count
If Myfile = "*programcleanup*" Then
Exit Sub
End If

Worksheets(Q).Activate
'Range("C13:H25").Copy
'Range("B13").PasteSpecial
'Range("C39:E42").Copy
'Range("B39").PasteSpecial
Range("A20") = NewYear ' should be A3 changed to A20 for testing
'Range("H13") = NewYear
'Range("H39") = NewYear
'Range("E39") = ThisYear
'Range("G14") = "Budget"
Next Q
ActiveWorkbook.Save
ActiveWorkbook.Close
Myfile = Dir
Loop
End Sub

 

[JLGWhiz]

See if this will do what you want

Private Sub CommandButton2_Click() ' third step edit work sheets
Dim Myfile As String
Dim FilePath As String
Dim Q As Long
Dim NewYear As String
Dim ThisYear As String
Dim myobject As Worksheet
Dim path As String
Dim Folderyear As String ' added
Dim Folder As String ' added
Folderyear = Range("B11") ' added
Folder = Range("B12")
NewYear = Range("B9")
ThisYear = Range("B10")
FilePath = Folder + Folderyear
Myfile = Dir(FilePath)
    Do While Len(Myfile) > 0
        'If Myfile = "*programcleanup*" Then
        'Exit Sub
        'End If
       [COLOR=#0000ff]If Myfile <> ThisWorkbook.Name Then[/COLOR]
            Workbooks.Open (FilePath & Myfile)
            For Q = 1 To Application.Worksheets.Count
                'If Myfile = "*programcleanup*" Then
                'Exit Sub
                'End If
                 Worksheets(Q).Activate
                'Range("C13:H25").Copy
                'Range("B13").PasteSpecial
                'Range("C39:E42").Copy
                'Range("B39").PasteSpecial
                Range("A20") = NewYear ' should be A3 changed to A20 for testing
                'Range("H13") = NewYear
                'Range("H39") = NewYear
                'Range("E39") = ThisYear
                'Range("G14") = "Budget"
            Next Q
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        [COLOR=#0000ff]End If
[/COLOR]       Myfile = Dir
    Loop
End Sub

 

[josephgunter163]

Thanks for the help JLGWhiz but I'm getting the same results. I'm going to keep tinkering with it. I'll let you know what I come up with.

 

[josephgunter163]

JLGWhiz, So I was tinkering with the code and decided to go back to my original and work my way forward from there. I found that if I put the code into a form control button it works. If the code is in an Active X button it does not. Not sure what the difference is but to me it doesn't matter. Thanks again for the help.

 

[JLGWhiz]

JLGWhiz, So I was tinkering with the code and decided to go back to my original and work my way forward from there. I found that if I put the code into a form control button it works. If the code is in an Active X button it does not. Not sure what the difference is but to me it doesn't matter. Thanks again for the help.

The difference is that with form controls button, the code can have any title and must be in a class module that is created by the user with the Insert>Module method in the vb editor.

The Active=X code must be in the object that hosts the button, eg. Sheet or Userform, and the title line mus be of specific type for that control (in your case a button) as a _Click() event. So to get a button on a sheet to work with active-X button on a sheet

Me.CommandButton1_Click()

With the code in the sheet code module for the host sheet.

 

[JLGWhiz]

Adding to Post #5 commients.
With Active-X controls, you can enter design mode by clicking the Triangle/Pencil/Ruler icon then right click the control, click 'View Code' in the pop up menu and if no code has been assigned it will display the title line and End Sub line which you can add code in between to do what you want. If you have already entered code, the when you right click it will simply display the existing macro for edit. Whereas, with form controls you have to assign a macro to the control and the macro will appear in the Macro dialog box when it is called up. The Active-X click event codes do not appear in the Macor dialog box because they are private subs and the full title line for a button on a sheet would be
Private Sub CommandButton1_Click().