Private Function LocalPathFromSharePoint(ByVal p As String) As String
Const URL_ROOT As String = _
"https://woodsideenergy.sharepoint.com/sites/OTTeam/Documents/"
Dim base As String
Dim rest As String
' Already a local path: return as-is
If Left$(LCase$(p), 4) <> "http" Then
LocalPathFromSharePoint = p
Exit Function
End If
base = Environ$("OneDriveCommercial")
If Len(base) = 0 Then base = Environ$("OneDrive")
If InStr(1, p, URL_ROOT, vbTextCompare) = 1 Then
rest = Mid$(p, Len(URL_ROOT) + 1)
rest = Replace(rest, "%20", " ") ' decode spaces if present
rest = Replace(rest, "/", "\")
LocalPathFromSharePoint = _
base & "\Documents - OT Team\" & rest
Else
' Unknown site: fall back somewhere safe
LocalPathFromSharePoint = _
Environ$("USERPROFILE") & "\Downloads"
End If
End Function
Private Function JsonEscape(ByVal s As String) As String
' Order matters: backslash first
s = Replace(s, "\", "\\")
s = Replace(s, """", "\""")
s = Replace(s, vbCrLf, "\n") ' before the individual CR/LF passes
s = Replace(s, vbCr, "\n")
s = Replace(s, vbLf, "\n") ' Excel in-cell breaks are vbLf
s = Replace(s, vbTab, "\t")
' Remaining control chars (rare, but bulletproof)
Dim i As Long, c As Long, out As String
For i = 1 To Len(s)
c = AscW(Mid$(s, i, 1))
If c < 32 Then
out = out & "\u" & Right$("000" & Hex$(c), 4)
Else
out = out & Mid$(s, i, 1)
End If
Next i
JsonEscape = out
End Function
Private Sub WriteUtf8(ByVal filePath As String, ByVal content As String)
Dim stm As Object
Set stm = CreateObject("ADODB.Stream")
stm.Type = 2 ' text
stm.Charset = "utf-8"
stm.Open
stm.WriteText content
stm.SaveToFile filePath, 2 ' overwrite
stm.Close
End Sub
Public Sub ExportFormulasAndValues()
Dim wks As Worksheet
Dim cell As Range
Dim json As String
Dim firstItem As Boolean
Dim filePath As String
Dim folder As String
folder = LocalPathFromSharePoint(ThisWorkbook.Path)
Set wks = ActiveSheet
json = "[" & vbCrLf
firstItem = True
For Each cell In Selection.Cells
If Not firstItem Then
json = json & "," & vbCrLf
End If
json = json & " {" & vbCrLf
json = json & " ""sheet"": """ & JsonEscape(wks.Name) & """," & vbCrLf
json = json & " ""cell"": """ & cell.Address(False, False) & """," & vbCrLf
json = json & " ""value"": """ & JsonEscape(CStr(cell.Value)) & """," & vbCrLf
json = json & " ""formula"": """ & JsonEscape(cell.Formula) & """" & vbCrLf
json = json & " }"
firstItem = False
Next cell
json = json & vbCrLf & "]"
' Verify the mapped folder actually exists
If Dir$(folder, vbDirectory) = "" Then
folder = Environ$("USERPROFILE") & "\Downloads"
End If
filePath = folder & "\formulas_values.json"
WriteUtf8 filePath, json
MsgBox "JSON exported to:" & vbCrLf & filePath
End SubWe then use python to copy the values to another spreadsheet structured the same:
import json
import os
from datetime import datetime
def load_json_flexible(json_path):
"""
Load JSON trying common Windows encodings.
utf-8-sig handles both plain UTF-8 and
BOM-prefixed files.
"""
last_error = None
for enc in ("utf-8-sig", "cp1252", "latin-1"):
try:
with open(json_path, "r", encoding=enc) as fp:
return json.load(fp)
except UnicodeDecodeError as ex:
last_error = ex
continue
raise ValueError(
f"Could not decode {json_path} "
f"with any known encoding: {last_error}"
)
def GetWin32comObject(object_name='Excel.Application'):
from win32com import client
try:
obj = client.gencache.EnsureDispatch(object_name)
except AttributeError:
import re
import sys
import shutil
module_list = [
m.__name__
for m in sys.modules.values()
if m
]
for module in module_list:
if re.match(r'win32com\.gen_py\..+', module):
del sys.modules[module]
gen_py = os.path.join(
os.environ.get('LOCALAPPDATA'),
'Temp',
'gen_py'
)
if os.path.exists(gen_py):
shutil.rmtree(gen_py)
obj = client.gencache.EnsureDispatch(object_name)
return obj
def update_workbook_from_json(
workbook_path,
json_path,
save_as=None
):
"""
Update workbook from JSON and
create an audit file.
If save_as is provided, the copy is saved
as a macro-free .xlsx workbook.
"""
records = load_json_flexible(json_path)
audit_entries = []
excel = GetWin32comObject()
excel.Visible = False
excel.DisplayAlerts = False
workbook = excel.Workbooks.Open(
os.path.abspath(workbook_path)
)
try:
updates = 0
for record in records:
sheet_name = record.get("sheet")
cell_ref = record.get("cell")
new_value = record.get("value")
if not sheet_name:
continue
if not cell_ref:
continue
try:
ws = workbook.Worksheets(sheet_name)
rng = ws.Range(cell_ref)
old_value = rng.Value
#
# Skip unchanged values
#
if str(old_value) == str(new_value):
continue
rng.Value = new_value
audit_entries.append({
"timestamp":
datetime.now().isoformat(),
"sheet":
sheet_name,
"cell":
cell_ref,
"old_value":
old_value,
"new_value":
new_value
})
updates += 1
print(
f"Updated "
f"{sheet_name}!{cell_ref}"
)
except Exception as ex:
audit_entries.append({
"timestamp":
datetime.now().isoformat(),
"sheet":
sheet_name,
"cell":
cell_ref,
"error":
str(ex)
})
#
# Save workbook
#
XL_XLSX = 51 # xlOpenXMLWorkbook: macro-free .xlsx
if save_as:
#
# Force .xlsx extension to match the
# macro-free format (Excel raises a COM
# error if format and extension disagree)
#
save_path = os.path.abspath(save_as)
root, ext = os.path.splitext(save_path)
if ext.lower() != ".xlsx":
save_path = root + ".xlsx"
workbook.SaveAs(
save_path,
FileFormat=XL_XLSX
)
workbook_name = save_path
else:
workbook.Save()
workbook_name = workbook_path
finally:
#
# Both branches save explicitly above, so
# close without saving. This also leaves the
# source workbook untouched if an exception
# occurred mid-update.
#
workbook.Close(SaveChanges=False)
excel.Quit()
#
# Save audit trail
#
audit_file = os.path.join(
os.path.dirname(workbook_name),
"barriers_audit.json"
)
with open(
audit_file,
"w",
encoding="utf-8"
) as fp:
json.dump(
audit_entries,
fp,
indent=2,
ensure_ascii=False
)
print(
f"\nUpdated {updates} cells"
)
print(
f"Audit log written to:\n"
f"{audit_file}"
)
if __name__ == "__main__":
update_workbook_from_json(
workbook_path=r"<path>",
json_path=r"<path>",
save_as=r"<path>"
)