We help IT Professionals succeed at work.

How can I autosave to a sharepoint file using a excel macro when saving workbook to normal location.

Hi

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.sharepoint.com/teams/PASSACC/Shared%20Documents/General/Cubic%20TVM/OLD%20LENNON%20DATA/loomis%20bag%20order99.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
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?
Comment
Watch Question

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Use the workbook's After Save event. And prefer the workbook's SaveCopyAs() member function, it will prevent bad surprises (such as workbook's path beeing updated).
david Jogersaccounts

Author

Commented:

Thanks fabrice.  Can I ask how that would be  in a macro as im completely new and only know the extreme basics to them. 

 

Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
Within the VBA Editor, you'll see a Workbook module, this is where the code responsible for responding to the workbook's events will be written:
Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook
    
    wb.SaveCopyAs "xxxs://arrivagroup.sharepoint.com/teams/PASSACC/Shared%20Documents/General/Cubic%20TVM/OLD%20LENNON%20DATA/loomis%20bag%20order99.xlsm"
End Sub

Open in new window

david Jogersaccounts

Author

Commented:

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? 



Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Afraid I can't answer, as I'm not familiar with MS Teams.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you use the Workbook_AfterSave event sub, the workbook's location will change from its normal location to SharePoint. When you save it again, you will effectively save it twice in the same SharePoint location. No bueno.

If 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.

To get the path right to the Teams file location, I suggest recording a macro saving a blank workbook to Teams. You can then splice this path into the Workbook_BeforeSave code.

I am not an expert on either SharePoint or Teams, and will defer to others if that is where you are stuck. I do know Excel VBA, however, and you should definitely be using Workbook_BeforeSave.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
If you use the Workbook_AfterSave event sub, the workbook's location will change from its normal location to SharePoint.

If instead you use the Workbook_BeforeSave event sub, your save to the SharePoint location will precede the save to the normal location.
Cept it isn't the event handler that relocate the workbook, but the SaveAs operation.
The SaveCopyAs operation do not relocate the workbook.
david Jogersaccounts

Author

Commented:

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





david Jogersaccounts

Author

Commented:

Massive big thanks

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
The AfterSave event handler do not need to save the workbook itself.
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.