Save workbook to a specific location and override file with same name.

[Blanchetdb]

Hello

I am looking to use a command button that will save a workbook to specific location.

The Workbook is updated daily by the user and when they save, it needs to override the previous information (saved information) and NOT save as a new workbook.

I have tried various methods suggested in previous posts but unsuccessfully.

this is the path of where the document needs to be saved: C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM

any help would be greatly appreciated.

thank you

 

[jkpieterse]

If you want the workbook to keep the same name it currently has, where ever it was opened from, you can use:

Application.Displayalerts = False
ThisWorkbook.SaveCopyAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM"
Application.Displayalerts = True

Alternatively you can use this (which changes the workbook to the saved name and location):

Application.Displayalerts = False
ThisWorkbook.SaveAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM", xlOpenXMLWorkbookMacroEnabled
Application.Displayalerts = True

 

[Blanchetdb]

If you want the workbook to keep the same name it currently has, where ever it was opened from, you can use:

Application.Displayalerts = False
ThisWorkbook.SaveCopyAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM"
Application.Displayalerts = True

Alternatively you can use this (which changes the workbook to the saved name and location):

Application.Displayalerts = False
ThisWorkbook.SaveAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM", xlOpenXMLWorkbookMacroEnabled
Application.Displayalerts = True

thank you for the reply

when I try the first code, I get:

When I try the second code; It looks like it is saving (somewhere) but it didn't override the previous saved document with the same name. Our department uses a repository to save all documents and it sometimes causes issues (this may be the case here). I provided the correct location of the document within repository.

 

[jkpieterse]

In that case, please try this:

Application.Displayalerts = False
On Error Resume Next
Kill "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM"
On Error Goto 0
DoEvents
If Len(Dir("C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM")) > 0 Then
    MsgBox "Unable to remove old copy of the file!"
Else
    ThisWorkbook.SaveAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM", xlOpenXMLWorkbookMacroEnabled
End If
Application.Displayalerts = True

 

[Blanchetdb]

In that case, please try this:

Application.Displayalerts = False
On Error Resume Next
Kill "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM"
On Error Goto 0
DoEvents
If Len(Dir("C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM")) > 0 Then
    MsgBox "Unable to remove old copy of the file!"
Else
    ThisWorkbook.SaveAs "C:\Users\blanchetdb\AppData\Roaming\OpenText\DM\Temp\CFIA_ACIA-#20398545-v1-test_database.XLSM", xlOpenXMLWorkbookMacroEnabled
End If
Application.Displayalerts = True

I got the message saying that it was unable to remove the old file ... which doesn't solve my issue

 

[jkpieterse]

I'm sorry. Sometimes a solution is hard to devise without access to all resources.