Adding VBA script to file created from scratch in a react app #2812

Closed
opened 2022-10-17 13:28:12 +00:00 by ankitamhatre04 · 4 comments

I'm trying to achieve same results as this thread but vbablob = wb.vbaraw;doesn't seem to be working

I'm manually saving the locally extracted vba.bin in S3 bucket and then trying to fetch it, to attach to the new excel workbook generated using const workbook = xlsx.utils.book_new();

On downloading the xlsm using writeFile, I'm unable to see the validation/ vba macros on the file

On debugging, I see that the vba.bin is in fact successfully added to the workbook.
Screen Shot 2022-10-17 at 5 49 32 AM

My export function flow is same as suggested above with exception of reading file from s3

  const onClick = async() => { // <-- async function
    try {
      const workbook = xlsx.utils.book_new();

      const lookupValues = generateLookupValueRows();
      const lookupValuesSheet = xlsx.utils.json_to_sheet(lookupValues);
      xlsx.utils.book_append_sheet(workbook, lookupValuesSheet, `Lookup Values`);

      const templateRows = generateTemplateRows();
      const templateSheet = xlsx.utils.json_to_sheet(templateRows);
      xlsx.utils.book_append_sheet(workbook, templateSheet, `Template Input`);

      // want to attach a VBA blob to the workbook before writing the file
      var vba = new Uint8Array(await (await fetch("s3 bucket public url/vba.bin")).arrayBuffer()) // <-- get vba blob
      workbook.vbaraw = vba; // <-- add to file
      xlsx.writeFile(workbook, `Template.xlsm`, {bookVBA: true}); // <-- use XLSM, set bookVBA true
    } catch (err) {
      console.log('Error downloading template');
    }
 };

Any pointers would be helpful! Thanks!

I'm trying to achieve same results as [this thread](https://github.com/SheetJS/sheetjs/issues/2805) but `vbablob = wb.vbaraw;`doesn't seem to be working I'm manually saving the locally extracted vba.bin in S3 bucket and then trying to fetch it, to attach to the new excel workbook generated using ` const workbook = xlsx.utils.book_new();` On downloading the xlsm using writeFile, I'm unable to see the validation/ vba macros on the file On debugging, I see that the vba.bin is in fact successfully added to the workbook. ![Screen Shot 2022-10-17 at 5 49 32 AM](https://user-images.githubusercontent.com/115994980/196181284-5d36cbee-71fb-4730-9e5f-90550dba9c76.png) My export function flow is same as suggested above with exception of reading file from s3 ``` const onClick = async() => { // <-- async function try { const workbook = xlsx.utils.book_new(); const lookupValues = generateLookupValueRows(); const lookupValuesSheet = xlsx.utils.json_to_sheet(lookupValues); xlsx.utils.book_append_sheet(workbook, lookupValuesSheet, `Lookup Values`); const templateRows = generateTemplateRows(); const templateSheet = xlsx.utils.json_to_sheet(templateRows); xlsx.utils.book_append_sheet(workbook, templateSheet, `Template Input`); // want to attach a VBA blob to the workbook before writing the file var vba = new Uint8Array(await (await fetch("s3 bucket public url/vba.bin")).arrayBuffer()) // <-- get vba blob workbook.vbaraw = vba; // <-- add to file xlsx.writeFile(workbook, `Template.xlsm`, {bookVBA: true}); // <-- use XLSM, set bookVBA true } catch (err) { console.log('Error downloading template'); } }; ``` Any pointers would be helpful! Thanks!
Owner

Let's start from a simple macro that shows a message on open.

Private Sub Workbook_Open()
MsgBox "Hello World"
End Sub

Attached HelloWorld.xlsx is the original file (rename to .xlsm and open to verify the contents)

var XLSX = require("xlsx"), fs = require("fs");
var wb = XLSX.readFile("HelloWorld.xlsm", { bookVBA: true });
var vbablob = wb.vbaraw;
fs.writeFileSync("vba.bin", vbablob);

Attached vba.zip is the VBA blob (rename to .bin)

.

For NodeJS, save the following to issue2812.js and run node issue2812.js:

var XLSX = require("xlsx"), fs = require("fs");
var wb = XLSX.utils.book_new();
var ws = XLSX.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
wb.vbaraw = fs.readFileSync("vba.bin");
XLSX.writeFile(wb, "issue2812.xlsm", { bookVBA: true });

Attached issue2812.xlsx is the generated file in NodeJS (rename to .xlsm and open)

.

For the browser, save the following to issue2812.html, start a local web server with npx http-server . and load the page http://127.0.0.1:8080/issue2812.html :

<body>
<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-0.18.12/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
var wb = XLSX.utils.book_new();
var ws = XLSX.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
wb.vbaraw = new Uint8Array(await (await fetch("vba.bin")).arrayBuffer());
XLSX.writeFile(wb, "issue2812.xlsm", { bookVBA: true });
})();
</script>

