Replace vbaProject.bin in XLSM excel file

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Replace vbaProject.bin in XLSM excel file

Post by YasserKhalil »

Hello everyone

I have an excel file named "Sample.xlsm" and in the same path of the excel file, I have a vbaProject.bin file
How can I replace the embedded vbaproject.bin in the xlsm file with my own .bin file?
I have searched and found a code but it doesn't work for me

Code: Select all

Sub ReplaceVBABin7z()

    Const SevenZipExe = "C:\Program Files\7-Zip\7z.exe"
    Const tmpDir = "c:\temp\7z\"
    Dim qq As String: qq = Chr(34)  '"
    
    ' check 7-zip exe exists
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.fileexists(SevenZipExe) Then
        MsgBox SevenZipExe & " not found", vbCritical, "7-Zip Not found"
        Exit Sub
    End If
    
    ' create list of commands available
    Dim cmd As String, pid As Double
    'cmd = "cmd /c """ & SevenZipExe & """ >" & tmpDir & "7-Zip_Commands.txt"
    'pid = Shell(cmd, vbHide)
    'MsgBox "Command List see " & tmpDir & "7-Zip_Commands.txt", vbInformation, pid
    
    Dim path As String
    Dim strFileName As String, strBinName As String

    ' select workbook
    path = ThisWorkbook.path & "\"
    strFileName = Application.GetOpenFilename("Excel Macro Enabled Workbook (*.xlsm), *.xlsm")
    If strFileName = "False" Then Exit Sub
    strFileName = qq & strFileName & qq ' quoted for spaces in filename
       
ext:
    ' extract xl dir and sub dirs into tmpdir
    cmd = qq & SevenZipExe & qq & " x -r -y -o" & qq & tmpDir & qq & " " & _
           strFileName & " xl"
    pid = Shell(cmd, vbHide)
    Debug.Print pid, cmd
    MsgBox "xl directory from " & strFileName & " extracted to " & tmpDir, vbInformation, "EXTRACT pid=" & pid
    'Shell "Taskkill -pid " & pid

del:
    ' delete xl\vbaProject.bin dir and subdir
    strBinName = "xl\vbaProject.bin"
    cmd = qq & SevenZipExe & qq & " d -r " & _
          strFileName & " " & strBinName
    pid = Shell(cmd, vbHide)
    Debug.Print pid, cmd
    MsgBox strBinName & " deleted from " & strFileName, vbInformation, "DELETE pid=" & pid
    'Shell "Taskkill -pid " & pid

upd:
    ' update xl dir and subdir
    cmd = qq & SevenZipExe & qq & " u -r -y -stl " & _
          strFileName & " " & qq & tmpDir & "xl" & qq
    pid = Shell(cmd, vbHide)
    Debug.Print pid, cmd
    MsgBox strFileName & " updated from " & tmpDir, vbInformation, "UPDATE pid=" & pid
    'Shell "Taskkill -pid " & pid

End Sub

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace vbaProject.bin in XLSM excel file

Post by HansV »

I don't have 7Zip, so I can't help you.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace vbaProject.bin in XLSM excel file

Post by YasserKhalil »

Away from 7zip, is it possible to do such a task without 7zip?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace vbaProject.bin in XLSM excel file

Post by HansV »

Sorry, no idea.
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Replace vbaProject.bin in XLSM excel file

Post by SpeakEasy »

>I have searched and found a code but it doesn't work for me

Mainly because it is missing a crucial step - which is replacing the vbaProject.bin in the extracted files with your version before updating the xlsm file. What this code odes is remove the existing vbaProject.bin from the xlsm and then immediately put it back ...

But I believe we can do this more cleanly and without a reliance on 7-Zip. Give me a n hour or so ...

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace vbaProject.bin in XLSM excel file

Post by YasserKhalil »

Thanks a lot for sharing. Take your time.

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Replace vbaProject.bin in XLSM excel file

Post by SpeakEasy »

