Seamus2626
asked on
Create File
Hi,
I have attached some code which saves a file down. I have two changes i would like to make.
(1) I would like the file to be saved down with todays date, i.e. no day difference.
(2) The code currently looks for a dated folder when saving down, so i get the attached error message. I need it only to look for the path
\\Ukhibmdata02.hbeu.adroot .hsbc\righ ts\Task Manager
and then save with todays date
Thanks
Seamus
Sub CreateFile1()
Dim strFile As String
strFile = Date_FileName("\\Ukhibmdat a02.hbeu.a droot.hsbc \rights\Ta sk Manager\", "Task Manager ")
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
MsgBox "File Created, close file"
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
CharDate = Format(Date - DayDiff, "DD MMM YY") ' Get the character date formwatted how we want it
CharMonth = Format(Date - DayDiff, "MM MMM YY")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
ERROR.doc
I have attached some code which saves a file down. I have two changes i would like to make.
(1) I would like the file to be saved down with todays date, i.e. no day difference.
(2) The code currently looks for a dated folder when saving down, so i get the attached error message. I need it only to look for the path
\\Ukhibmdata02.hbeu.adroot
and then save with todays date
Thanks
Seamus
Sub CreateFile1()
Dim strFile As String
strFile = Date_FileName("\\Ukhibmdat
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
MsgBox "File Created, close file"
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
CharDate = Format(Date - DayDiff, "DD MMM YY") ' Get the character date formwatted how we want it
CharMonth = Format(Date - DayDiff, "MM MMM YY")
Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".xls"
Date_FileName = Result
End Function
ERROR.doc
Look at the folder permissions to set write access for the folder where you save.
ASKER
Nope, permission is fine, i can manually save a file into the folder
Thanks
Seamus
Thanks
Seamus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change the date format to DD MMM YY if you want the day included rather than month number.
ASKER
Thats the one, good man Rory
Thanks
Seamus
Thanks
Seamus