Create File

Seamus2626
Seamus2626 used Ask the Experts™
on
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\rights\Task Manager


and then save with todays date



Thanks
Seamus


Sub CreateFile1()
Dim strFile As String
strFile = Date_FileName("\\Ukhibmdata02.hbeu.adroot.hsbc\rights\Task 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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Anuradha GoliSystems Development / Support Specialist

Commented:
Look at the folder permissions to set write access for the folder where you save.

Author

Commented:
Nope, permission is fine, i can manually save a file into the folder

Thanks
Seamus
Most Valuable Expert 2011
Top Expert 2011
Commented:
Function Date_FileName(pPath As String, pFilePrefix As String) As String
     Date_FileName= pPath &  Format(Date, "MM MMM YY") & ".xls"
End Function

Open in new window


should do it I think.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Change the date format to DD MMM YY if you want the day included rather than month number.

Author

Commented:
Thats the one, good man Rory

Thanks
Seamus

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial