La command SaveAs ne fonctionne pas, mais SaveCopyAs ne fonctionne pas

J'ai beaucoup de difficulté à get une command SaveAs pour fonctionner correctement. Pour un hôpital local, il existe des arrays de patients qui sont créés à partir d'un file model, dans lequel datatables du patient sont inputs après quoi il est renommé manuellement (en utilisant save-As) puis copié dans un autre location en tant que sauvegarde. Le model est réutilisé encore et encore.

L'objective de mon code est d'automatiser ce process. Par conséquent, je souhaite save dans deux locations différents, à partir d'un file model. Le file model ne doit pas être écrasé. Dans le model, un user définit le nom du département et le numéro de lit dans la cellule K1 et N1, de manière réfléchie. Ces champs déterminent le dossier et le nom du file dans ce dossier.

Lorsque le button Enregistrer est pressé, mon code commence à s'exécuter. J'utilise SaveCopyAs pour save le file de sauvegarde et après cela, je souhaite utiliser SaveAs pour save dans mon dossier principal. SaveAs devrait configurer ce nouveau file comme étant mon file de travail, donc ne pas écraser mon model. Au less, c'est ce que je crois …

LE PROBLÈME: lors de l'exécution de SaveAs , Excel SaveAs (sans message d'erreur clair). La chose étrange (pour moi) est que cela ne se SaveAs pas lorsque je remplace SaveAs par SaveCopyAs .

• Utilisez une search pour générer plusieurs valeurs
• Déclaration IF nestede
• Excel VBA: Manipuler la propriété Flash Shockwave
• Comment replace la virgule dans le file .xlsx dans ColdFusion?
• Problème avec mise à jour sql avec excel

LA QUESTION: Pourquoi Excel s'écrase-t-il à ce stade? Existe-t-il un moyen de corriger ou d'éviter ce comportement? Je ne trouve pas une solution appropriée qui ne modifie pas mon model. Toute aide ou suggestion est plus que bienvenue.

Le code ci-dessous est placé dans mon dossier "ThisWorkbook" et est exécuté chaque fois que je clique sur le button "Enregistrer".

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Annuler comme Boolean) Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub département = Gamme ("K1"). Valeur 'Nom du département: CHIC, THIC, ICB ou NCIC Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub bedNumber = Gamme ("N1"). Valeur N ° de lit ou numéro de pièce: lit 1. Lit 2 ou pièce 1, pièce 2. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub newFileName = department & "\" & bedNumber & ".xls" Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub Si IsEmpty (département) puis Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub MsgBox "Vous n'êtes pas entré dans un département. Veuillez réessayer." Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub ElseIf isEmpty (bedNumber) Ensuite Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub MsgBox "Vous n'êtes pas entré dans un lit ou un numéro de pièce. Veuillez réessayer." Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub ActiveWorkbook.SaveCopyAs "C: \ myBackupFolder \" + newFileName Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub ActiveWorkbook.SaveAs "C: \ myPrimaryFolder \" + newFileName 'Ne fonctionne pas Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub 'ActiveWorkbook.SaveCopyAs' C: \ myPrimaryFolder \ "+ newFileName 'Fonctionne, mais je me retrouve avec un model déguisé! Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) department = Range("K1").Value 'Name of department: CHIC, THIC, ICB or NCIC bedNumber = Range("N1").Value 'bednumber or roomnumber: Bed 1. Bed 2 or Room 1, Room 2. newFileName = department & "\" & bedNumber & ".xls" If IsEmpty(department) Then MsgBox "You haven't entered a department. Please try again." ElseIf IsEmpty(bedNumber) Then MsgBox "You haven't entered a bed or room number. Please try again." Else ActiveWorkbook.SaveCopyAs "C:\myBackupFolder\" + newFileName End If ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work 'ActiveWorkbook.SaveCopyAs "C:\myPrimaryFolder\" + newFileName 'Does work, but I end up with a messed up template! End Sub 

• Ajouter un hyperlink dans VBA UDF
• Sélectionner UsedRange dans les colonnes et les lignes dans la feuille
• utiliser la variable dans la formule excel
• Le jeu d'loggings ADODB dans VBA indique que le champ excel est vide quand ce n'est pas
• Nombre d'occurrences de dates consécutives pour le nom associé (VBA, SQL)

 Application.EnableEvents = False ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work Application.EnableEvents = True Application.EnableEvents = Faux Application.EnableEvents = False ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work Application.EnableEvents = True ActiveWorkbook.SaveAs "C: \ myPrimaryFolder \" + newFileName 'Ne fonctionne pas Application.EnableEvents = False ActiveWorkbook.SaveAs "C:\myPrimaryFolder\" + newFileName 'Doesn't work Application.EnableEvents = True