Persisting macros to new xlsm results in Workbook_Open method not working #615

New Issue

2017-03-30T16:34:00Z

johnothetree commented

2017-03-30 16:34:00 +00:00

(Migrated from github.com)

After the changes made for issue #606 i am now able to put the macros onto the newly written workbook, but any code in the ThisWorkbook excel object (which holds all methods relating to Workbook-wide code like Workbook_Open to do operations when you open the workbook, or Workbook_SheetChange to detect changes to any sheet in the workbook) no longer works.
From what I'm seeing, the newly-written xlsm file has a ThisWorkbook and a ThisWorkbook1, and it doesn't allow for the globals to work from ThisWorkbook, and I don't want to force end users to go into the code and move things over. I also can't delete ThisWorkbook1 from Excel's Visual Basic editor.

To show this, try doing the following:

make a new xlsm file called "buttonTest.xslm" and copy the following code into the ThisWorkbook object in the xlsm file.

Private Sub Workbook_Open()
Dim t As Range
Dim btn As Button
Set t = ActiveSheet.Range("A1:A2")
Set btn = ActiveWorkbook.ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.Caption = "Testing"
.Name = "TestButton"
End With
End Sub

put the following code into a js file to run with Node.

var XLSX = require('xlsx')
var ws_name = "connect";
var wb = XLSX.readFile("buttonTest.xlsm", {bookVBA:true}); //reads in the file as a workbook
var ws = XLSX.utils.aoa_to_sheet("Test", "123"); //array of array input, worksheet output
wb.SheetNames.push(ws_name); //add a sheet with the given sheetname
wb.Sheets[ws_name] = ws; //assign the worksheet to the sheet with given name
XLSX.writeFile(wb, "newpage.xlsm", {bookType:"xlsm"}); //write out the workbook with the given name and workbook type

run the js file and open the created xlsm file, "newpage.xlsm".

You'll see that there's a new sheet (as desired) but that the button that was supposed to be created on workbook startup doesn't show up and that there's a new object called ThisWorkbook1.

After the changes made for issue #606 i am now able to put the macros onto the newly written workbook, but any code in the ThisWorkbook excel object (which holds all methods relating to Workbook-wide code like Workbook_Open to do operations when you open the workbook, or Workbook_SheetChange to detect changes to any sheet in the workbook) no longer works. From what I'm seeing, the newly-written xlsm file has a ThisWorkbook and a ThisWorkbook1, and it doesn't allow for the globals to work from ThisWorkbook, and I don't want to force end users to go into the code and move things over. I also can't delete ThisWorkbook1 from Excel's Visual Basic editor. To show this, try doing the following: 1) make a new xlsm file called "buttonTest.xslm" and copy the following code into the ThisWorkbook object in the xlsm file. > Private Sub Workbook_Open() > Dim t As Range > Dim btn As Button > Set t = ActiveSheet.Range("A1:A2") > Set btn = ActiveWorkbook.ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height) > With btn > .Caption = "Testing" > .Name = "TestButton" > End With > End Sub 2) put the following code into a js file to run with Node. > var XLSX = require('xlsx') > var ws_name = "connect"; > var wb = XLSX.readFile("buttonTest.xlsm", {bookVBA:true}); //reads in the file as a workbook > var ws = XLSX.utils.aoa_to_sheet([["Test", "123"]]); //array of array input, worksheet output > wb.SheetNames.push(ws_name); //add a sheet with the given sheetname > wb.Sheets[ws_name] = ws; //assign the worksheet to the sheet with given name > XLSX.writeFile(wb, "newpage.xlsm", {bookType:"xlsm"}); //write out the workbook with the given name and workbook type 3) run the js file and open the created xlsm file, "newpage.xlsm". You'll see that there's a new sheet (as desired) but that the button that was supposed to be created on workbook startup doesn't show up and that there's a new object called ThisWorkbook1.

