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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Excel conversion issue with Sql server | 14 | 46 | |
Excel VBA - Public declaration of variables | 3 | 46 | |
macro for same location path | 2 | 19 | |
remove lower case characters in excel formula | 12 | 31 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
25 Experts available now in Live!