Alert message for duplication of a file

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,699
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have the following supplied code which i run after i have printed an invoice.
It saves a word invoice to the folder mentioned & informs me with a message box once done.

Could you advise a edit where it will only allow the save to go ahead if that invoice number does not exist.
I mean if there is an invoice number 100 already show msg box otherwise continue and save.
Just a back up so i dont overwrite an existing file.


Code: 
Private Sub Clear_Invoice_After_Printing_Click()    
    Dim objWord As New Word.Application
    'Copy the range Which you want to paste in a New Word Document as a screenshot
    Dim strFileName As String
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\Invoice " & Range("N4").Value & " " & Format(Now, "dd-mm-yyyy") & ".doc"
    Range("G3:O60").CopyPicture xlPrinter
    With objWord
        With .Documents.Add
            .Parent.Selection.Paste
            .SaveAs strFileName
            .Close
        End With
        '.Visible = True
        .Quit
    End With
    MsgBox strFileName, vbInformation, "Invoice Saved as Screenshot  Word.doc"
    
    
    Range("G13:I18").ClearContents
    Range("N14:O18").ClearContents
    Range("G27:N42").ClearContents
    Range("G13:I13").ClearContents
    Range("G49:I49").ClearContents
    Range("G48:I48").ClearContents
    Range("G47:I47").ClearContents
    Range("G46:I46").ClearContents
    Range("G45:I45").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Worksheets("INV 2").Range("N4").Value = Range("N4").Value
    Range("G13").Select
    ActiveWorkbook.Save
    
End Sub

Many thanks
 
Sort by date Sort by votes
The amended code is to keep Invoice 100 from saying it already exists because Invoice 1000 would already be in there. It wouldn't solve the problem that I'm not understanding. I don't think that the actual invoice workbook makes a difference in this case. The worksheet I'm using is actually being auto generated each time I run the second block of code in Message #9 . (I reposted it below just to make sure it's clear which one I'm talking about).

Code: 
Private Sub Clear_Invoice_After_Printing_Click()
    Dim objWord As New Word.Application
    'Copy the range Which you want to paste in a New Word Document as a screenshot
    Dim strFileName As String
    Dim strSearchName As String
    
    'Make a new workbook for test data
    Workbooks.Add.Worksheets(1).Activate
    'Setup Test Data
    Range("G3:O60").Value2 = 1
    'Setup Fake Invoice Number
    Range("N4").Value = 101
    
    strFileName = Environ$("UserProfile") & "\Desktop\Invoice " & Range("N4").Value & " " & Format(Now, "dd-mm-yyyy") & ".doc"
    strSearchName = Environ$("UserProfile") & "\Desktop\Invoice " & Range("N4").Value & " *.doc"
    Range("G3:O60").CopyPicture xlPrinter
    If Dir(strSearchName) <> vbNullString Then
        MsgBox "Invoice " & Range("N4").Value & " already exists"
    Else
        'Code here to save new file
        With objWord
            With .Documents.Add
                .Parent.Selection.Paste
                .SaveAs strFileName
                .Close
            End With
            '.Visible = True
            .Quit
        End With
        MsgBox strFileName, vbInformation, "Invoice Saved as Word.doc"
        
'        Range("G13:I18").ClearContents
'        Range("N14:O18").ClearContents
'        Range("G27:N42").ClearContents
'        Range("G13:I13").ClearContents
'        Range("G49:I49").ClearContents
'        Range("G48:I48").ClearContents
'        Range("G47:I47").ClearContents
'        Range("G46:I46").ClearContents
'        Range("G45:I45").ClearContents
'        Range("N4").Value = Range("N4").Value + 1
'        Worksheets("INV 2").Range("N4").Value = Range("N4").Value
'        Range("G13").Select
'        ActiveWorkbook.Save
    End If
   
End Sub
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
ipbr21054 said:
I have shared a copy of the sheet here.


