How to close all .csv or .xlsx type file

[xyz987]

Hi sirs
i want to close all csv and xlsx type file, when i restart program. i use * to filter, but it looks some problem... please give me some suggest, thanks.

Sub check_menu()
   Dim wb As Workbook
   'ThisWorkbook.Path：傳回本工作簿、檔案所儲存的路徑。
   'ThisWorkbook.FullName：傳回本工作簿、檔案所儲存的路徑 + 檔名。
   'ThisWorkbook.Name：傳回本工作簿、檔案所儲存的檔名。
   'ActiveWindow.ActiveSheet.Name 或 ActiveSheet.Name 目前工作表名稱
   work_book = ThisWorkbook.Name
'檔案檢查
   If Dir("C:\stock", vbDirectory) = "" Then MkDir "C:\stock"
If Dir("C:\stock\analysis", vbDirectory) = "" Then MkDir "C:\stock\analysis"
   If Dir("C:\stock\data", vbDirectory) = "" Then MkDir "C:\stock\data"  
For file_check = 1 To Workbooks.Count
    If Workbooks(file_check).Name = "*.csv" Then
        Windows("*.csv").Activate
        Application.DisplayAlerts = False
        Windows("*.csv").Close (vb = yes)
        Application.DisplayAlerts = True
    End If
   Next file_check
For file_check = 1 To Workbooks.Count
    If Workbooks(file_check).Name = "*.xlsx" Then
       Windows("*.xlsx").Activate
        Application.DisplayAlerts = False
        Windows("*.xlsx").Close (vb = no)
        Application.DisplayAlerts = True
    End If
   Next file_check
'舊檔案刪除
If Len(Dir("C:\stock\*.*")) > 0 Then Kill "C:\stock\*.*"
'SetAttr "C:\stock\*.*", vbNormal   ' 排除唯讀檔問
If Len(Dir("C:\stock\data\*.*")) > 0 Then Kill "C:\stock\data\*.*"
'SetAttr "C:\stock\data\*.*", vbNormal   ' 排除唯讀檔問題
If Len(Dir("C:\stock\analysis\*.*")) > 0 Then Kill "C:\stock\analysis\*.*"
SetAttr "C:\stock\analysis\*.*", vbNormal   ' 排除唯讀檔問題
End Sub

[June7]

See if this helps https://answers.microsoft.com/en-us/...a-9cb0dbcef0be

[xyz987]

solve it, thanks

Sub check_menu()
   Dim wb As Workbook
   'ThisWorkbook.Path：傳回本工作簿、檔案所儲存的路徑。
   'ThisWorkbook.FullName：傳回本工作簿、檔案所儲存的路徑 + 檔名。
   'ThisWorkbook.Name：傳回本工作簿、檔案所儲存的檔名。
   'ActiveWindow.ActiveSheet.Name 或 ActiveSheet.Name 目前工作表名稱
   work_book = ThisWorkbook.Name