OK, something like this (it assumes that the xlsm and the .bin file are in the same folder, as per your assertion "in the same path of the excel file, I have a vbaProject.bin file"):

Code: Select all

Public Sub doit()
    ModifyXlsm "D:\Downloads\DeleteMe\7z\", "sample" 
End Sub

' pass path to folder with xlsm and new vbaProject.bin file, and optionally the name of the xlsm file in that folder without the xlsm extension
Public Sub ModifyXlsm(rootpath As String, Optional myfile As String = "sample")
    Dim myShell As New Shell32.Shell
    Dim zipfile As String
    Dim xlsmfile As String
    
    xlsmfile = rootpath & myfile & ".xlsm"
    zipfile = rootpath & myfile & ".zip"
    
     ' Shell actually knows how to work with basic zip files, but cannot recognise that an xlsm file is actually a zip file, so we just need to do a quick rename
    Name xlsmfile As zipfile
    
    MkDir rootpath & "tmp\" 'temp place to dump old vbaProject.bin
    
    ' move unwanted file out of  xlsm
    myShell.Namespace(tmppath).MoveHere myShell.Namespace(zipfile).ParseName("vbaProject.bin")
    ' copy in the alternate file
    myShell.Namespace(zipfile).CopyHere rootpath & "vbaProject.bin" ', 1044 '20
    DoEvents ' make sure shell keeps up
    
    ' Clean up
    Kill rootpath & "tmp\" & "*.*"
    RmDir rootpath & "tmp\"

    Name zipfile As xlsmfile
End Sub

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace vbaProject.bin in XLSM excel file

Post by YasserKhalil »

Thank you very much.
After this line `MkDir rootpath & "tmp\"`, I have put a line that defines a value to `tmppath` variable

Code: Select all

tmppath = rootpath & "tmp\"
The I encountered an error at this line

Code: Select all

Kill rootpath & "tmp\" & "*.*"
So I wrapped it with statements of skipping errors

Code: Select all

    On Error Resume Next
        Kill rootpath & "tmp\" & "*.*"
    On Error GoTo 0
And finally, the code works, but when opening the Sample.xlsm to have a look at the modules, I found that nothing changed. The same old modules are still there.

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Replace vbaProject.bin in XLSM excel file

Post by SpeakEasy »

>I have put a line that defines a value to `tmppath` variable
This is what happens when I try and meet a self-imposed deadline. I didn't tidy up my code enough!

