In a previous post, I have described how to get the url from a sync onedrive file location, but the solution wasn't perfect and needed user intervention to correct the path in case a subfolder was sync'ed (instead of the root level of the document library).
The AutoHotkey script simply fill the first column and let the second column be calculated.
Ctrl+C to copy the file in the File Explorer (Synced location).
Solution 1 using temporary Excel files
Idea
The workaround to get the right sync url is to create a temporary Excel file at the sync location and let evaluate the formula:
Credit implementation idea @guwidoe
AutoHotkey script
This is implemented in the ahk/Lib/SharePoint.ahk -> SharePoint_UpdateSync()
SharePoint_UpdateSync(){
; SharePoint_UpdateSync()
; Update SPSync.ini file creating temporary Excel files in each sync directory
sIniFile := SharePoint_GetSyncIniFile()
FileRead, IniContent, %sIniFile%
oExcel := ComObjCreate("Excel.Application")
oExcel.Visible := False ; DBG
oExcel.DisplayAlerts := false
Loop, Reg, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive, K
{
RegRead MountPoint, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive\%A_LoopRegName%, MountPoint
MountPoint := StrReplace(MountPoint,"\\","\")
; Exclude Personal OneDrive
If InStr(MountPoint,"\OneDrive -")
Continue
FoundPos := InStr(MountPoint, "\" , , -1)
sOneDriveDir = SubStr(MountPoint,1,FoundPos-1)
If InStr(IniContent,MountPoint . A_Tab) ; already mapped
Continue
xlFile := MountPoint . "\SPsync.xlsx"
; Create Excel file under MountPoint
xlWorkbook := oExcel.Workbooks.Add ;add a new workbook
oSheet := oExcel.ActiveSheet
oSheet.Range("A1").Formula := "=CELL(""filename"")" ; escape quotes
oSheet.Range("A1").Dirty
; Save Workbook https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
Try ; sometimes return error "Enable to get the SaveAs property" but still work
xlWorkbook.SaveAs(xlFile,xlWorkbookDefault := 51)
; Calculate Formula
oSheet.Calculate
; Get value
UrlNamespace := oSheet.Range("A1").Value
;Find last /
FoundPos:=InStr(UrlNamespace,"/",,0)
UrlNamespace := SubStr(UrlNamespace,1,FoundPos-1)
xlWorkbook.Close(False)
; Delete temp file
FileDelete, %xlFile%
If (UrlNamespace = "") {
sTrayTip := "Error in getting url path for synced location '" . MountPoint . "'!"
TrayTip Check Mapping in SPsync.ini! , %sTrayTip%,,0x2
Run "%sIniFile%"
}
FileAppend, %MountPoint%%A_Tab%%UrlNamespace%`n, %sIniFile%
} ; end Loop
N.B.: To get oSheet.Recalculate to work you have to set the Range Dirty. (Thanks GWD)
This solution requires creating and deleting temporary Excel files but works quite reliably.
Alternative Solution based on VBA macro
Idea
It relies on a preset macro-enabled SPSyncIni.xlsm file that includes this VBA library mentioned here.
The file also include a Table with a formula calling the GetWebPath function:
The AutoHotkey script simply fill the first column and let the second column be calculated.
AutoHotkey Script
Implemented in ahk/Lib/SharePoint.ahk -> SharePoint_UpdateSyncVBA()
; -------------------------------------------------------------------------------------------------------------------
SharePoint_UpdateSyncVBA(xlFile := ""){
; SharePoint_UpdateSyncVBA()
; Update SPSync.ini file using SPSyncIni.xlsm with VBA code
; based on VBA macro: https://gist.github.com/guwidoe/6f0cbcd22850a360c623f235edd2dce2
If (xlFile = "") {
xlFile := A_ScriptDir . "\SPSyncIni.xlsm"
}
oExcel := ComObjCreate("Excel.Application")
oExcel.Visible := True ; DBG
oExcel.DisplayAlerts := false
xlWorkbook := oExcel.Workbooks.Open(xlFile) ; Open xlFile
oSheet := oExcel.ActiveSheet
;oSheet.Range("A1").Formula := "=CELL(""filename"")" ; escape quotes
tbl := oSheet.ListObjects("SPSync")
; Reset Table - but keep formulas: https://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table
; tbl.DataBodyRange.Delete
If (tbl.ListRows.Count >= 2)
tbl.DataBodyRange.Offset(1, 0).Resize(tbl.DataBodyRange.Rows.Count - 1, tbl.DataBodyRange.Columns.Count).Delete
rowCounter := 0
Loop, Reg, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive, K
{
RegRead MountPoint, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive\%A_LoopRegName%, MountPoint
MountPoint := StrReplace(MountPoint,"\\","\")
; Exclude Personal OneDrive
If InStr(MountPoint,"\OneDrive -")
Continue
rowCounter := rowCounter + 1
If rowCounter > 1 ; Create new row and paste formats and formula
tbl.ListRows.Add ; Will copy previous row with same formatting and formulas
tbl.DataBodyRange.Cells(rowCounter, 1).Value := MountPoint
; Takes pretty long for first evaluation
} ; end Loop
; Save Workbook https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
; Save as CSV file
sCsvFile := StrReplace(xlFile,".xlsm",".csv")
FileDelete, %sCsvFile%
xlWorkbook.SaveAs(sCsvFile,xlCSV := 6)
xlWorkbook.Close(False)
oExcel.Quit()
; Convert CSV to ini file
sIniFile := A_ScriptDir . "\PowerTools.ini"
IniDelete, %sIniFile%, SPsync
FileRead, IniContent, %sCsvFile%
IniContent := StrReplace(IniContent,",","=")
IniContent := StrReplace(IniContent,"`r","")
; delete header line
pos:=InStr(IniContent,"`n")
IniContent:=SubStr(IniContent,pos+1)
IniWrite, %IniContent%, %sIniFile%, SPsync
;Run, "%sIniFile%"
FileDelete, %sCsvFile%
} ; eofun
Bottom Line
Drawback of this solution is that the first time the function is called, Excel takes quite a few seconds to get it calculated. (It looks like Excel is hanging.) I guess because behind the seen it is doing the mapping calculation.
Advantages of the approach is that you get all the mapping in one file and only call the Excel comobj interface for one file. Also you have no leftovers/temporary files to clean-up.
Still for reason of speed I prefer to use the first solution.
PowerTool usage
Once the mapping between sync'ed location and SharePoint url is stored in the SPSync.ini file, you can then conveniently convert a local path to a file in a OneDrive sync'ed location to its SharePoint url.
It is implemented in the SharePoint_Sync2Url function.
It is used mainly in the NWS PowerTool IntelliPaste function:
Ins (IntelliPaste Hotkey) to IntelliPaste it:
it will paste a rich text link to the file SharePoint location (instead of the local path). You can choose also a breadcrumb format.
It is convenient to point explictly the reader to where the file is located. But note that such file links are not robust to file moving/ renaming (contrary to link generated via the Share or Copy link OneDrive menu.)
To trigger the update of the SPSync.ini file (e.g. if you have added or changed your sync'ed locations), you can go in the NWS PowerTool System Tray icon menu under Settings-> IntelliPaste-> Update SharePoint Sync Ini
No comments:
Post a Comment