johnothetree commented

2017-03-30 16:50:24 +00:00

(Migrated from github.com)

Also, by my local testing, it doesn't make a ThisWorkbook1 object if there is no code in the ThisWorkbook object.

SheetJSDev commented

2017-03-30 16:53:22 +00:00

(Migrated from github.com)

Thanks for looking into this! The fix is very easy this time :)

The worksheets and workbook have a properties tag that maps the VBA code names to the sheet names.

If you can tinker with the source, there are two changes:

in the function write_wb_xml, add a codeName:

-	o[o.length] = (writextag('workbookPr', null, {date1904:safe1904(wb)}));
+	o[o.length] = (writextag('workbookPr', null, {date1904:safe1904(wb), codeName:"ThisWorkbook"}));

If you make just this change, you will see the ThisWorkbook1 stuff is resolved.

in the function write_ws_xml, add a sheetPr tag with a codeName:

+       o[o.length] = (writextag('sheetPr', null, {'codeName': escapexml(wb.SheetNames[idx])}));

Thanks for looking into this! The fix is very easy this time :) The worksheets and workbook have a properties tag that maps the VBA code names to the sheet names. If you can tinker with the source, there are two changes: 1) in the function `write_wb_xml`, add a `codeName`: ```diff - o[o.length] = (writextag('workbookPr', null, {date1904:safe1904(wb)})); + o[o.length] = (writextag('workbookPr', null, {date1904:safe1904(wb), codeName:"ThisWorkbook"})); ``` If you make just this change, you will see the ThisWorkbook1 stuff is resolved. 2) in the function `write_ws_xml`, add a `sheetPr` tag with a `codeName`: ```diff + o[o.length] = (writextag('sheetPr', null, {'codeName': escapexml(wb.SheetNames[idx])})); ```

SheetJSDev commented

2017-03-30 16:57:34 +00:00

(Migrated from github.com)

GitHub doesn't allow uploading XLSM files :( so you will have to download and rename to XLSM to verify:

Using XLSX 0.9.7 from NPM: broken.xlsx

Using the patches from the previous comment: fixed.xlsx

Please verify that the fixed file works. We will push the change in the next release, hopefully tomorrow.

GitHub doesn't allow uploading XLSM files :( so you will have to download and rename to XLSM to verify: Using XLSX 0.9.7 from NPM: [broken.xlsx](https://github.com/SheetJS/js-xlsx/files/883180/broken.xlsx) Using the patches from the previous comment: [fixed.xlsx](https://github.com/SheetJS/js-xlsx/files/883177/fixed.xlsx) Please verify that the fixed file works. We will push the change in the next release, hopefully tomorrow.

johnothetree commented

2017-03-30 17:07:05 +00:00

(Migrated from github.com)

It works! But it seems like Excel doesn't like it. See attached for the dialog boxes that pop up after making the changes you suggested with the code i used at the start of this post.

It works! But it seems like Excel doesn't like it. See attached for the dialog boxes that pop up after making the changes you suggested with the code i used at the start of this post. ![xlsmissue1](https://cloud.githubusercontent.com/assets/23673013/24516733/54d95f56-1541-11e7-8788-9334418968f2.PNG) ![xlsmissue2](https://cloud.githubusercontent.com/assets/23673013/24516734/54eb11ba-1541-11e7-8cad-88183a89e22f.PNG)

SheetJSDev commented

2017-03-30 18:02:15 +00:00

(Migrated from github.com)

Hmm what version of Excel are you using? I just checked the attached "fixed.xlsm" file against Excel 2016 and Excel 2007, both accept the macro and show the testing button:

Hmm what version of Excel are you using? I just checked the attached "fixed.xlsm" file against Excel 2016 and Excel 2007, both accept the macro and show the testing button: <img width="189" alt="issue615_xl2007" src="https://cloud.githubusercontent.com/assets/6070939/24518864/4682318e-1551-11e7-805e-d3a0d2063adf.png"> <img width="232" alt="issue615_xl2016" src="https://cloud.githubusercontent.com/assets/6070939/24518918/7301d2dc-1551-11e7-87e3-87e5bee05c52.png">

