Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save as csv (Excel 2000 Sr-1)

    I have the following working code which takes a worksheet and saves it as a csv file,
    and the file name already exists I get prompted to overwrite it.

    I would like to create the file, overwriting the existing one without being prompted.

    Sheets("Instructions").Select
    SaveDir = Cells(29, 2) + Cells(30, 2) + "Data"
    ScName = Cells(32, 2)
    Sheets("struct").Select
    ActiveWorkbook.SaveAs Filename:= _
    SaveDir + "" + ScName + " struct.csv", FileFormat:=xlCSV _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False

    Any help appreciated
    Michael

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save as csv (Excel 2000 Sr-1)

    Hi Michael,

    Insert Application.DisplayAlerts = False before the SaveAs instruction, and Application.DisplayAlerts = True after it.

    Note: it is advisable to add error handling to your routine, so that Application.DisplayAlerts = True is always executed, even if an error occurs:

    Sub SomethingOrOther()
    Dim SaveDir As String
    ...

    On Error GoTo ErrHandler

    ...
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ...

    ExitHandler:
    Application.DisplayAlerts = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •