Problem executing macro on first run after opening workbook

zott69

New Member
Joined
Jul 9, 2015
Messages
1
Hi

This is my first post, and I'm quite a newbie to VB so please be gentle.

I'm having an issue whereby the code that I've written (with a little help) to save the existing file as new, which checks to see if a file already exists with the same name, doesn't run as expected on first run after opening the workbook.

In the code, I try to bypass the automatic prompt for "file already exists" to give the user the option to change the filename and so the macro doesn't break. But on first run, the automatic prompt comes up, meaning when you choose No/Cancel the error code '1004' comes up and breaks the macro.

Strangley, the macro works fine the second time you run it.

Code:
Sub save_it_as_new()

    dToday = Format(DateTime.Now, "yyyymmdd")
    x = MsgBox("This will save as a new file with today's date", vbOKCancel, "")
    f = "name_of_file " & dToday & ".xlsm"

[COLOR=#ff0000]'''on first run the code follows Case 1, on second run the code follows (correctly) Case Else[/COLOR]
    Select Case x
    Case 1
        If Not FileExist((f)) Then [COLOR=#008000]'file doesn't exist[/COLOR]
        
        ChDrive "C:\"
        ChDir "C:\location\of\file\"


        ActiveWorkbook.SaveAs f
        
        Else [COLOR=#008000]'file does exist[/COLOR]
            z = MsgBox("Filename already exists - overwrite?", vbYesNoCancel, "")


            Select Case z
            Case 6 [COLOR=#008000]'user pressed Yes[/COLOR]
                Application.DisplayAlerts = False
                 ChDrive "C:\"
                 ChDir "C:\location\of\file\"


                 ActiveWorkbook.SaveAs f
                 Application.DisplayAlerts = True
            Case 7 'user pressed No
                 Application.GetSaveAsFilename
                
            Case 2 [COLOR=#008000]'user pressed Cancel[/COLOR]
                 Exit Sub
               End Select
         End If
        
    Case 2 [COLOR=#008000]'user pressed Cancel[/COLOR]
        Exit Sub
        
    End Select
     
End Sub


Function FileExist(FilePath As String) As Boolean
[COLOR=#008000]'PURPOSE: Test to see if a file exists or not[/COLOR]
[COLOR=#008000]'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault[/COLOR]
[COLOR=#008000]'RESOURCE: http://www.rondebruin.nl/win/s9/win003.htm[/COLOR]


Dim TestStr As String


  On Error Resume Next
    TestStr = Dir(FilePath)
  On Error GoTo 0


[COLOR=#008000]'Determine if File exists[/COLOR]
  If TestStr = "" Then
    FileExist = False
  Else
    FileExist = True
  End If


End Function
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top