The generated file in the browser is identical to the NodeJS file.

.

If you can run the steps here and it works correctly, it'd be useful to look at the actual data coming from S3. Is that blob the same as the uploaded file?

Let's start from a simple macro that shows a message on open. ```vb Private Sub Workbook_Open() MsgBox "Hello World" End Sub ``` Attached `HelloWorld.xlsx` is the original file (rename to `.xlsm` and open to verify the contents) ```js var XLSX = require("xlsx"), fs = require("fs"); var wb = XLSX.readFile("HelloWorld.xlsm", { bookVBA: true }); var vbablob = wb.vbaraw; fs.writeFileSync("vba.bin", vbablob); ``` Attached `vba.zip` is the VBA blob (rename to `.bin`) . For NodeJS, save the following to `issue2812.js` and run `node issue2812.js`: ```js var XLSX = require("xlsx"), fs = require("fs"); var wb = XLSX.utils.book_new(); var ws = XLSX.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); wb.vbaraw = fs.readFileSync("vba.bin"); XLSX.writeFile(wb, "issue2812.xlsm", { bookVBA: true }); ``` Attached `issue2812.xlsx` is the generated file in NodeJS (rename to .xlsm and open) . For the browser, save the following to `issue2812.html`, start a local web server with `npx http-server .` and load the page http://127.0.0.1:8080/issue2812.html : ```html <body> <script lang="javascript" src="https://cdn.sheetjs.com/xlsx-0.18.12/package/dist/xlsx.full.min.js"></script> <script> (async() => { var wb = XLSX.utils.book_new(); var ws = XLSX.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); wb.vbaraw = new Uint8Array(await (await fetch("vba.bin")).arrayBuffer()); XLSX.writeFile(wb, "issue2812.xlsm", { bookVBA: true }); })(); </script> ``` The generated file in the browser is identical to the NodeJS file. . If you can run the steps here and it works correctly, it'd be useful to look at the actual data coming from S3. Is that blob the same as the uploaded file?
Author

Thank you for a quick response! Yes, I'm able to run all the steps listed above and they work as expected for your file but for some reason it doesn't work with my xlsm file.
I'm able to fetch your vba.bin, save it to S3 and attach it to the workbook generate in the browser, and see a hello world in the message box when I open it.

I'm attaching the vba script (replace .zip with .bin) and the excels I'm using to better understand what might be going wrong, why the macros fails to work

Does my file use unsupported features?

This is the macro I'm trying to attach to the workbook

'Initialization of global variables, validations, plus other one-time activities when Excel file is loaded
Private Sub Workbook_Open()
    Dim wb As Workbook
    Dim shtMeta As Worksheet
    Dim shtInput As Worksheet
    Dim shtLookup As Worksheet
    Dim lastCell As Range
    Dim strRange As String
    Dim strLookups As String
    Dim bottomCell As Range
    Dim ColumnLetter As String
    Dim lastRow As Long
    Dim i As Long
    ' useful info on how to interact with ranges on a worksheet:
    ' https://stackoverflow.com/questions/27802286/vba-getting-run-time-1004-method-range-of-object-worksheet-failed-when-us
        
    Application.EnableEvents = False
        
    'assign temporary variables used later with initializations
    Set wb = ActiveWorkbook
    Set shtInput = wb.Sheets("Template Input")
    Set shtLookup = wb.Sheets("Lookup Values")
    Set shtMeta = wb.Sheets("MetaData")
    Set lastCell = shtMeta.Cells.SpecialCells(xlCellTypeLastCell)
    
    'Global variable initialization
    g_columnCount = lastCell.Column
    g_rowLast = ActiveCell.Row
    
    'Initialization of input cell validations
    'e.g. 'Lookup Values'!$G$2:$G$17
    For i = 1 To g_columnCount
        If shtMeta.Cells(2, i) <> "Numeric" Then
            Set bottomCell = shtInput.Cells.SpecialCells(xlCellTypeLastCell)
            lastRow = shtLookup.Cells(shtLookup.Rows.Count, i).End(xlUp).Row
            'Convert To Column Letter
            ColumnLetter = Split(Cells(1, i).Address, "$")(1)
            strLookups = "='Lookup Values'!$" & ColumnLetter & "$2:$" & ColumnLetter & "$" & lastRow
            strRange = shtInput.Range(Cells(2, i), Cells(999, i)).Address
            shtInput.Range(strRange).Validation.Delete
            shtInput.Range(strRange).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlEqual, Formula1:=strLookups
        End If
    Next i
    
    Application.EnableEvents = True

End Sub