'檔案檢查
   If Dir("C:\stock", vbDirectory) = "" Then MkDir "C:\stock"
   If Dir("C:\stock\analysis", vbDirectory) = "" Then MkDir "C:\stock\analysis"
   If Dir("C:\stock\data", vbDirectory) = "" Then MkDir "C:\stock\data"
   'For file_check = 1 To Workbooks.Count
    'If Workbooks(file_check).Name = "single_suggest.xlsx" Then
    'Windows("single_suggest.xlsx").Activate
    'Application.DisplayAlerts = False
    'Windows("single_suggest.xlsx").Close (vb = no)
    'Application.DisplayAlerts = True
    'Exit For
    'End If
    'Next file_check
    'For file_check = 1 To Workbooks.Count
    'If Workbooks(file_check).Name = "multi_suggest.xlsx" Then
    'Windows("multi_suggest.xlsx").Activate
    'Application.DisplayAlerts = False
    'Windows("multi_suggest.xlsx").Close (vb = no)
    'Application.DisplayAlerts = True
    'Exit For
    'End If
    'Next file_check
    For file_check = 1 To Workbooks.Count
        check_1 = Right(Workbooks(file_check).Name, 4)
        If check_1 = ".csv" Then
            check_2 = Workbooks(file_check).Name
            'Windows(check_2).Activate
            Application.DisplayAlerts = False
            Windows(check_2).Close (vb = yes)
            Application.DisplayAlerts = True
        End If
    Next file_check
    For file_check = 1 To Workbooks.Count
        check_1 = Right(Workbooks(file_check).Name, 5)
        If check_1 = ".xlsx" Then
            check_2 = Workbooks(file_check).Name
            'Windows(check_2).Activate
            Application.DisplayAlerts = False
            Windows(check_2).Close (vb = yes)
            Application.DisplayAlerts = True
        End If
    Next file_check
    '舊檔案刪除
    If Len(Dir("C:\stock\*.*")) > 0 Then Kill "C:\stock\*.*"
    'SetAttr "C:\stock\*.*", vbNormal   ' 排除唯讀檔問題
    If Len(Dir("C:\stock\data\*.*")) > 0 Then Kill "C:\stock\data\*.*"
    'SetAttr "C:\stock\data\*.*", vbNormal   ' 排除唯讀檔問題
    If Len(Dir("C:\stock\analysis\*.*")) > 0 Then Kill "C:\stock\analysis\*.*"
    'SetAttr "C:\stock\analysis\*.*", vbNormal   ' 排除唯讀檔問題
    Main_Menu (work_book)
End Sub

[xyz987]

Dear sirs
fix last code, if use "for" will cause error, need change "for each"

Sub check_menu()
    Dim wb As Workbook
    'ThisWorkbook.Path：傳回本工作簿、檔案所儲存的路徑。
    'ThisWorkbook.FullName：傳回本工作簿、檔案所儲存的路徑 + 檔名。
    'ThisWorkbook.Name：傳回本工作簿、檔案所儲存的檔名。
    'ActiveWindow.ActiveSheet.Name 或 ActiveSheet.Name 目前工作表名稱
    work_book = ThisWorkbook.Name
    '檔案檢查
    If Dir("C:\stock", vbDirectory) = "" Then MkDir "C:\stock"
    If Dir("C:\stock\analysis", vbDirectory) = "" Then MkDir "C:\stock\analysis"
    If Dir("C:\stock\data", vbDirectory) = "" Then MkDir "C:\stock\data"
    For each wb in application.workbooks
    if Right(wb.Name, 4) = ".csv" or Right(wb.Name, 4) = "csv" then
        Application.DisplayAlerts = False
        wb.Close (vb = yes)
        Application.DisplayAlerts = True
    elseIf Right (wb.Name, 5) = ".xlsx" or Right(wb.Name, 5) = "xlsx" then
        Application.DisplayAlerts = False
        wb.Close (vb = yes)
       Application.DisplayAlerts = True
    End If
    Next wb
    '舊檔案刪除
    If Len(Dir("C:\stock\*.*")) > 0 Then Kill "C:\stock\*.*"
    'SetAttr "C:\stock\*.*", vbNormal   ' 排除唯讀檔問題
    If Len(Dir("C:\stock\data\*.*")) > 0 Then Kill "C:\stock\data\*.*"
    'SetAttr "C:\stock\data\*.*", vbNormal   ' 排除唯讀檔問題
    If Len(Dir("C:\stock\analysis\*.*")) > 0 Then Kill "C:\stock\analysis\*.*"
    'SetAttr "C:\stock\analysis\*.*", vbNormal   ' 排除唯讀檔問題
    Main_Menu (work_book)
End Sub

[Aussiebear]

What is the error?

[xyz987]

sorry, i just look this message, if use "For file_check = 1 To Workbooks.Count" , when it finish (close how many excel)then jump to other sub, it will cause how many close excel circle. like if it close three excel file. then when it finish, it will make three circle. but maybe it only error in my code, if you want to look, attach file is full file, you can refer it. sorry about my English not very well, maybe cause you confuse.