New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with executing macros from added vba project. #202

Closed
vo-va opened this Issue Dec 19, 2014 · 9 comments

Comments

3 participants
@vo-va

vo-va commented Dec 19, 2014

I write simple macros, then extract it and added to new xlsm file created by xlsxwriter. When I try to execute this macros Excel return error
"License information for this component not found. You do not have an appropriate license to use this functionality in the design environment"

This is original xlsm file https://dl.dropboxusercontent.com/u/66530188/A.xlsm from where I extracted the macros.
And this is new xlsm file https://dl.dropboxusercontent.com/u/66530188/output.xlsm created by xlsxwriter

I try execute macros on 3 different computers, the error persists on each computer.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 19, 2014

Owner

Hi Vladimir,

Thanks for the report.

I can open the xlsm in Excel 2007 and 2013 but I cannot edit or view the macros.

  • Can you view/edit/run the macros in the original file on a machine that raises the XlsxWriter file error?
  • Do you know if there is anything unusual about the macros in the original file?
  • Does it contain some component that requires a license?
  • Did you go through the macro debugging steps at the bottom of the Working with Macros page?

I found this on the Microsoft Knowledge Base "PRB: "License Information for This Component Not Found" Error":

When a control is installed for run time use only (such as those installed by an application created with Visual Basic), if the Visual Basic Development Environment is installed onto the same machine afterwards and the existing control is of equal or higher version than that from the CD, the license key for that control is not updated.

Let me know.

Regards,

John

Owner

jmcnamara commented Dec 19, 2014

Hi Vladimir,

Thanks for the report.

I can open the xlsm in Excel 2007 and 2013 but I cannot edit or view the macros.

  • Can you view/edit/run the macros in the original file on a machine that raises the XlsxWriter file error?
  • Do you know if there is anything unusual about the macros in the original file?
  • Does it contain some component that requires a license?
  • Did you go through the macro debugging steps at the bottom of the Working with Macros page?

I found this on the Microsoft Knowledge Base "PRB: "License Information for This Component Not Found" Error":

When a control is installed for run time use only (such as those installed by an application created with Visual Basic), if the Visual Basic Development Environment is installed onto the same machine afterwards and the existing control is of equal or higher version than that from the CD, the license key for that control is not updated.

Let me know.

Regards,

John

@jmcnamara jmcnamara self-assigned this Dec 19, 2014

@jmcnamara jmcnamara added the question label Dec 19, 2014

@vo-va

This comment has been minimized.

Show comment
Hide comment
@vo-va

vo-va Dec 19, 2014

Hi,
yes I can open/view/edit/run macros in original xlsm file and this is really simple macros see screenshot
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A15%3A47.png

in file created by xlsxwrite I can't view or edit macros from ribbon View-Macros
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A23%3A20.png

but if activate Developer ribbon in Excel options I can view or edit macros from VisualBasic editor
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A25%3A10.png

And right now I found something strange, as you can see on this screnshot
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A39%3A36.png

Excel or maybe xlsxwriter create default sheets and workbook, I create only 2 sheets (s1, and s2) and ThisWorkbook1. By default, when I open created file, macros has been saved in Sheet1 (not Sheet4(s1)).
If you try to run macros from Sheet1-3 or ThisWorkbook modules you receive error like on screenshot above, but if copy macros and paste in modules Sheet4(s1), Sheet5(s3) and ThisWorkbook1, macros will be executed correctly.

And answer on your three last questions is no.

I don't know what other information you needed, feel free to ask.

And I don't have any Visual Basic Development Environment installed on my computer, only MS Office 2010.

vo-va commented Dec 19, 2014

Hi,
yes I can open/view/edit/run macros in original xlsm file and this is really simple macros see screenshot
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A15%3A47.png

in file created by xlsxwrite I can't view or edit macros from ribbon View-Macros
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A23%3A20.png

but if activate Developer ribbon in Excel options I can view or edit macros from VisualBasic editor
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A25%3A10.png

And right now I found something strange, as you can see on this screnshot
https://dl.dropboxusercontent.com/u/66530188/Screenshot%20from%202014-12-19%2016%3A39%3A36.png

Excel or maybe xlsxwriter create default sheets and workbook, I create only 2 sheets (s1, and s2) and ThisWorkbook1. By default, when I open created file, macros has been saved in Sheet1 (not Sheet4(s1)).
If you try to run macros from Sheet1-3 or ThisWorkbook modules you receive error like on screenshot above, but if copy macros and paste in modules Sheet4(s1), Sheet5(s3) and ThisWorkbook1, macros will be executed correctly.