>I encountered an error at this line
Hmm - the only reason it should error is if either the folder (rootpath & "tmp\") does not exist, or the folder does not contain a file. Since we have created the folder ourselves with the earlier mkdir, this means the latter: the error would be because the folder does not contain a file. And the only way that should be the case is if we have failed to extract vbaProject.bin from sample.xlsm. But that shouldn't be possible unless sample.xlsm has already had vbaProject.bin removed. Which means .... ah, yes, Ok. here, have some slightly updated code ...

Code: Select all

Public Sub doit()
    ModifyXlsm "D:\Downloads\DeleteMe\7z\", "sample" ' path to folder with xlsm and new vbaProject.bin file
End Sub

' pass path to folder with xlsm and new vbaProject.bin file, and optionaslly the name of the xlsm file in that folder without the xlsm extension
Public Sub ModifyXlsm(rootpath As String, Optional myfile As String = "sample")
    Dim myShell As New Shell32.Shell
    Dim zipfile As String
    Dim oZipfile As FolderItem
    Dim xlsmfile As String
    
    xlsmfile = rootpath & myfile & ".xlsm"
    zipfile = rootpath & myfile & ".zip"
    
     ' Shell actually knows how to work with basic zip files, but cannot recognise that an xlsm file is actually a zip file, so we just need to do a quick rename
    Name xlsmfile As zipfile
    Set oZipfile = myShell.Namespace(zipfile).Items.Item("xl") ' get correct subfolder!!!
    
    MkDir rootpath & "tmp\" 'temp place to dump old vbaProject.bin
    
    ' move unwanted file out of  xlsm
    myShell.Namespace(rootpath & "tmp\").MoveHere myShell.Namespace(oZipfile).ParseName("vbaProject.bin")
    ' copy in the alternate file
    myShell.Namespace(oZipfile).CopyHere rootpath & "vbaProject.bin" 
    DoEvents ' make sure shell keeps up
    
    ' Clean up
    Kill rootpath & "tmp\" & "*"
    RmDir rootpath & "tmp\"

    Name zipfile As xlsmfile
End Sub

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace vbaProject.bin in XLSM excel file

Post by YasserKhalil »

Amazing. Thank you very much.
Best Regards

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace vbaProject.bin in XLSM excel file

Post by LisaGreen »

Good solution!!!

Lisa

stevenXML
NewLounger
Posts: 2
Joined: 24 Jan 2023, 15:39

Re: Replace vbaProject.bin in XLSM excel file

Post by stevenXML »

SpeakEasy wrote:
27 May 2022, 12:26
>I have put a line that defines a value to `tmppath` variable
This is what happens when I try and meet a self-imposed deadline. I didn't tidy up my code enough!

>I encountered an error at this line
Hmm - the only reason it should error is if either the folder (rootpath & "tmp\") does not exist, or the folder does not contain a file. Since we have created the folder ourselves with the earlier mkdir, this means the latter: the error would be because the folder does not contain a file. And the only way that should be the case is if we have failed to extract vbaProject.bin from sample.xlsm. But that shouldn't be possible unless sample.xlsm has already had vbaProject.bin removed. Which means .... ah, yes, Ok. here, have some slightly updated code ...

Code: Select all

Public Sub doit()
    ModifyXlsm "D:\Downloads\DeleteMe\7z\", "sample" ' path to folder with xlsm and new vbaProject.bin file
End Sub

' pass path to folder with xlsm and new vbaProject.bin file, and optionaslly the name of the xlsm file in that folder without the xlsm extension
Public Sub ModifyXlsm(rootpath As String, Optional myfile As String = "sample")
    Dim myShell As New Shell32.Shell
    Dim zipfile As String
    Dim oZipfile As FolderItem
    Dim xlsmfile As String
    
    xlsmfile = rootpath & myfile & ".xlsm"
    zipfile = rootpath & myfile & ".zip"
    
     ' Shell actually knows how to work with basic zip files, but cannot recognise that an xlsm file is actually a zip file, so we just need to do a quick rename
    Name xlsmfile As zipfile
    Set oZipfile = myShell.Namespace(zipfile).Items.Item("xl") ' get correct subfolder!!!
    
    MkDir rootpath & "tmp\" 'temp place to dump old vbaProject.bin
    
    ' move unwanted file out of  xlsm
    myShell.Namespace(rootpath & "tmp\").MoveHere myShell.Namespace(oZipfile).ParseName("vbaProject.bin")
    ' copy in the alternate file
    myShell.Namespace(oZipfile).CopyHere rootpath & "vbaProject.bin" 
    DoEvents ' make sure shell keeps up
    
    ' Clean up
    Kill rootpath & "tmp\" & "*"
    RmDir rootpath & "tmp\"

    Name zipfile As xlsmfile
End Sub
Is this code complete? because I get undefined type for the 'FolderItem' for starters, maybe even more errors if my compiler gets past that.
Last edited by stevenXML on 24 Jan 2023, 17:41, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace vbaProject.bin in XLSM excel file

Post by HansV »

Welcome to Eileen's Lounge!

For this code to work, you have to set a reference (in Tools > References...) to Microsoft Shell Controls and Automation.
Best wishes,
Hans

stevenXML
NewLounger
Posts: 2
Joined: 24 Jan 2023, 15:39

Re: Replace vbaProject.bin in XLSM excel file

Post by stevenXML »

Thx Hans, will try and keep you posted.