API tools faq
paste
Advertisement
TCLynx

VBA Macro XL to JSON for NPC Dialogue

TCLynx
Sep 30th, 2021 (edited)
226
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.94 KB | None | 0 0
raw download clone embed print report
  1. Sub Dialogue_json_from_XL()
  2.  
  3. 'This is an example macro for Excel to be able to parse a Dialogue.diag.json file excel sheet for more complex dialogue in a minecraft
  4. 'education edition or Bedrock edition NPC. This file would be put in a dialogue folder in your beha'vior pack.
  5. 'The spreadsheet I used for this has a separate row for each scene_tag. The spreadsheet has 10 columns, not all used in this.
  6. 'column 1 = scene_tag. Column 2 = npc_name, Column 3 = text, (I did not use any On open or On close commands in my set up.)
  7. 'Column 4 = Buttons (not needed but the following are all buttons),
  8. 'Column 5 = name, Column 6 = commands, Column 7 = name, Column 8 = commands, Column 9 = name, Column 10 = commands
  9.  
  10. Dim strFile_Path As String
  11. strFile_Path = "C:\Users\exampleusername\Desktop\example.diag.json" 'fill in your path and file name between the "
  12.  
  13. Open strFile_Path For Output As #1
  14.  
  15. Dim CurrentRow As Integer
  16.  
  17. Dim MyCh
  18. MyCh = Chr(9) 'MyCh stands for My Character and Chr(9) is Tab
  19.  
  20. Print #1, "{"
  21. Print #1, MyCh & """format_version""" & ": " & """1.14""" & ","
  22. Print #1, MyCh & """minecraft:npc_dialogue""" & ": {"
  23. Print #1, MyCh & MyCh & """scenes""" & ": ["
  24.  
  25. For CurrentRow = 2 To 576 'my spreadsheet has a header row at 1 so we start at 2 and goes to row 577, we will do the last row below.
  26. 'Edit actual numbers to suite your spreadsheet. Also Below, I have three buttons per dialogue, they are the name and commands lines.
  27. 'my spreadsheet has 10 columns Cells(CurrentRow, 1) means current row Column 1
  28.  
  29. Print #1, MyCh & MyCh & MyCh & "{"
  30. Print #1, MyCh & MyCh & MyCh & MyCh & """scene_tag""" & ": " & """" & Cells(CurrentRow, 1) & """" & ","
  31. Print #1, MyCh & MyCh & MyCh & MyCh & """npc_name""" & ": " & """" & Cells(CurrentRow, 2) & """" & ","
  32. Print #1, MyCh & MyCh & MyCh & MyCh & """text""" & ": " & """" & Cells(CurrentRow, 3) & """" & ","
  33. Print #1, MyCh & MyCh & MyCh & MyCh & """buttons""" & ": ["
  34. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  35. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 5) & """" & ","
  36. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 6) & """" & "]"
  37. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "},"
  38. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  39. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 7) & """" & ","
  40. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 8) & """" & "]"
  41. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "},"
  42. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  43. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 9) & """" & ","
  44. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 10) & """" & "]"
  45. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "}"
  46. Print #1, MyCh & MyCh & MyCh & MyCh & "]"
  47. Print #1, MyCh & MyCh & MyCh & "},"
  48.  
  49. Next CurrentRow
  50.  
  51. For CurrentRow = 577 To 577 'Edit number for your file, last row separately to avoid the extra comma and then close all the brackets.
  52.  
  53. Print #1, MyCh & MyCh & MyCh & "{"
  54. Print #1, MyCh & MyCh & MyCh & MyCh & """scene_tag""" & ": " & """" & Cells(CurrentRow, 1) & """" & ","
  55. Print #1, MyCh & MyCh & MyCh & MyCh & """npc_name""" & ": " & """" & Cells(CurrentRow, 2) & """" & ","
  56. Print #1, MyCh & MyCh & MyCh & MyCh & """text""" & ": " & """" & Cells(CurrentRow, 3) & """" & ","
  57. Print #1, MyCh & MyCh & MyCh & MyCh & """buttons""" & ": ["
  58. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  59. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 5) & """" & ","
  60. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 6) & """" & "]"
  61. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "},"
  62. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  63. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 7) & """" & ","
  64. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 8) & """" & "]"
  65. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "},"
  66. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "{"
  67. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """name""" & ": " & """" & Cells(CurrentRow, 9) & """" & ","
  68. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & MyCh & """commands""" & ": [" & """" & Cells(CurrentRow, 10) & """" & "]"
  69. Print #1, MyCh & MyCh & MyCh & MyCh & MyCh & "}"
  70. Print #1, MyCh & MyCh & MyCh & MyCh & "]"
  71. Print #1, MyCh & MyCh & MyCh & "}"
  72. Print #1, MyCh & MyCh & "]"
  73. Print #1, MyCh & "}"
  74. Print #1, "}"
  75.  
  76. Next CurrentRow
  77.  
  78. Close #1
  79.  
  80. MsgBox ("Done")
  81.  
  82. End Sub
  83.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!