Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    No Filedialog message to say file already exists

    The code below will look familiar to some!

    (Controlling Word from Excel)

    When saving, I do not get filediolog message saying file already exists, my code as it is just over writes/replaces the existing file if it is.
    I have Googled my heart out and the chances are the answer was right in front of me. I have tested various methods all in different locations within the code. All come back saying file exists – but then it would do wouldn’t it because haven’t I just created it with the Template?
    So it is a matter of where when and how do I place the argument for if it exists or not
    It all really comes down the file dialog not showing?

    Any help readily received.

    jjhs

    Code:
    Sub CommandButton1_Click()
                              'macropods version using a .dotx template
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdRng As Word.Range
    Dim wdPath As String
    
        wdPath = "C:\Users\Me\ Documents\My Doc\ServiceTimes\2014 Timesheets\"
        ThisWorkbook.ActiveSheet.Range("B10:E59").Copy 'Range on Excel sheet of condensed Timesheets
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Open(wdPath & "Timesheets.dotx")      ' Create a new document
        With wdDoc
              'Populate the document
        Set wdRng = .Bookmarks("Point_1").Range
        wdRng.PasteAndFormat (wdPasteDefault)
        .Bookmarks.Add "Point_1", wdRng
              'Save the document
        .SaveAs2 Filename:=wdPath & "Timesheets  " & Format(Now, "(mmm)") & ".docx", _
              FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        End With
         With wdApp
                 .Visible = True
                 .ActiveWindow.WindowState = wdWindowStateNormal
                 .Left = 300
                 .Top = 15
                 .Width = 500
                 .Height = 600
                 .Activate
          End With
        Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub
    Last edited by jjhs; 2014-02-28 at 06:26.

  2. #2
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    860
    Thanks
    0
    Thanked 10 Times in 9 Posts
    This from the MS article on SaveAs2: "If a document with the specified file name already exists, the document is overwritten without prompting the user." You can write code before the SaveAs2 method to determine if the file exists, e.g. If Not Dir(strFileName) <> "" Then....

  3. #3
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you Richard for your reply.

    Have placed your snippet at beginning of my code so can now establish if file already exist, with this

    Code:
    		' Declared variables
    Dim wdApp As Word.Application
     Dim wdDoc As Word.Document
     Dim wdRng As Word.Range
     Dim wdPath As String
     Dim strFilename As String
            
    wdPath = "C:\Users\Me\ Documents\My Doc\ServiceTimes\2014 Timesheets\"
    strFilename = wdPath & "Timesheets # " & Format(Now, "(mmm)") & ".docx"     
      
    	       If Not Dir(strFilename) <> "" Then
              	 MsgBox " File DOESN'T exist. This will create it"
           Else
               	 MsgBox "File ALREADY exist."
          End If
                
             	** rest of code here**
    So now I know it exist or not, thought it was then just simple matter of altering the messagebox arguments around a bit and use something along these lines;

    Code:
    'MsgBox ("File ALREADY exist. Do you want to Over Write it", vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
    But of course things are never that simple are they! I believe my logic behind all this is messed up, and causing me to go around in circles looking for the answer. As you can tell I have clearly not grasped this, where is the flaw in my thinking?

  4. #4
    New Lounger
    Join Date
    Feb 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Below is how it turned out.
    In the end; took the “Not” out of If Not Dir(strFilename), only because it was easier to handle this way around, couple of extra
    If’s and that was it.

    Code:
    Sub CommandButton1_Click()
    	       'step1: Declare your variables
     Dim wdApp As Word.Application
     Dim wdDoc As Word.Document
     Dim wdRng As Word.Range
     Dim wdPath As String
     Dim strFilename As String   
                wdPath = "C:\Users\Me\Documents\My Doc\ ServiceTimes\2014 Timesheets \Timesheets\"
                strFilename = wdPath & "Timesheets # " & Format(Now, "(mmm)") & ".docx"
                	'step2: Choose what you want to do
          If Dir(strFilename) <> "" Then
             If MsgBox(" The file already exists, do you want to Overwrite it?", vbYesNo + vbExclamation + vbDefaultButton2) = vbNo     Then Exit Sub
                Else
             If MsgBox(" You want to CREATE a new file ", vbYesNo + vbDefaultButton1) = vbNo Then Exit Sub
           End If
    	        'step3: Selects the Range to Hide
    
    		** rest of code here**

Posting Permissions

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