我需要使用宏/VBA 代碼將當前作業簿的名稱匯出到文本檔案中的特定行/位置。
我有作業簿，其中計算是使用 Python 在外部完成的。Python 使用一個 config.json 檔案作為對哪個 Excel 檔案進行計算的參考。
每次我想在不同的作業簿上進行計算時，我都必須手動更改 config.json 檔案中的 Excel 檔案名。config.jason 檔案與 Excel 檔案位于不同的目錄中。
Excel檔案名需要進入config.jason檔案“input_file”中的“input_file”行：“D:\Temp\workbook_name.xlsm”
config.jason 檔案示例內容。
{
"columns": {
"Date": "Date",
"Product Description": "Description",
"Category": "Category",
"ID": "ID",
"Views": "Stock",
"PM": "SUMIF",
"Active Until": "Until",
"Item Decription": "DES",
"Item Category": "CAT",
},
"input_file": "D:\\Temp\\workbook_name.xlsm"
}
uj5u.com熱心網友回復：
構建 JSON 字串并使用 FileSystemObject 創建文本檔案。
Option Explicit
Sub CreateJSON()
Const JSONFILE = "config.json"
Const FOLDER = "path-to-config.json"
Const NL = vbCrLf & " " ' new line
Dim dict As Object, k
Dim json As String, filename As String, comma As String
Set dict = CreateObject("Scripting.Dictionary")
' config
dict.Add "Date", "Date"
dict.Add "Product Description", "Description"
dict.Add "Category", "Category"
dict.Add "ID", "ID"
dict.Add "Views", "Stock"
dict.Add "PM", "SUMIF"
dict.Add "Active Until", "Until"
dict.Add "Item Decription", "DES"
dict.Add "Item Category", "CAT"
' filename unix for python
filename = Replace(ThisWorkbook.FullName, "\", "/")
' build json string
json = "{" & NL & """columns"": {"
For Each k In dict.keys
json = json & comma & NL & " """ & k & """: """ & dict(k) & """"
comma = ","
Next
json = json & NL & "}," & NL & """input_file"": """ & filename & """" & vbCrLf & "}"
' write file
Dim FSO As Object, ts As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ts = FSO.CreateTextFile(FOLDER & JSONFILE)
ts.write json
ts.Close
MsgBox JSONFILE & " created in " & FOLDER, vbInformation
End Sub
uj5u.com熱心網友回復：
在單元格 A1 中：
{
"columns": {
"Date": "Date",
"Product Description": "Description",
"Category": "Category",
"ID": "ID",
"Views": "Stock",
"PM": "SUMIF",
"Active Until": "Until",
"Item Decription": "DES",
"Item Category": "CAT",
},
"input_file": "<file>"
}
替換令牌和創建/覆寫 json 檔案的代碼：
Sub RefreshJson()
PutContent "C:\temp\config.json", _
Replace(Sheet1.Range("A1").Value, "<file>", _
Replace(ActiveWorkbook.FullName, "\", "\\"))
End Sub
'create/overwrite a text file at location `f` using `content`
Sub PutContent(f As String, content As String)
CreateObject("scripting.filesystemobject"). _
opentextfile(f, 2, True).write content
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/415403.html
標籤：