Seamus2626
asked on
Amend a create file vba sub
I have the below sub which i need amending, i need the code to recognise thats its 2014 and the month, so next month it would save the file in the folder ("\\ukxyzmdata23456\rights \debt2014\ 201407\", "The file")
Im not sure there is a need for the function as i have no requirement to save the file with any day difference
Many thanks
-------------------------- ---------- ------
Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukxyzmdat a23456\rig hts\debt\2 014\201406 \", "The file ")
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
Kill strFile
Else
Exit Sub
End If
End If
ActiveWorkbook.SaveAs Filename:= _
strFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth As String
Dim Result As String
DayOfWeek = Weekday(Date) ' Sunday is 1, Monday is 2, Tuesday is 3 etc
If DayOfWeek = 2 Then ' If a monday then we need the date as of friday, otherwise previous day
DayDiff = 3
Else
If DayOfWeek = 1 Then
DayDiff = 2 ' If a Sunday then we need the date as of friday, otherwise previous day
Else
DayDiff = 1 ' otherwise it should be set to the previous day
End If
End If
' Get the character date formwatted how we want
CharMonth = Format(Date - DayDiff, "m mmm yyyy")
CharDate = Format(Date - DayDiff, "dd mmm yy")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
Im not sure there is a need for the function as i have no requirement to save the file with any day difference
Many thanks
--------------------------
Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukxyzmdat
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
Kill strFile
Else
Exit Sub
End If
End If
ActiveWorkbook.SaveAs Filename:= _
strFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=False
End Sub
Function Date_FileName(pPath As String, pFilePrefix As String) As String
Dim DayOfWeek As Integer, DayDiff As Integer, CharDate As String, MonthNo As Integer, CharYear As String, CharMonth As String
Dim Result As String
DayOfWeek = Weekday(Date) ' Sunday is 1, Monday is 2, Tuesday is 3 etc
If DayOfWeek = 2 Then ' If a monday then we need the date as of friday, otherwise previous day
DayDiff = 3
Else
If DayOfWeek = 1 Then
DayDiff = 2 ' If a Sunday then we need the date as of friday, otherwise previous day
Else
DayDiff = 1 ' otherwise it should be set to the previous day
End If
End If
' Get the character date formwatted how we want
CharMonth = Format(Date - DayDiff, "m mmm yyyy")
CharDate = Format(Date - DayDiff, "dd mmm yy")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
ASKER
Hi GasperK,
I dont mean to be lazy or stupid, but i am still relatively new to VBA, could you work that line into my code or show it as it would appear in a stand alone sub?
Thanks!
I dont mean to be lazy or stupid, but i am still relatively new to VBA, could you work that line into my code or show it as it would appear in a stand alone sub?
Thanks!
Sub CreateFile()
Dim strFile As String
yearmonth = Format(Date, "yyyymm")
strFile = "\\ukxyzmdata23456\rights\debt\2014\" & yearmonth & "\The file"
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
'Kill strFile
Else
Exit Sub
End If
End If
ActiveWorkbook.SaveAs Filename:=strFile
End Sub
This is basically it, the kill file and some other parts of the code were not ok, so i added a comment to that or just deleted it, but the rest should work.
Try it out...
ASKER
Hi GasperK,
I have created the below sub, and am getting the message "Bad file name or number"
Sub CreateFile2()
Dim strFile As String
yearmonth = Format(Date, "yyyymm")
strFile = "\\R:\SPM\Monthly_Inputs\2 014\Report ing" & yearmonth & "\The file.xlsx"
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
'Kill strFile
Else
Exit Sub
End If
End If
ActiveWorkbook.SaveAs Filename:=strFile
End Sub
I have created the below sub, and am getting the message "Bad file name or number"
Sub CreateFile2()
Dim strFile As String
yearmonth = Format(Date, "yyyymm")
strFile = "\\R:\SPM\Monthly_Inputs\2
If Dir(strFile) <> "" Then
If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
'Kill strFile
Else
Exit Sub
End If
End If
ActiveWorkbook.SaveAs Filename:=strFile
End Sub
ASKER
I have this sub for opening files in the same folder
FileYear = Year(Date)
MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "yyyymm")
FilePath = "R:\SPM\Monthly_Inputs\" & FileYear & "\" & FileDate & "\Reporting\June_2014_Win_ Loss_Jack. xlsx"
Workbooks.Open (FilePath)
Could you amend so it doesnt open the file, but saves as into that folder?
Thanks
FileYear = Year(Date)
MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
FileMonth = Month(Date) - MonthOffset
FileDate = Format(Date - 1, "yyyymm")
FilePath = "R:\SPM\Monthly_Inputs\" & FileYear & "\" & FileDate & "\Reporting\June_2014_Win_
Workbooks.Open (FilePath)
Could you amend so it doesnt open the file, but saves as into that folder?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
all you need is a
yearmonth = Format(Date, "yyyymm")
and then a
strFile = Date_FileName("\\ukxyzmdat
Hope this helps