And then another one on sheet1 for dropdowns in Excel


Private Sub Worksheet_Change(ByVal Target As Range)
'Allows multiple selections in a Drop Down List in Excel
' (without repetition and sorted alphabetically)
    Dim nColumn As Long
    Dim wb As Workbook
    Dim shtMeta As Worksheet
    Dim shtInput As Worksheet
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim Defaultvalue As String
    
    Application.EnableEvents = True
    On Error GoTo Exitsub
    
    Set wb = ActiveWorkbook
    Set shtMeta = wb.Sheets("MetaData")
    Set shtInput = wb.Sheets("Template Input")
    
    If shtMeta.Cells(2, Target.Column) = "MultiSelectable" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else: 'If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
            Defaultvalue = shtMeta.Cells(4, Target.Column)
            If Target.Value = "" Then
                Newvalue = Defaultvalue
            Else
                Newvalue = Target.Value
            End If
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Or Oldvalue = Defaultvalue Then
                Target.Value = Newvalue
            Else
                If Newvalue = Defaultvalue Then
                    Target.Value = Newvalue
                Else
                    If InStr(1, Oldvalue, Newvalue) = 0 Then
                        Target.Value = Oldvalue & ", " & Newvalue
                        Dim val As String
                        Dim delim As String
                        Dim arr() As String
                        val = Replace(Target.Value, ", ", ",")
                        delim = ","
                        arr() = Split(val, delim)
                        arr = SortArrayAtoZ(arr)
                        SortCSVString = Replace(Join(arr, delim), delim, delim & " ")
                        Target.Value = SortCSVString
                    Else:
                        Target.Value = Oldvalue
                    End If
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
Exitsub:
    Application.EnableEvents = True
End Sub

'Sort input array in alphabetical order
Function SortArrayAtoZ(myArray As Variant)

Dim i As Long
Dim j As Long
Dim Temp

'Sort the Array A-Z
For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) > UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

SortArrayAtoZ = myArray

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Call Module1.SwitchActiveRow(Target)
    Application.EnableEvents = True
End Sub


It'd be great if we could identify if some functionality is currently unsupported

Thank you for taking the time

Thank you for a quick response! Yes, I'm able to run all the steps listed above and they work as expected for your file but for some reason it doesn't work with my xlsm file. I'm able to fetch your vba.bin, save it to S3 and attach it to the workbook generate in the browser, and see a hello world in the message box when I open it. I'm attaching the vba script (replace .zip with .bin) and the excels I'm using to better understand what might be going wrong, why the macros fails to work Does my file use unsupported features? This is the macro I'm trying to attach to the workbook ``` 'Initialization of global variables, validations, plus other one-time activities when Excel file is loaded Private Sub Workbook_Open() Dim wb As Workbook Dim shtMeta As Worksheet Dim shtInput As Worksheet Dim shtLookup As Worksheet Dim lastCell As Range Dim strRange As String Dim strLookups As String Dim bottomCell As Range Dim ColumnLetter As String Dim lastRow As Long Dim i As Long ' useful info on how to interact with ranges on a worksheet: ' https://stackoverflow.com/questions/27802286/vba-getting-run-time-1004-method-range-of-object-worksheet-failed-when-us Application.EnableEvents = False 'assign temporary variables used later with initializations Set wb = ActiveWorkbook Set shtInput = wb.Sheets("Template Input") Set shtLookup = wb.Sheets("Lookup Values") Set shtMeta = wb.Sheets("MetaData") Set lastCell = shtMeta.Cells.SpecialCells(xlCellTypeLastCell) 'Global variable initialization g_columnCount = lastCell.Column g_rowLast = ActiveCell.Row 'Initialization of input cell validations 'e.g. 'Lookup Values'!$G$2:$G$17 For i = 1 To g_columnCount If shtMeta.Cells(2, i) <> "Numeric" Then Set bottomCell = shtInput.Cells.SpecialCells(xlCellTypeLastCell) lastRow = shtLookup.Cells(shtLookup.Rows.Count, i).End(xlUp).Row 'Convert To Column Letter ColumnLetter = Split(Cells(1, i).Address, "$")(1) strLookups = "='Lookup Values'!$" & ColumnLetter & "$2:$" & ColumnLetter & "$" & lastRow strRange = shtInput.Range(Cells(2, i), Cells(999, i)).Address shtInput.Range(strRange).Validation.Delete shtInput.Range(strRange).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlEqual, Formula1:=strLookups End If Next i Application.EnableEvents = True End Sub ``` And then another one on sheet1 for dropdowns in Excel ``` Private Sub Worksheet_Change(ByVal Target As Range) 'Allows multiple selections in a Drop Down List in Excel ' (without repetition and sorted alphabetically) Dim nColumn As Long Dim wb As Workbook Dim shtMeta As Worksheet Dim shtInput As Worksheet Dim Oldvalue As String Dim Newvalue As String Dim Defaultvalue As String Application.EnableEvents = True On Error GoTo Exitsub Set wb = ActiveWorkbook Set shtMeta = wb.Sheets("MetaData") Set shtInput = wb.Sheets("Template Input") If shtMeta.Cells(2, Target.Column) = "MultiSelectable" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: 'If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Defaultvalue = shtMeta.Cells(4, Target.Column) If Target.Value = "" Then Newvalue = Defaultvalue Else Newvalue = Target.Value End If Application.Undo Oldvalue = Target.Value If Oldvalue = "" Or Oldvalue = Defaultvalue Then Target.Value = Newvalue Else If Newvalue = Defaultvalue Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Dim val As String Dim delim As String Dim arr() As String val = Replace(Target.Value, ", ", ",") delim = "," arr() = Split(val, delim) arr = SortArrayAtoZ(arr) SortCSVString = Replace(Join(arr, delim), delim, delim & " ") Target.Value = SortCSVString Else: Target.Value = Oldvalue End If End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub 'Sort input array in alphabetical order Function SortArrayAtoZ(myArray As Variant) Dim i As Long Dim j As Long Dim Temp 'Sort the Array A-Z For i = LBound(myArray) To UBound(myArray) - 1 For j = i + 1 To UBound(myArray) If UCase(myArray(i)) > UCase(myArray(j)) Then Temp = myArray(j) myArray(j) = myArray(i) myArray(i) = Temp End If Next j Next i SortArrayAtoZ = myArray End Function Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Call Module1.SwitchActiveRow(Target) Application.EnableEvents = True End Sub ``` It'd be great if we could identify if some functionality is currently unsupported Thank you for taking the time
Owner

