Excel Ă— Microservice instead of Export/Import
How many developers could we reach with this? Potentially thousands—every Excel power user and SMB coder who needs live data, not exports.
If we make Excel talk directly to a microservice, we’re not targeting dozens—we’re unlocking a broad pool of Excel/VBA users plus web devs who live on JSON.
This approach could attract the largest adjacent talent pool we have: Excel builders, VBA tinkerers, and lightweight web developers who want live, auditable data without a full rewrite.
Excel Ă— Microservice instead of Export/Import
Direct access from Excel to DBF data via HTTP — read with Power Query, write with VBA (or Power Automate).
Excel stays the familiar frontend, while locking, validation, and audit live centrally in the microservice. Result: a single source of truth without CSV shuffling.
Why this beats export → edit → re-import
No media breaks: Live data via HTTP; refresh instead of file ping-pong.
Consistency & security: Locks, rules, type checks, audit in the service (not in countless Excel copies).
Future-proof: DBF today, SQL tomorrow — the API stays the same, no Excel rebuild needed.
Fast & cost-effective: Minimal change for SMBs — immediate benefit, no big migration.
Architecture in 3 lines
Excel (client) calls the microservice’s REST endpoints.
Reads via Power Query (GET → JSON → table), writes via VBA/Power Automate (POST/PUT).
Lock → Edit → Release protects concurrent edits; the service validates, writes, and audits (WAL-light).
1) Read in Excel (Power Query — copy & paste)
Excel → Data → From Web → Advanced, then:
BaseUrl = "
https://api.myservice.local/handlereaddbf",
Params = [
databasePath = "x:\xwhdaten\datawin\belegung.dbf",
fields = "INDEX,PLANNAME,ZIMMERNR,ANREISE,ABREISE,BUCHUNGSNR",
start = "1",
end = "500"
],
Url = BaseUrl & "?" & Uri.BuildQueryString(Params),
Source = Json.Document(
Web.Contents(
Url,
[ Headers = [ Authorization = "Bearer YOUR_TOKEN" ] ]
)
),
Records = Source[records],
Table = Table.FromRecords(Records)
in
Table
Refresh on open or on demand.
Adjust/remove the Authorization header if your API uses a different scheme.
2) Write from Excel (VBA — Lock → Edit → Release)
Alt+F11 → Insert → Module, then paste:
Option Explicit
Private Function HttpPostJson(ByVal url As String, ByVal body As String, ByVal bearer As String) As String
Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.Open "POST", url, False
http.SetRequestHeader "Content-Type", "application/json; charset=utf-8"
If Len(bearer) > 0 Then http.SetRequestHeader "Authorization", "Bearer " & bearer
http.Send body
HttpPostJson = http.ResponseText
End Function
Private Function JsonEscape(ByVal s As String) As String
s = Replace(s, "\", "\\")
s = Replace(s, """", "\""")
s = Replace(s, vbCrLf, "\n"): s = Replace(s, vbCr, "\n"): s = Replace(s, vbLf, "\n")
JsonEscape = s
End Function
Private Function ParseJson(ByVal json As String, ByVal key As String) As String
Dim pat As String: pat = """" & key & """:" & """"
Dim p As Long: p = InStr(1, json, pat, vbTextCompare)
If p = 0 Then Exit Function
p = p + Len(pat)
ParseJson = Mid$(json, p, InStr(p, json, """") - p)
End Function
'=== Example workflow: Lock -> Edit -> Release ======================
Public Sub SaveBookingRow()
Dim api As String: api = "
https://api.myservice.local"
Dim auth As String: auth = "YOUR_TOKEN"
Dim ws As Worksheet: Set ws = ActiveSheet
Dim r As Long: r = ActiveCell.Row
' Assumes column headers named "INDEX", "PLANNAME", etc.
Dim recno As Long: recno = ws.Range("INDEX").Cells(r, 1).Value
Dim bnr As String: bnr = CStr(ws.Range("BUCHUNGSNR").Cells(r, 1).Value)
' 1) Acquire lock
Dim lockBody As String, lockResp As String, token As String
lockBody = "{""databasePath"":""x:\\xwhdaten\\DATAKASS\\BOOKLOCK.dbf"",""buchungsnr"":""" & bnr & """,""rgid"":""EXCEL-CLIENT""}"
lockResp = HttpPostJson(api & "/booklock_acquire", lockBody, auth)
token = ParseJson(lockResp, "token")
If Len(token) = 0 Then
MsgBox "Lock failed: " & lockResp, vbExclamation
Exit Sub
End If
' 2) Send edit (fields taken from current Excel row)
Dim body As String, plan As String, zi As String, anr As String, abr As String
plan = JsonEscape(CStr(ws.Range("PLANNAME").Cells(r, 1).Value))
zi = JsonEscape(CStr(ws.Range("ZIMMERNR").Cells(r, 1).Value))
anr = Format(ws.Range("ANREISE").Cells(r, 1).Value, "yyyy-mm-dd")
abr = Format(ws.Range("ABREISE").Cells(r, 1).Value, "yyyy-mm-dd")
body = "{""databasePath"":""x:\\xwhdaten\\DATAWIN\\BELEGUNG.DBF""," & _
"""recordId"":" & recno & "," & _
"""fields"":{""PLANNAME"":""" & plan & """,""ZIMMERNR"":""" & zi & """,""ANREISE"":""" & anr & """,""ABREISE"":""" & abr & """}}"
Dim editResp As String
editResp = HttpPostJson(api & "/ziplan_editrecord", body, auth)
If InStr(1, editResp, """success"":true", vbTextCompare) = 0 Then
MsgBox "Save failed: " & editResp, vbCritical
End If
' 3) Release lock (idempotent)
Dim relBody As String
relBody = "{""databasePath"":""x:\\xwhdaten\\DATAKASS\\BOOKLOCK.dbf"",""buchungsnr"":""" & bnr & """,""token"":""" & token & """}"
Call HttpPostJson(api & "/booklock_release", relBody, auth)
MsgBox "Saved.", vbInformation
End Sub
Adjust: endpoint paths (/handlereaddbf, /ziplan_editrecord, /booklock_*) and field names to match your service.
No-macro alternative: Power Automate button → HTTP action → microservice.
Best practices (short)
Service-only access: Remove direct file shares; everything goes through the service.
HTTPS & tokens: Bearer/API key; store the token in Excel’s Name Manager, not in cells.
Optimistic concurrency (optional): Excel sends etag/updatedAt; service rejects on mismatch → user chooses (reload/overwrite).
After-write refresh: Auto-refresh the affected Power Query table after a save.
Audit & WAL-light: Service logs writes and supports crash replays.
TL;DR
Excel remains the UI/thinkpad, the microservice guarantees data quality (locking, validation, audit).
No more export/import routines — true live work on system-of-record data.
Future-safe: even if parts move to SQL later, the API stays, so nothing changes for Excel.
AbkĂĽrzungs-Legende
API – Interface that lets one program talk to another over defined rules.
DBF – Classic dBASE-format table file used by xBase systems.
HTTP – Web protocol used by clients and servers to send requests and responses.
JSON – Lightweight text format for structured data (key–value objects, arrays).
REST – Style of web API that uses standard HTTP methods for resources.
VBA – Visual Basic for Applications, Excel’s built-in scripting language.
SMB – Small and medium-sized businesses.
WAL – Write-ahead logging; a way to record changes before applying them for safety.
CSV – Comma-separated values; plain text table format often used for exports.
UI – User interface; the visible part users work with (e.g., Excel sheets).