asked on

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?

Fabrice Lambert

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 Jogers

ASKER

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

SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

david Jogers

ASKER

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 Lambert

Afraid I can't answer, as I'm not familiar with MS Teams.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

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 Jogers

ASKER

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 Jogers

ASKER

Massive big thanks

Fabrice Lambert

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.