RAECH
03-25-2021, 05:32 AM
Hi Everyone
I have some issues with the SaveCopyAs method and the use of SharePoint.
I have a master file for following up on energy consumption and more on our factories. Once in a while we need to update it and prepare new files to send out to the factories. I therefore need to create a copy of the workbook and save it with a new name for each plant.
As we are moving everything to SharePoint I found the (common?) issues of SaveCopyAs that comes with that.. But so far not a proper fix for it.
My code now: This code has been working locally for years, but not with files located on SharePoint.
PathTo= SharePoint folder path
GiveName = File name (In this example "Energy 20_21"
Sub PrepareFile()
ActiveWorkbook.SaveCopyAs Filename:=Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm"
Workbooks.Open Filename:=Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm"
Set NewWB = ActiveWorkbook
NewWB.Worksheets("Indtastning").Activate
Range("Plant").Value = PlantName
Call ClearAllButOne
Application.Run ("'" & Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm'!Skjul")
Application.Run ("'" & Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm'!Fabriksvisning")
ActiveWorkbook.Close savechanges:=True
ThisWB.Activate
End Sub
Does anyone have a workaround? Or do I need to do something like Copy my sheets into a new workbook and save that one - for each of our 10 plants?
Best regards
Rasmus
I have some issues with the SaveCopyAs method and the use of SharePoint.
I have a master file for following up on energy consumption and more on our factories. Once in a while we need to update it and prepare new files to send out to the factories. I therefore need to create a copy of the workbook and save it with a new name for each plant.
As we are moving everything to SharePoint I found the (common?) issues of SaveCopyAs that comes with that.. But so far not a proper fix for it.
My code now: This code has been working locally for years, but not with files located on SharePoint.
PathTo= SharePoint folder path
GiveName = File name (In this example "Energy 20_21"
Sub PrepareFile()
ActiveWorkbook.SaveCopyAs Filename:=Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm"
Workbooks.Open Filename:=Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm"
Set NewWB = ActiveWorkbook
NewWB.Worksheets("Indtastning").Activate
Range("Plant").Value = PlantName
Call ClearAllButOne
Application.Run ("'" & Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm'!Skjul")
Application.Run ("'" & Range("PathTo").Value & Range("GiveName").Value & " - " & PlantName & ".xlsm'!Fabriksvisning")
ActiveWorkbook.Close savechanges:=True
ThisWB.Activate
End Sub
Does anyone have a workaround? Or do I need to do something like Copy my sheets into a new workbook and save that one - for each of our 10 plants?
Best regards
Rasmus