1

I am attempting to copy the most recent edition of an XML file into an Excel Addin. To do this by hand, first add .zip to the end of the addin file name, then copy the xml into the .xlam.zip folder, and lastly rename the folder back to a .xlam file.

I am attempting to speed up this process by making a VBS script. Here is what I have:

Dim Fso, ShellApp
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")

'rename the addin to be a zip file
fso.MoveFile "C:\Users\me\AppData\Roaming\Microsoft\AddIns\MyAddIn.xlam", _
    "C:\Users\me\AppData\Roaming\Microsoft\AddIns\MyAddIn.xlam.zip"
wscript.sleep 1000
'copy the xml into the addin
Set ShellApp = CreateObject("Shell.Application")
ShellApp.namespace ("C:\Users\me\AppData\Roaming\Microsoft\AddIns\MyAddIn.xlam.zip\customUI").copyhere _
    "C:\Users\me\Desktop\customUI.xml", 12 ' 4 + 8 no progress bar, yes to all dialogs
'unzip the addin
fso.MoveFile "C:\Users\me\AppData\Roaming\Microsoft\AddIns\MyAddIn.xlam.zip", _
    "C:\Users\me\AppData\Roaming\Microsoft\AddIns\MyAddIn.xlam"
wscript.echo "Done!"

This works intermittently...

Sometimes it will create a new zip folder in the directory, make a customUI folder inside that and then copy the file there.

Less frequently (and what I want to happen) it will change my addin to a zip, copy the xml in the subfolder, and then change it back to an addin.

Any thoughts on how to make it more consistant or what might be causing the inconsistancies? Thanks in advance.

Evan
  • 600
  • 2
  • 7
  • 34
  • when copying to zip folder you'll need to wait until the number of the files within the zip is increased (copying in zip is transactional) - check this for reference - http://www.robvanderwoude.com/vbstech_files_zip.php or this - https://github.com/npocmaka/batch.scripts/blob/master/hybrids/jscript/zipjs.bat – npocmaka Jun 10 '15 at 20:49
  • See this: https://stackoverflow.com/q/25497917/3451115 – SlowLearner Oct 10 '18 at 09:05

0 Answers0