johnothetree commented

2017-03-30 18:17:33 +00:00

(Migrated from github.com)

@SheetJSDev I'm using 2013. the button still shows up appropriately, but those dialog boxes show up on first time opening the newly made workbook

SheetJSDev commented

2017-03-30 18:40:05 +00:00

(Migrated from github.com)

@johnothetree I still can't reproduce in Excel 2013 :/

download https://github.com/SheetJS/js-xlsx/files/883177/fixed.xlsx
open a command prompt window and run:

cd Downloads
move fixed.xlsx fixed.xlsm

open fixed.xlsm

I see a message in the top bar:

I click enable editing and see the macro security warning:

After clicking enable content I see the macro worked:

@johnothetree I still can't reproduce in Excel 2013 :/ - download https://github.com/SheetJS/js-xlsx/files/883177/fixed.xlsx - open a command prompt window and run: ```cmd cd Downloads move fixed.xlsx fixed.xlsm ``` - open `fixed.xlsm` I see a message in the top bar: <img width="442" alt="issue615_xl2013a" src="https://cloud.githubusercontent.com/assets/6070939/24520323/0282946a-1556-11e7-896f-b2df0709c8de.png"> I click enable editing and see the macro security warning: <img width="325" alt="issue615_xl2013b" src="https://cloud.githubusercontent.com/assets/6070939/24520330/0a9da298-1556-11e7-8104-b0d30dd8fd58.png"> After clicking enable content I see the macro worked: <img width="190" alt="issue615_xl2013c" src="https://cloud.githubusercontent.com/assets/6070939/24520344/1ea4206e-1556-11e7-8ef1-ff01d2e9391c.png">

SheetJSDev commented

2017-03-30 18:44:48 +00:00

(Migrated from github.com)

For completeness, I included a complete demo in a gist: https://gist.github.com/SheetJSDev/decd392746c23195696efab1c80d66a2

Just download it and follow the README instructions. On my computer the generated file works in Excel 2013 without the warnings you showed.

For completeness, I included a complete demo in a gist: https://gist.github.com/SheetJSDev/decd392746c23195696efab1c80d66a2 Just download it and follow the README instructions. On my computer the generated file works in Excel 2013 without the warnings you showed.

johnothetree commented

2017-03-30 18:51:16 +00:00

(Migrated from github.com)

huh. yeah the gist works fine. maybe i put something in the wrong spot? whatever, looks to work fine! thanks for the help! i'll work with the official release when it's out!

🎉 1

MiriamHershowitz commented

2021-11-02 12:17:22 +00:00

(Migrated from github.com)

@SheetJSDev , I am trying your solution, in the second change

in the function write_ws_xml, add a sheetPr tag with a codeName:

  o[o.length] = (writextag('sheetPr', null, {'codeName': escapexml(wb.SheetNames[idx])}));

what line in the write_ws_xml function should I add the tag?

@SheetJSDev , I am trying your solution, in the second change > in the function write_ws_xml, add a sheetPr tag with a codeName: + o[o.length] = (writextag('sheetPr', null, {'codeName': escapexml(wb.SheetNames[idx])})); what line in the write_ws_xml function should I add the tag?

reviewher commented

2021-11-03 08:37:41 +00:00

(Migrated from github.com)

@MiriamHershowitz no need. https://docs.sheetjs.com/#vba-and-macros Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name wb.Workbook.Sheets[idx].CodeName

@MiriamHershowitz no need. https://docs.sheetjs.com/#vba-and-macros Workbook code name `wb.Workbook.WBProps.CodeName`, Worksheet code name `wb.Workbook.Sheets[idx].CodeName`

