Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA to overwrite an existing file

  1. #1
    Board Regular RichardMGreen's Avatar
    Join Date
    Feb 2006
    Location
    Somewhere beyond the Twilight Zone
    Posts
    2,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to overwrite an existing file

    Hi all

    I've code some VBA code which produces an output file from a template.
    Now, if I want to run the output more than once, I get an message asking if I want to overwrite the existing file (which I always do).

    I've tried turning off displayalerts but it's not working.
    Is there any way of turning this message off?
    RichardMGreen

    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Office 2003/2007 (Work) and Office 2007 (Home)

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,920
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to overwrite an existing file

    You can check for the file and delete it first:
    Code:
    If Dir(filepath) <> "" then kill filepath

  3. #3
    Board Regular RichardMGreen's Avatar
    Join Date
    Feb 2006
    Location
    Somewhere beyond the Twilight Zone
    Posts
    2,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    I did look at that, but I don't think it'll work.
    I stumbled upon an Application.AlertBeforeOverwriting but I'm not sure how to use it (and the help files aren't that helpful).

    Any ideas?
    RichardMGreen

    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Office 2003/2007 (Work) and Office 2007 (Home)

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,920
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to overwrite an existing file

    Quote Originally Posted by RichardMGreen View Post
    I did look at that, but I don't think it'll work.
    because?

  5. #5
    Board Regular RichardMGreen's Avatar
    Join Date
    Feb 2006
    Location
    Somewhere beyond the Twilight Zone
    Posts
    2,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    Sorry, that was half a comment.
    I don't think it will work as the file is on a secure server and it's very difficult to delete files, which is why I was overwriting in the first place. It is, however, easy enough to add/overwrite files to/on the server (bizarre, I know).
    RichardMGreen

    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Office 2003/2007 (Work) and Office 2007 (Home)

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,920
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to overwrite an existing file

    Makes no sense to me, but you might try
    Code:
    Application.DisplayAlerts = False
    to suppress the dialog.

  7. #7
    Board Regular RichardMGreen's Avatar
    Join Date
    Feb 2006
    Location
    Somewhere beyond the Twilight Zone
    Posts
    2,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    Me neither, but there you go.
    I did try that, but it still asks if I want to overwrite the file.
    RichardMGreen

    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Office 2003/2007 (Work) and Office 2007 (Home)

  8. #8
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,771
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    Did you try Application.AlertBeforeOverwriting=False immediately before you try to write the file?

    If the file is being written by VBA code then maybe the warning message also being generated by VBA code, in which case you won't be able to suppress it in that way? Look for some code like this:-
    Code:
    if dir(somefilename) then
      if msgbox("Are you sure?",vbyesno)=vbyes then
        ' some code to overwrite the file
      end if
    end if
    If there's something like that in your procedure, that's where you should be focusing your attention.

    Otherwise I think we've reached the point where you should post the code for us to look at.
    Last edited by Ruddles; Apr 1st, 2011 at 11:06 AM.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  9. #9
    Board Regular RichardMGreen's Avatar
    Join Date
    Feb 2006
    Location
    Somewhere beyond the Twilight Zone
    Posts
    2,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    Here's the code that generates the output file:-
    Code:
    Sub b_exporter()
    '----- Switch off screen updating (stop flicker) -----
        Application.ScreenUpdating = False
    '----- Copy all worksheet to a new book -----
        Worksheets.Copy
    '----- Go through worksheets, make into values only and hide row/column headers -----
        For Each ws In Worksheets
            ws.Select
            Cells.Select
            Selection.Copy
            Selection.PasteSpecial (xlPasteValues)
            Range("B1").Select
            ActiveWindow.DisplayHeadings = False
        Next
    '----- Destroy variable -----
        Set ws = Nothing
    '----- Switch off alerts, remove data_Sheet1 as no longer needed and switch alerts back on -----
        Application.DisplayAlerts = False
        Sheets("Data_Sheet1").Delete
        Application.DisplayAlerts = True
    '----- Select first sheet and save/close workbook with new name -----
        Sheets("Call Volumes").Select
        ActiveWorkbook.SaveAs filepath & "\Output Files\NHSD - " & Format(Sheets("Call Volumes").Range("C4"), "Mmmm YYYY") & ".xls"
        ActiveWorkbook.Close True
    '----- Select first sheet on this workbook to go back to beginning -----
        Sheets("Call Volumes").Select
    '----- Switch screen updating back on -----
        Application.ScreenUpdating = True
    End Sub
    It looks like it's an automatic error message generated by the system.
    RichardMGreen

    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Office 2003/2007 (Work) and Office 2007 (Home)

  10. #10
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,771
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to overwrite an existing file

    I think it may be the ActiveWorkbook.Close which is triggering the message. (You can confirm this if you know how to single-step through the code) Why are you saving the workbook again when you close it if you've just saved it and not made any changes since then?

    Try closing it with ActiveWorkbook.Close False.

    (I'm only guessing.)
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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