And answer on your three last questions is no.

I don't know what other information you needed, feel free to ask.

And I don't have any Visual Basic Development Environment installed on my computer, only MS Office 2010.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 19, 2014

Owner

Hi Vladimir,

It looks like the issue may be that the VBA names in the macro don't match the workbook/worksheet names in the xlsxwriter file. Try adding something like the following to your program:

    workbook.set_vba_name('ЭтаКнига')
    worksheet1.set_vba_name('Лист1')
    worksheet2.set_vba_name('Лист2')
    # Etc.

John

Owner

jmcnamara commented Dec 19, 2014

Hi Vladimir,

It looks like the issue may be that the VBA names in the macro don't match the workbook/worksheet names in the xlsxwriter file. Try adding something like the following to your program:

    workbook.set_vba_name('ЭтаКнига')
    worksheet1.set_vba_name('Лист1')
    worksheet2.set_vba_name('Лист2')
    # Etc.

John

@vo-va

This comment has been minimized.

Show comment
Hide comment
@vo-va

vo-va Dec 19, 2014

Yes, I can confirm this is fix my issue, sorry for false bug report. But from my experience, I want to say what if you want to include vba project to excel file you must explicit use set_vba_name() for all worksheets and workbook, because by default instead of replacing vba modules xlsxwriter create new. I check it just now, and I check what both excel files donor (with original macros) and new created file, have the same amount of sheets and name of sheets are equal. So maybe it should be described in the documentation.
Thank you for your time, and again, sorry for false bug report.

vo-va commented Dec 19, 2014

Yes, I can confirm this is fix my issue, sorry for false bug report. But from my experience, I want to say what if you want to include vba project to excel file you must explicit use set_vba_name() for all worksheets and workbook, because by default instead of replacing vba modules xlsxwriter create new. I check it just now, and I check what both excel files donor (with original macros) and new created file, have the same amount of sheets and name of sheets are equal. So maybe it should be described in the documentation.
Thank you for your time, and again, sorry for false bug report.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 19, 2014

Owner

sorry for false bug report.

It wasn't a false report. You did have an issue. So that is okay.

So maybe it should be described in the documentation.

It is described. :-)

But I'll try to make it clearer. I think that with an English version of Excel this wouldn't have happened since the default sheet and VBA names would have matched. However, for non-english Excel's (of which there is quite a lot) this will be a problem. So thanks for letting me know.

Regards,

John

Owner

jmcnamara commented Dec 19, 2014

sorry for false bug report.

It wasn't a false report. You did have an issue. So that is okay.

So maybe it should be described in the documentation.

It is described. :-)

But I'll try to make it clearer. I think that with an English version of Excel this wouldn't have happened since the default sheet and VBA names would have matched. However, for non-english Excel's (of which there is quite a lot) this will be a problem. So thanks for letting me know.

Regards,

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 31, 2014

Owner

I've updated the docs to make finding and setting the VBA code names easier.

Owner

jmcnamara commented Dec 31, 2014

I've updated the docs to make finding and setting the VBA code names easier.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 31, 2014

Owner

Added in release 0.6.5.

Thanks.

Owner

jmcnamara commented Dec 31, 2014

Added in release 0.6.5.

Thanks.

@jmcnamara jmcnamara closed this Dec 31, 2014

@NicoHood

This comment has been minimized.

Show comment
Hide comment
@NicoHood

NicoHood Oct 6, 2015

I have the same issue but cannot solve it yet. I am using Excel 2010, mostly english (some stuff is still german on the system, although I try to avoid this).

My code looks like this:

# Add VBA macro
if($vbaMacro){
    # Make sure to use .xlsm file ending if you are using macros.
    # https://github.com/jmcnamara/XlsxWriter/issues/202
    # http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm#Setting_the_VBA_codenames
    $workbook->set_vba_name( 'ThisWorkbook' );
    $worksheet->set_vba_name( 'Sheet1' );
    $workbook->add_vba_project( $vbaMacro );
}

The vba Makro is this. It is placed inside the "ThisWorkbook" file:

Private Sub Workbook_Open()
    AutoFitAll
End Sub

' http://www.vbaexpress.com/kb/getarticle.php?kb_id=40
Sub AutoFitAll()
    ' Speed up screen lags
    Application.ScreenUpdating = False

    ' Cache selected worksheet
    Dim selwkSt As String
    selwkSt = ActiveSheet.Name

    ' Autofit each worksheet
    Dim wkSt As Worksheet
    For Each wkSt In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkSt.Activate
        Cells.EntireColumn.AutoFit
    Next wkSt

    ' Select the first worksheet again and update screen
    Sheets(selwkSt).Select
    Application.ScreenUpdating = True

