Save as code error

Posted on 2011-05-06
Last Modified: 2012-08-14

I have some code which attempts to save a file as a CSV, when i run it, i get the attached  error message

The folder is not read only, is there any error in the code?


Sub CreateFile()
Dim strFile As String
strFile = Date_FileName("\\ukhibmdata02\RIGHTS\Eurobond\COUPONS\CouponTickets\Coupons 2011\Output\", "Treats Seamus MTN For Merit ")

If Dir(strFile) <> "" Then
   If MsgBox("File already exists - overwrite?", vbYesNo) = vbYes Then
      Kill strFile

      Exit Sub
   End If
End If
ActiveWorkbook.SaveAs Filename:= _
strFile, _
FileFormat:=xlCSV, 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
        If DayOfWeek = 1 Then
          DayDiff = 2                                           ' If a Sunday then we need the date as of friday, otherwise previous day
          DayDiff = 1                                           ' otherwise it should be set to the previous day
        End If
    End If
    CharDate = Format(Date - DayDiff, "ddmmyyyy")               ' Get the character date formwatted how we want it

    CharMonth = Format(Date - DayDiff, "m mmm yy")
    Result = pPath & CharMonth & "\" & pFilePrefix & "" & CharDate & ".CSV"
    Date_FileName = Result
End Function

Open in new window

Question by:Seamus2626
    LVL 29

    Accepted Solution

    Try saving in a different folder. Does that generate an error?

    LVL 29

    Expert Comment

    Some other things to try:

    Shorter name and no spaces in the file name.
    LVL 85

    Assisted Solution

    by:Rory Archibald
    If you add a Msgbox strFile line, is the path that comes up correct? Does the date folder already exist?

    Author Closing Comment

    all sorted guys, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now