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

New Issue

Closed

opened 2017-03-30 16:34:00 +00:00 by johnothetree · 16 comments

johnothetree commented 2017-03-30 16:34:00 +00:00 (Migrated from github.com)

Copy Link

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.

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)

Copy Link

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

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)

Copy Link

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:

-	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:

+       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)

Copy Link

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)

Copy Link

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.  

SheetJSDev commented 2017-03-30 18:02:15 +00:00 (Migrated from github.com)

Copy Link

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)

Copy Link

@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 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)

Copy Link

@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)

Copy Link

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)

Copy Link

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!

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)

Copy Link

@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)

Copy Link

@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)

Copy Link

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)

Copy Link

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)

Copy Link

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)

Copy Link

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

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)

Copy Link

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.

No Branch/Tag Specified

Branches Tags

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.20.3

v0.20.2

v0.20.1

v0.20.0

v0.19.3

v0.19.2

v0.19.1

v0.19.0

v0.18.12

v0.18.11

v0.18.10

v0.18.9

v0.18.8

v0.18.7

v0.18.5

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0

v0.17.5

v0.17.4

v0.17.3

v0.17.2

v0.17.1

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.1

v0.12.0

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.11

v0.7.10

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.1

v0.5.10

v0.5.9

v0.5.8

v0.5.7

v0.5.0

v0.4.3

Labels

Clear labels   

DBF

  

Dates

  

Defined Names

  

Features

  

Formula

  

HTML

  

Images

  

Infrastructure

  

Integration

  

International

  

ODS

  

Operations

  

Performance

  

PivotTables

  

Pro

  

Protection

  

Read Bug

  

SSF

  

SYLK

  

Style

  

Write Bug

  

good first issue

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

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#615

No description provided.