DR22
Click to expand...
In that workbook you have 2 macros named Clear_Invoice_After_Printing_Click, one in the Sheet14 module (the INV sheet) which checks for the existence of the wildcard file name (LockeGarmin's strSearchName) and one in Sheet22 (the INV 2 sheet) which doesn't check whether the file exists. Which macro are you running?
 
Upvote 0
INV2 just has the name and address details of a user that i print the invoice out for quite often.
I have not yet applied the code to this worksheet.
Once i get the code working for INV i will then apply it also to INV2
 
Upvote 0
Hi,
Ive just cleared my cache / cookies and now i see it working.
This is the code in use shown below.

One thing ive noticed is that the message box that pops up to advise it exists is 99.9% the same as a successfull save.
Can the message box be altered so to not confuse my staff.



Code: 
Private Sub Clear_Invoice_After_Printing_Click()    Dim objWord As New Word.Application
    'Copy the range Which you want to paste in a New Word Document as a screenshot
    Dim strFileName As String
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\Invoice " & Range("N4").Value & " " & Format(Now, "dd-mm-yyyy") & ".doc"
    Range("G3:O60").CopyPicture xlPrinter
        If Dir(strFileName) <> vbNullString Then
        MsgBox strFileName & " already exists"
    Else
        'Code here to save new file
            With objWord
        With .Documents.Add
            .Parent.Selection.Paste
            .SaveAs strFileName
            .Close
        End With
        '.Visible = True
        .Quit
    End With
    MsgBox strFileName, vbInformation, "Invoice Saved as Word.doc"
    
    
    Range("G13:I18").ClearContents
    Range("N14:O18").ClearContents
    Range("G27:N42").ClearContents
    Range("G13:I13").ClearContents
    Range("G49:I49").ClearContents
    Range("G48:I48").ClearContents
    Range("G47:I47").ClearContents
    Range("G46:I46").ClearContents
    Range("G45:I45").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Worksheets("INV 2").Range("N4").Value = Range("N4").Value
    Range("G13").Select
    ActiveWorkbook.Save
    End If
   
End Sub
 
Upvote 0
Ok
Ive got it

MsgBox "FILE ALREADY EXISTS", vbCritical + vbOKOnly
 
Upvote 0
This works for me (code in sheet module for INV sheet). I have commented out the strFileName line I used for testing. It displays the strFileName variable then displays "Invoice 79 already exists".

Code: 
Private Sub Clear_Invoice_After_Printing_Click()
    Dim objWord As New Word.Application
    'Copy the range Which you want to paste in a New Word Document as a screenshot
    Dim strFileName As String
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\Invoice " & Range("N4").Value & " " & Format(Now, "dd-mm-yyyy") & ".doc"
    'strFileName = "C:\Temp\Invoice " & Range("N4").Value & " " & Format(Now, "dd-mm-yyyy") & ".doc"
    MsgBox "strFileName = " & strFileName
    
    Range("G3:O60").CopyPicture xlPrinter
    If Len(Dir(strFileName)) <> 0 Then
        MsgBox "Invoice " & Range("N4").Value & " already exists"
    Else
        'Code here to save new file
        With objWord
            With .Documents.Add
                .Parent.Selection.Paste
                .SaveAs strFileName
                .Close
            End With
            '.Visible = True
            .Quit
        End With
        MsgBox strFileName, vbInformation, "Invoice Saved as Word.doc"
        
        Range("G13:I18").ClearContents
        Range("N14:O18").ClearContents
        Range("G27:N42").ClearContents
        Range("G13:I13").ClearContents
        Range("G49:I49").ClearContents
        Range("G48:I48").ClearContents
        Range("G47:I47").ClearContents
        Range("G46:I46").ClearContents
        Range("G45:I45").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV 2").Range("N4").Value = Range("N4").Value
        Range("G13").Select
        ActiveWorkbook.Save
    End If
   
End Sub
 
Upvote 0
Hi,
Can you advise the correct way for my message box.

I have the following but its written incorrectly,i like the code above that shows the invoice number but need it attached to my message box.

Code: 
MsgBox "FILE WAS NOT SAVED AS THE INVOICE NUMBER ALLREADY EXISTS " & Range("N4").Value vbCritical + vbOKOnly
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
523
glaccounting
G
I
  • Question Question
only one code executes after i merged two codes together
Replies
4
Views
740
ipbr21054
I
I
  • Question Question
Run Time Error 13 Type Mismatch
2
Replies
10
Views
1K
DanteAmor
DanteAmor
I
  • Question Question
Existing code needs an edit
Replies
8
Views
749
ipbr21054
I
V
  • Question Question
Invoice Creation
Replies
4
Views
652
Yongle
Yongle

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top