Sub TodaysTxt()
Dim fl As Object, fso As Object
Dim fileSpec As String 'Will be populated with path & filename of chosen txt file
fileSpec = Application.GetOpenFilename("Text files (*.txt), *.txt", Title:="Please pick the file of interest, then click 'Open'")
If fileSpec = "False" Then Exit Sub
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
If CDate(fl.DateLastModified) < Date Then
MsgBox "The chosen txt file was modified before today. Please find a more recent file."
Exit Sub
End If
'Your code goes here
End Sub
'Delete old sheet tab
Application.DisplayAlerts = False
Sheets("CDPSRECRPT").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = False
'
'Open new days dataset from extract.
Workbooks.OpenText Filename:= _
"C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
Sub TodaysFiles()
Dim fl As Object, fso As Object
Dim fileSpec As String
'Check if file has been updated
fileSpec = "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
If CDate(fl.DateLastModified) < Date Then
MsgBox "The file you are about to use has not been updated with today's data." & vbLf & _
"You probably don't want to use yesterday's dataset to produce today's report"
Exit Sub
Else
'Delete old sheet tab
Application.DisplayAlerts = False
Sheets("CDPSRECRPT").Delete
Application.DisplayAlerts = False
'
'Open new days dataset from extract.
Workbooks.OpenText Filename:= _
"C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=True, OtherChar:="|", TrailingMinusNumbers:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), _
Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1))
'Do more stuff with this file if you like
End If
End Sub
MsgBox "abc" & vbLf & vbLf & "def"
Making that change to the previous code, you would have:Sub TodaysFiles()
Dim fl As Object, fso As Object
Dim fileSpec As String
'Check if file has been updated
fileSpec = "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
If CDate(fl.DateLastModified) < Date Then
MsgBox "The file you are about to use has not been updated with today's data." & vbLf & vbLf & _
"You probably don't want to use yesterday's dataset to produce today's report"
Exit Sub
Else
'Delete old sheet tab
Application.DisplayAlerts = False
Sheets("CDPSRECRPT").Delete
Application.DisplayAlerts = False
'
'Open new days dataset from extract.
Workbooks.OpenText Filename:= _
"C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=True, OtherChar:="|", TrailingMinusNumbers:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), _
Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1))
'Do more stuff with this file if you like
End If
End Sub
Sub TodaysFiles()
Dim fl As Object, fso As Object
Dim fileSpec As String
'Check if file has been updated
fileSpec = "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
Select Case (Now - CDate(fl.DateLastModified))
Case Is > TimeSerial(12, 0, 0)
MsgBox "The file you are about to use has not been updated with today's data." & vbLf & vbLf & _
"You probably don't want to use yesterday's dataset to produce today's report"
Exit Sub
Case Is > TimeSerial(3, 0, 0)
MsgBox "The file you are about to use has not been updated with the latest data." & vbLf & vbLf & _
"You probably don't want to use this morning's dataset to produce the afternoon report"
Exit Sub
Case Else
'Delete old sheet tab
Application.DisplayAlerts = False
Sheets("CDPSRECRPT").Delete
Application.DisplayAlerts = False
'
'Open new days dataset from extract.
Workbooks.OpenText Filename:= _
"C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=True, OtherChar:="|", TrailingMinusNumbers:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), _
Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1))
'Do more stuff with this file if you like
End Select
End Sub
Title | # Comments | Views | Activity |
---|---|---|---|
Excel: How would I make a depleting dropdown list that shows only values that havent been chosen before? | 4 | 52 | |
Formula to convert ranked data | 2 | 11 | |
Excel Difference between 2013 and 2016 | 2 | 15 | |
Excel Formula Unlimited IF's | 6 | 15 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
9 Experts available now in Live!