End Sub

The unzip gives me:

x@x:~> unzip ./Book1.xlsm -d myfile
Archive:  ./Book1.xlsm
  inflating: myfile/[Content_Types].xml
  inflating: myfile/_rels/.rels
  inflating: myfile/xl/_rels/workbook.xml.rels
  inflating: myfile/xl/workbook.xml
  inflating: myfile/xl/theme/theme1.xml
  inflating: myfile/xl/worksheets/sheet2.xml
  inflating: myfile/xl/worksheets/sheet3.xml
  inflating: myfile/xl/worksheets/sheet1.xml
  inflating: myfile/xl/vbaProject.bin
  inflating: myfile/xl/styles.xml
  inflating: myfile/docProps/app.xml
  inflating: myfile/docProps/core.xml

x@x:~> xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
  <workbookPr codeName="ThisWorkbook" defaultThemeVersion="124226"/>
  <sheetPr codeName="Sheet2"/>
  <sheetPr codeName="Sheet1"/>
  <sheetPr codeName="Sheet3"/>

The filename IS .xlsm and I did enable the makros. It I manually try to start the makro cia developers tool I get the error described above. Any ideas?

Edit update
I got it working now, the version before didnt compile (because of an other error). $workbook->set_vba_name( 'ThisWorkbook' ); was required here. Since this should be the default I am still wondering why it is needed then?

NicoHood commented Oct 6, 2015

I have the same issue but cannot solve it yet. I am using Excel 2010, mostly english (some stuff is still german on the system, although I try to avoid this).

My code looks like this:

# Add VBA macro
if($vbaMacro){
    # Make sure to use .xlsm file ending if you are using macros.
    # https://github.com/jmcnamara/XlsxWriter/issues/202
    # http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm#Setting_the_VBA_codenames
    $workbook->set_vba_name( 'ThisWorkbook' );
    $worksheet->set_vba_name( 'Sheet1' );
    $workbook->add_vba_project( $vbaMacro );
}

The vba Makro is this. It is placed inside the "ThisWorkbook" file:

Private Sub Workbook_Open()
    AutoFitAll
End Sub

' http://www.vbaexpress.com/kb/getarticle.php?kb_id=40
Sub AutoFitAll()
    ' Speed up screen lags
    Application.ScreenUpdating = False

    ' Cache selected worksheet
    Dim selwkSt As String
    selwkSt = ActiveSheet.Name

    ' Autofit each worksheet
    Dim wkSt As Worksheet
    For Each wkSt In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkSt.Activate
        Cells.EntireColumn.AutoFit
    Next wkSt

    ' Select the first worksheet again and update screen
    Sheets(selwkSt).Select
    Application.ScreenUpdating = True

End Sub

The unzip gives me:

x@x:~> unzip ./Book1.xlsm -d myfile
Archive:  ./Book1.xlsm
  inflating: myfile/[Content_Types].xml
  inflating: myfile/_rels/.rels
  inflating: myfile/xl/_rels/workbook.xml.rels
  inflating: myfile/xl/workbook.xml
  inflating: myfile/xl/theme/theme1.xml
  inflating: myfile/xl/worksheets/sheet2.xml
  inflating: myfile/xl/worksheets/sheet3.xml
  inflating: myfile/xl/worksheets/sheet1.xml
  inflating: myfile/xl/vbaProject.bin
  inflating: myfile/xl/styles.xml
  inflating: myfile/docProps/app.xml
  inflating: myfile/docProps/core.xml

x@x:~> xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
  <workbookPr codeName="ThisWorkbook" defaultThemeVersion="124226"/>
  <sheetPr codeName="Sheet2"/>
  <sheetPr codeName="Sheet1"/>
  <sheetPr codeName="Sheet3"/>

The filename IS .xlsm and I did enable the makros. It I manually try to start the makro cia developers tool I get the error described above. Any ideas?

Edit update
I got it working now, the version before didnt compile (because of an other error). $workbook->set_vba_name( 'ThisWorkbook' ); was required here. Since this should be the default I am still wondering why it is needed then?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 6, 2015

Owner

Since this should be the default I am still wondering why it is needed then?

It is only set by default if the workbook contains a button.The documentation probably should make that distinction.

Owner

jmcnamara commented Oct 6, 2015

Since this should be the default I am still wondering why it is needed then?

It is only set by default if the workbook contains a button.The documentation probably should make that distinction.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment