How can I autosave to a sharepoint file using a excel macro when saving workbook to normal location.
I run excel 2016 on a server and im trying to autorun a macro when the save button is clicked on the workbook I have open or if easier run it when workbook closed.
I need it to save the workbook thats open as normal to location it is at and also run the following macro in the background so people dont have to save twice.
ActiveWorkbook.SaveAs Filename:= _
"xxxs://arrivagroup.sharep
, FileFormat:=xlOpenXMLWorkb
End Sub
What im also trying to do is when it saves it to the sharepoint there will already be a file with the same name in it and so I need it overwritten with new file it saving,.
I have had a look at doing it via autosave into one drive but this dont work.
Any Ideas?
Thanks fabrice. Can I ask how that would be in a macro as im completely new and only know the extreme basics to them.
Sorry I think im doing something wrong can I just check.
Ive copied the macro into the workbook module of the file I want to copy and have done as follows, Im saving to teams instead of sharepoint now if that makes any difference.
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
wb.SaveCopyAs "https://teams.microsoft.com/_#/files/General?threadId=19%3Aebed8ec0027d460bbdd74a4248193283%40thread.skype&ctx=channel"
End Sub
I save the workbook and then I test it by just typing something in and saving it using the save button on the workbook but it don't seem to be copying over to the teams website.
What am I doing wrong?
If you use the Workbook_AfterSave event sub, the workbook's location will change from its normal location to SharePoint.Cept it isn't the event handler that relocate the workbook, but the SaveAs operation.
If instead you use the Workbook_BeforeSave event sub, your save to the SharePoint location will precede the save to the normal location.
The SaveCopyAs operation do not relocate the workbook.
I think in 90% there and just need a bit of advice on it keep repeating.
I have this code in the area called "this workbook"
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Call Macro1
End Sub
And this is macro 1
ActiveWorkbook.SaveAs Filename:= _
"https://xxrivagroup.sharepoint.com/teams/PASSACC/Shared%20Documents/General/shrek%20summary.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
The issue I have now is that the macro keeps on repeating its self and saving all the time and the only way out is to push esc key.
Byundt
in relation to what you said
instead you use the Workbook_BeforeSave event sub, your save to the SharePoint location will precede the save to the normal location. You may save the file repeatedly, and never lose sight of your normal save location. The code for the Workbook_BeforeSave event sub will be the same as Fabrice Lambert was suggesting for Workbook_AfterSave--except that the first line of the event sub will be different
I changed the code to as follows but now comes up with complie error. procedure declaration does not match description of event.....
Option Explicit
Private Sub Workbook_BeforeSave(ByVal Success As Boolean)
Dim wb As Excel.Workbook
Set wb = ThisWorkbook
wb.SaveCopyAs "https://arrivagroup.sharepoint.com/teams/PASSACC/Shared%20Documents/General/Cubic%20TVM/OLD%20LENNON%20DATA/loomis%20bag%20order99.xlsm"
End Sub
Massive big thanks
As its name suggest, the saving operation has already been done, and does not need to be performed again, else it enter into an infinite loop.
It does not need to close the workbook either, this is not its responsibility, this have to be done elsewhere.
All your event handler need to do, is to "clone" your workbook (save a copy).
Dito for the BeforeSave event, it does not need to save the workbook itself as the saving operation will be performed once the event is done.