👍 1

MiriamHershowitz commented

2021-11-03 09:00:07 +00:00

(Migrated from github.com)

Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name wb.Workbook.Sheets[idx].CodeName

Do I have to set the code name?

> Workbook code name `wb.Workbook.WBProps.CodeName`, Worksheet code name `wb.Workbook.Sheets[idx].CodeName` Do I have to set the code name?

ghost commented

2021-11-04 12:50:21 +00:00

(Migrated from github.com)

Yes you do

On Wed, Nov 3, 2021 at 04:00 MiriamHershowitz @.***>
wrote:

Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name
wb.Workbook.Sheets[idx].CodeName

Do I have to set the code name?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/SheetJS/sheetjs/issues/615#issuecomment-958757666,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AVQWQIEMKZ2WNWCZGFTOFQDUKD24NANCNFSM4DF2OLAQ
.

Yes you do On Wed, Nov 3, 2021 at 04:00 MiriamHershowitz ***@***.***> wrote: > Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name > wb.Workbook.Sheets[idx].CodeName > > Do I have to set the code name? > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > <https://github.com/SheetJS/sheetjs/issues/615#issuecomment-958757666>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AVQWQIEMKZ2WNWCZGFTOFQDUKD24NANCNFSM4DF2OLAQ> > . >

👍 1

MiriamHershowitz commented

2021-11-09 08:29:32 +00:00

(Migrated from github.com)

Yes you do
…
On Wed, Nov 3, 2021 at 04:00 MiriamHershowitz @.***> wrote: Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name wb.Workbook.Sheets[idx].CodeName Do I have to set the code name? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#615 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AVQWQIEMKZ2WNWCZGFTOFQDUKD24NANCNFSM4DF2OLAQ .

I tried to set the CodeName but still can't see the macro buttons, I saw that the Workbook and Worksheet code name are automatically set, do I have to change it from the default value? and if so, what value?

> Yes you do > […](#) > On Wed, Nov 3, 2021 at 04:00 MiriamHershowitz ***@***.***> wrote: Workbook code name wb.Workbook.WBProps.CodeName, Worksheet code name wb.Workbook.Sheets[idx].CodeName Do I have to set the code name? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <[#615 (comment)](https://github.com/SheetJS/sheetjs/issues/615#issuecomment-958757666)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AVQWQIEMKZ2WNWCZGFTOFQDUKD24NANCNFSM4DF2OLAQ> . I tried to set the CodeName but still can't see the macro buttons, I saw that the Workbook and Worksheet code name are automatically set, do I have to change it from the default value? and if so, what value?

reviewher commented

2021-11-09 08:31:24 +00:00

(Migrated from github.com)

Please share a sample file (you may need to rename to .xlsx to attach to the GH issue)

MiriamHershowitz commented

2021-11-09 09:10:42 +00:00

(Migrated from github.com)

I am trying to read the file MacroBook.xlsm and write it to MacroBook1.xlsm

let workbook = XLSX.readFile(__dirname + '/xlsm/MacroBook.xlsm', { bookVBA: true });

   XLSX.writeFile(workbook, __dirname + '/xlsm/MacroBook1.xlsm', { bookType: 'xlsm' });

but after wrting I cant see the button MacroBook.xlsm has

MacroBook.xlsx

Thank U for trying to help...

I am trying to read the file `MacroBook.xlsm` and write it to `MacroBook1.xlsm` ``` let workbook = XLSX.readFile(__dirname + '/xlsm/MacroBook.xlsm', { bookVBA: true }); XLSX.writeFile(workbook, __dirname + '/xlsm/MacroBook1.xlsm', { bookType: 'xlsm' }); ``` but after wrting I cant see the button MacroBook.xlsm has [MacroBook.xlsx](https://github.com/SheetJS/sheetjs/files/7503416/MacroBook.xlsx) Thank U for trying to help...

Sign in to join this conversation.