malleshg24
10-17-2020, 05:50 PM
Hi Team,
I use below code to check files in a folder and open correct file.
Can we shorten below code, by Creating one time function:help
Step1 User will select date and Product Name (MTO) on a calender.
in excel Complete file path will get generated. with some formula.
Sub Check_File_and Open()
frmInput.Show
wsFolderPath.Range("I1").Value = dtReportDate
wsFolderPath.Range("J1").Value = strMTOName
Path = wsFolderPath.Range("B2").Value
Keyword = strMTOName & " Debtor Report"
'Check File and Open Debtor workbook
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Debtor = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check File and Open Creditor
Keyword = strMTOName & " Creditor"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Creditor = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check Sales1 Workbook and Open
Keyword = strMTOName & " Sales Register 001"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Sales1 = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check File Sales2 Workbook
Keyword = strMTOName & " Sales Register 002"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Sales2 = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'myend:
'I am closing all workbook here, setting object to nothing
End Sub
I use below code to check files in a folder and open correct file.
Can we shorten below code, by Creating one time function:help
Step1 User will select date and Product Name (MTO) on a calender.
in excel Complete file path will get generated. with some formula.
Sub Check_File_and Open()
frmInput.Show
wsFolderPath.Range("I1").Value = dtReportDate
wsFolderPath.Range("J1").Value = strMTOName
Path = wsFolderPath.Range("B2").Value
Keyword = strMTOName & " Debtor Report"
'Check File and Open Debtor workbook
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Debtor = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check File and Open Creditor
Keyword = strMTOName & " Creditor"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Creditor = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check Sales1 Workbook and Open
Keyword = strMTOName & " Sales Register 001"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Sales1 = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'Check File Sales2 Workbook
Keyword = strMTOName & " Sales Register 002"
strFile = Dir(Path & "\*" & Keyword & "*.xls*")
If strFile <> "" Then
Application.DisplayAlerts = False
Set wbk_Sales2 = Workbooks.Open(Path & "\" & strFile, False, True)
Application.DisplayAlerts = True
Else
MsgBox "Workbook :- " & Keyword & " not found in a Folder " & Path, vbCritical, "Select Correct Date or Check Files in A Folder"
GoTo Myend
End If
'myend:
'I am closing all workbook here, setting object to nothing
End Sub