Looking at the base file, you are adding worksheet-level macros so the codenames must match up. To discover the codenames:

var XLSX = require("xlsx");
var wb = XLSX.readFile("File_WITH_macros.xlsm"); 
console.log(wb.Workbook.Sheets);

That will print an array of objects. The CodeName property of each object must align with the final result. In this case:

[
  { CodeName: 'Sheet1' },
  { CodeName: 'Sheet2' },
  { CodeName: 'Sheet3' }
]

So that structure must also be built:

var XLSX = require("xlsx"), fs = require("fs");
var wb = XLSX.readFile("File_to_add_macros_to.xlsm");
wb.vbaraw = fs.readFileSync("vba.zip");

// --- ADD THIS PART TO YOUR CODE ---
if(!wb.Workbook) wb.Workbook = {};
wb.Workbook.Sheets = [
  { CodeName: "Sheet1" },
  { CodeName: "Sheet2" },
  { CodeName: "Sheet3" },
];
// ---

XLSX.writeFile(wb, "issue2812.xlsm", {bookVBA: true});

Attached is the generated file. Inspecting the generated file, the macros appear and the code names are lining up with the actual sheets. The developer view also shows the code is the same.

PS: you can also just read the base file (the one with the macros) using the bookVBA: true option, edit the data with sheet_add_aoa or sheet_add_json, then write with the bookVBA: true option.

Looking at the base file, you are adding worksheet-level macros so the codenames must match up. To discover the codenames: ```js var XLSX = require("xlsx"); var wb = XLSX.readFile("File_WITH_macros.xlsm"); console.log(wb.Workbook.Sheets); ``` That will print an array of objects. The `CodeName` property of each object must align with the final result. In this case: ```js [ { CodeName: 'Sheet1' }, { CodeName: 'Sheet2' }, { CodeName: 'Sheet3' } ] ``` So that structure must also be built: ```js var XLSX = require("xlsx"), fs = require("fs"); var wb = XLSX.readFile("File_to_add_macros_to.xlsm"); wb.vbaraw = fs.readFileSync("vba.zip"); // --- ADD THIS PART TO YOUR CODE --- if(!wb.Workbook) wb.Workbook = {}; wb.Workbook.Sheets = [ { CodeName: "Sheet1" }, { CodeName: "Sheet2" }, { CodeName: "Sheet3" }, ]; // --- XLSX.writeFile(wb, "issue2812.xlsm", {bookVBA: true}); ``` Attached is the generated file. Inspecting the generated file, the macros appear and the code names are lining up with the actual sheets. The developer view also shows the code is the same. PS: you can also just read the base file (the one with the macros) using the `bookVBA: true` option, edit the data with `sheet_add_aoa` or `sheet_add_json`, then write with the `bookVBA: true` option.
Author

Aligning the codenames with the workbook structure worked! Thank you so much guys

Aligning the `codenames` with the workbook structure worked! Thank you so much guys
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2812
No description provided.