Modify code, to open all files in a directory except one

[Bagsy]

Could anybody help me a bit here please, I have some code which opens all the files in a directory & copies and pastes information onto a sheet. I need to modify it so it will open all files except one file named WIP.
I did try modifying this line Do While myfile <> "", but it just gave me an error
Any help is very much appreciated

Sub ImportCostSheetDetails()
   Dim i As Long
   Dim Ws As Worksheet
   Dim Wkbk As Workbook
   Dim sht As Worksheet
   
Dim fnd As Variant
Dim rplc As Variant
  
  With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
End With

   Set Ws = Worksheets("Sheet1")

    myDir = "W:\1works managers files\Cost sheets"
    'myDir = "W:\1works managers files\Cost sheets\_Cost sheets test"
   
   myfile = Dir(myDir & Application.PathSeparator & "*.xlsm", vbDirectory)
   
   Ws.Range("B3:E500").ClearContents 'Clear Data in Column B3 to E500
   i = 3 ' this is starting a Row 1 then offsetting to row 3
        Do While myfile <> ""
            Ws.Cells(i, 2) = myfile ' this is offsetting from column A to column B
                Set Wkbk = Workbooks.Open(myDir & "\" & myfile, False) ' the false on the end prevents update links and opens read only
                    Ws.Cells(i, 3).Value = Wkbk.Sheets("Cost Sheet").Range("AF7").Value ' Labour
                        Ws.Cells(i, 4).Value = Wkbk.Sheets("Cost Sheet").Range("AG7").Value ' Material
                    Ws.Cells(i, 5).FormulaR1C1 = "=SUM(RC[-2]+RC[-1])" ' Sum of Qty. & Material
                Wkbk.Close False
            myfile = Dir
        i = i + 1
    Loop
    
'removing file extension and Perform the Find/Replace All
fnd = ".xlsm"
rplc = ""

  Set sht = Sheets("Sheet1") 'Store a specfic sheet to a variable
    sht.Cells.Replace what:=fnd, Replacement:=rplc, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
     With Application
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
       
End Sub

 

[mohadin]

HI
Have you

Do While myfile <> "" and myfile = "WIP"

 

[Bagsy]

Thanks mohadin
Neat suggestion
Unfortunately although it did not give any errors, it bypassed the main part of the code & went straight to removing the file extension.
I have however got it to work I added
If myfile <> "WIP.XLSX" Then Workbooks.Open Filename:=myDir & "" & myfile, UpdateLinks:=False
Do While myfile <> ""
This now seems to be OK I think, still testing.
Seems I pressed the panic button just too early. Full code below may help somebody else out.

Sub ImportCostSheetDetails()
   Dim i As Long
   Dim Ws As Worksheet
   Dim Wkbk As Workbook
   Dim sht As Worksheet
   
Dim fnd As Variant
Dim rplc As Variant
  
  With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
End With

   Set Ws = Worksheets("Sheet1")

    'myDir = "W:\1works managers files\Cost sheets"
    myDir = "W:\1works managers files\Cost sheets\Cost sheets test"

   
   myfile = Dir(myDir & Application.PathSeparator & "*.xlsm", vbDirectory)
   
   Ws.Range("B3:E500").ClearContents 'Clear Data in Column B3 to E500
   i = 3 ' this is starting a Row 1 then offsetting to row 3
   '-----------------------------------
   
   
Do While myfile <> ""
    If myfile <> "WIP.XLSX" Then Workbooks.Open Filename:=myDir & "\" & myfile, UpdateLinks:=False
            Ws.Cells(i, 2) = myfile ' this is offsetting from column A to column B
                Set Wkbk = Workbooks.Open(myDir & "\" & myfile, False) ' the false on the end prevents update links and opens read only
                    Ws.Cells(i, 3).Value = Wkbk.Sheets("Cost Sheet").Range("AF7").Value ' Labour
                        Ws.Cells(i, 4).Value = Wkbk.Sheets("Cost Sheet").Range("AG7").Value ' Material
                    Ws.Cells(i, 5).FormulaR1C1 = "=SUM(RC[-2]+RC[-1])" ' Sum of Qty. & Material
                Wkbk.Close False
            myfile = Dir
        i = i + 1
    Loop
    
'removing file extension and Perform the Find/Replace All
fnd = ".xlsm"
rplc = ""

  Set sht = Sheets("Sheet1") 'Store a specfic sheet to a variable
    sht.Cells.Replace what:=fnd, Replacement:=rplc, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
     With Application
  .DisplayAlerts = True
  .ScreenUpdating = True
End With

End Sub

 

[mohadin]

Hi
Thank you for the reply
Be happy
cheers