Well I’ve had some time to spend on this little conundrum and think I’ve found a solution. It’s not the tidiest and I’d welcome any suggestions of other methods to do this.
Basically the problem is that the SharePoint web based dialogue appears before the Custom Properties are re-freshed in Excel 2003. There doesn’t appear to be any method available to re-fresh the properties. These properties only refresh AFTER the file has been through the pre-save (of which the SharePoint form is part) and just before the actual save.
So, how do we force a refresh, well my solution is to utilise the temporary folder within windows and to save a quick copy of the file there, prior to pushing it to SharePoint. This has the added benefit of NOT showing the custom properties web form which suits my application just fine.
First thing required is a reference to the Microsoft Scripting Runtime, This gives you access to the FileSystemObject from your code.
Public fso as New FileSystemObject
Then, where ever you want to place your save code (Mine is off a command button on a seperate control panel with the normal menu’s all hidden), the following code will retrieve the temporary fold and generate a random filename prior to the SharePoint save command.
Dim f As Folder
Dim fi As String
Set f = fso.GetSpecialFolder(TemporaryFolder)
fi = fso.GetTempName()
ThisWorkbook.SaveAs f & "\" & fi
ThisWorkbook.SaveAs "http://dev-esserviceorders/Meta%20Data%20Test/test2.xls"
As I said, It’s not the tidiest solution, but it works for now albeit adding a fair save time. But it does need some error checking adding as well.
In the meantime, I’m going to keep looking for a better way of refreshing those custom properties..