Programming corner wildboy85

Tuesday, September 16, 2014

Programming with structure without making it too complicated

Program example/sample with a good structure

Outlook 2013 addin that create a toolbar and a button on it
Also define a handler to call a sub with the button we added

Once upon a time, I programmed multiple dimension arrays, all numbered and dynamically accessibles by numbers.
A year later I told myself: never again!

Why? Because a program like this is like diving. When you are 30 feet deep, you are comfortable. But when you restart from the top a year later, the descent is long...

So i decided to adopt a standard, a plan to follow, and here it is.

Situation:
You get some code example on internet
You try to run it in visual studio, but a lot of objects are not reconized

Problem:
You are missing libraries (references, dll)

Solution:
Always include comments with what library to include in the code

Example:
'=== this is a addin for outlook 2013
'=== target framework: .net framework 4.5
'=== compile: any cpu
'=== programming: visual studio 2013
'=== references: just default office 15 references and visual studio ones, no custom
'=== software: office 15 (2013) must be installed
'=== software: .net framework 4.5 must be installed but office 2013 requires it anyway
'=== system: windows 7 64 bits sp1
---------------------------------------

Situation:
You have many functions and subs in your program

Problem:
You have to add a parameter to that sub you call because you forgot a situation that just emerged
But you already have 100 lines calling this sub...
You have to change all the line calling it for that

Solution:
use structures variables (vb)
or type variables (scripts)
You pass the structure as parameter to the sub or function
When you add one more parameter, you can reprogram the code inside the sub, but you do not have to change all the lines calling it, they still pass only one structure to the sub

Example:
'=== structure to pass to a function to create a toolbar in outlook

Public Structure bar_param
Dim bar_Exist01 As Integer '=== if bar exist we do not recreate
Dim bar_Caption01 As String '=== name of bar
Dim bar_inexplorer01 As Integer '=== create in main outlook window
Dim bar_ininspector01 As Integer '=== create in message window
End Structure

'=== strucutre to pass to a function to create a button in the bar we created just before

Public Structure but_param
Dim bar_obj As Object
Dim but_exist01 As Integer
Dim but_Caption01 As String
Dim but_tooltip01 As String
Dim but_onaction01 As String
Dim but_face01 As Integer '=== icon
Dim but_inexplorer01 As Integer '=== in main outlook window
Dim but_ininspector01 As Integer '=== in message window
Dim but_bar01 As Object '=== bar to add button to
End Structure

As you can see, but is abbreviation for button
Some poeple would say get a more significative name
But no, we also need clarity of code (and i am too lazy to type long names)
Too much text and you drown in your own code

The number at the end of the parameters are to show that these variables are not system variables, but custom/user made
------------------------------------------

Situation:
Your code is spaghetti
Your main loop is 1000 lines

Problem:
You have no main sub to look at to understand the program with one look
It's all a big pack of lines with lots of exceptions add along the way
You had to do that because you have many global variables used everywhere

Solution:
You main sub should be 1 to 2 screen big
Use functions even if you have to pass them lots of parameters to make them work
Always verify if the function return something
Then your main sub can continue processing only if the functions did return something

Example:
Private Sub ThisAddIn_Startup() Handles Me.Startup

Dim button01 As Office.CommandBarButton
Dim bar01 As Office.CommandBar

Dim objNet = CreateObject("WScript.Network")
'=== get username logon from local machine
usenam = LCase(objNet.UserName)

'====== BAR addition
Dim toolbarcnt = 0
'=== dynamic number of parameters by passing a structure to the function
'=== you can add parameters you want to opass to the functon in structure without modifying the code that call the function
Dim bar_param01 As bar_param

bar_param01.bar_Exist01 = 0
bar_param01.bar_Caption01 = "Hyperzip01"
bar_param01.bar_inexplorer01 = 1
bar_param01.bar_ininspector01 = 0

'=== if we want more toolbars, we extend the array and repeat same thing with a different bar name

bar01 = toolbar_Add(bar_param01)

Dim buttoncnt = 0
Dim but_param01(buttoncnt) As but_param

If Not bar01 Is Nothing Then

'====== buttons creation in explorer (main outlook windows)

In this example, we call a function to create the outlook bar
If the resulting bar (bar01) is nothing we obviously cannot continue and create the buttons on the bar
We create only one bar, so we defined a structure (bar_param01) as one variable, not an array of variables

The next time we use structures as an array containing many buttons parameters to create
We ony have one button here, but we can define the array of parameters to be bigger if we want to create more than one

'====== buttons creation in explorer (main outlook windows)
but_param01(buttoncnt).but_exist01 = 0
but_param01(buttoncnt).but_Caption01 = "HyperZIP"
but_param01(buttoncnt).but_tooltip01 = "Zip des fichiers ou dossiers et envoie un lien de téléchargement"
but_param01(buttoncnt).but_onaction01 = "hyperZIP"
but_param01(buttoncnt).but_face01 = 5432
but_param01(buttoncnt).but_inexplorer01 = 1
but_param01(buttoncnt).but_ininspector01 = 0
but_param01(buttoncnt).but_bar01 = bar01

'=== redim of more buttons to create
'buttoncnt = buttoncnt + 1
'ReDim Preserve but_param01(buttoncnt)

Loop through the array of parameters to create all buttons

For i = 0 To buttoncnt
button01 = button_Add(but_param01(buttoncnt))
If Not button01 Is Nothing Then
'=== button created in a bar
'MsgBox("bar name: " & bar01.name & vbCrLf & "Button name: " & button01.Caption)

'=== every handler must be connected to a sub, so we cant use dynamic name for the call here, we use fixed name hyperzip_click_01
If but_param01(buttoncnt).but_Caption01 = "HyperZIP" Then AddHandler button01.Click, AddressOf hyperzip_click_01

if the button was not created, we will have a fatal error

Else
'=== error the button was not created
MsgBox("WARNING - button (explorer - outlook window) not existing or created" & vbCrLf & but_param01(buttoncnt).but_Caption01 & vbCrLf & "next button")
End If
Next
Else

if the bar was not created we will have a fatal error

'=== error bar was not created
MsgBox("ERROR - fatal - toolbar (explorer - outlook window) not existing or created" & vbCrLf & bar_param01.bar_Caption01 & vbCrLf & "exiting program")
Exit Sub
End If

As you can see, we create a bar, and continue the main sub only if the bar exist
Then we create a button in the bar and continue only if the button exist

As this addin will be called by thoses buttons, there is no need to continue if the bar or the button does not exist after we tried to create it

Debugging:
There is no error trapping, but eventually, there should be only in the lower level sub
If you trap error before calling a function (in main sub), you will never know where the program crash as it will mostly report an error at the line calling the function
(this happen if your function is in another class, in same class, it's usually not a problem)

here is the rest of the code:

Function toolbar_Add(bar_param01 As bar_param) As Office.CommandBar

'=== add a toolbar object in outlook explorer or inspector (message)

'=== with a structure as parameter, we can add as many arguments we want before calling this function without modifying all the line that use it
'=== outlook objects for "complements"

Dim explorer01 As Microsoft.Office.Interop.Outlook.Explorer
Dim inspector01 As Microsoft.Office.Interop.Outlook.Inspector
explorer01 = Globals.ThisAddIn.Application.ActiveExplorer
inspector01 = Globals.ThisAddIn.Application.ActiveInspector

Dim bars01 As Object

If bar_param01.bar_inexplorer01 = 1 Then
'=== 1 = in message
bars01 = explorer01.CommandBars
End If
If bar_param01.bar_ininspector01 = 1 Then
'=== 0 = in outlook (main)
bars01 = inspector01.CommandBars
End If

'=== check if in all bars in a bar with same name exist
For Each bar01 In bars01
If Trim(LCase(bar01.Name)) = Trim(LCase(bar_param01.bar_Caption01)) Then
'bar01.Delete
If InStr(LCase(bar01.Name), "test01") Then
'MsgBox("stas2 found")
End If
bar_param01.bar_Exist01 = 1
toolbar_Add = bar01
End If
Next

'=== add tolbar if not already there
If bar_param01.bar_Exist01 = 0 Then
'=== we are in outlook main window
If bar_param01.bar_inexplorer01 = 1 Then
toolbar_Add = explorer01.CommandBars.Add(bar_param01.bar_Caption01)

ElseIf bar_param01.bar_ininspector01 = 1 Then
'=== we are in a message
toolbar_Add = inspector01.CommandBars.Add(bar_param01.bar_Caption01)
End If
End If

'=== make toolbar visible
If toolbar_Add IsNot Nothing Then
toolbar_Add.Name = bar_param01.bar_Caption01
toolbar_Add.Visible = True
If bar_param01.bar_Exist01 = 0 Then
toolbar_Add.Position = Office.MsoBarPosition.msoBarTop
End If
End If

Return toolbar_Add

End Function

Function button_Add(but_param01 As but_param) As Office.CommandBarButton

'=== add a button in a toolbar
'=== the bar to add the button to is in the parameters structure

'=== compteur de boutons pour pouvoir en ajouter n'importe où dans la matrice
'========================= button in toolbar

'=== outlook objects for "complements"
Dim inspector01 As Microsoft.Office.Interop.Outlook.Inspector
Dim explorer01 As Microsoft.Office.Interop.Outlook.Explorer
explorer01 = Globals.ThisAddIn.Application.ActiveExplorer
inspector01 = Globals.ThisAddIn.Application.ActiveInspector

Dim button01 As Object

'=== delete all buttons in bar
For Each button01 In but_param01.but_bar01.Controls
button01.Delete()
Next

'=== button add

If but_param01.but_inexplorer01 = 1 Then
'=== in outlook main windows
button01 = explorer01.CommandBars(but_param01.but_bar01.name).Controls.Add(Type:=Office.MsoControlType.msoControlButton, Before:=1)
ElseIf but_param01.but_ininspector01 = 1 Then
'=== in message
button01 = inspector01.CommandBars(but_param01.but_bar01.name).Controls.Add(Type:=Office.MsoControlType.msoControlButton, Before:=1)
End If

If Not button01 Is Nothing Then
With button01
'buttons(depnum, 0)

.Caption = but_param01.but_Caption01
.TooltipText = but_param01.but_tooltip01
.Enabled = True
.Visible = True
.OnAction = but_param01.but_onaction01
'.OnAction = "!<" & & ">"
.tag = but_param01.but_Caption01
.Style = Office.MsoButtonStyle.msoButtonIconAndCaption
.FaceId = but_param01.but_face01

'Dim Icon01 = LoadPicture("C:\_stas\outlook\bou_HYPERZIP.bmp")

End With

End If
button_Add = button01

End Function

Private Sub hyperzip_click_01(ByVal ctrl As Office.CommandBarButton, ByRef Cancel As Boolean)

'MsgBox("You clicked: " + ctrl.Caption)

'=== in 2013, you create a class called "form", then create a new object (window aka form) from this class, then use it
Dim form01 As New Form1
'=== show the form
form01.Show()

End Sub

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
hyperzip = Nothing
End Sub

End Class

Thursday, July 3, 2014

Unattended windows 7 64 bits professional usb boot

Usb key windows 7 unattend bootable

Hullo,

I am a little tired of formatting pcs (windows and softwares)
I decided to boot my windows 7 dvd on usb and add an autounattend.xml

1.
You must use the one time boot key to boot the computer on the usb key (F8 on core2, ESC on HP, F12 on dell) (WARNING: the key will delete all partitions on first sata connector) (WARNING: disable EFI boot (or secure boot), the key is regular boot)

2.
if you change the boot order in BIOS, the key will format and reboot until the end of time (unless you remove the usb key after first reboot)

3.
The file [rootoftheusbkey] autounattend.xml contain what will be installed

4.
At the end, there will be a folder on desktop named "À faire - Todo"
In this folder, run the script "Redetecte les périphériques mal installés"
The second redetection script, is for the video card, but it will reboot without a warning, so do not run it while something else is in progress

5.
Folders on the key

ALL the folders in [rootoftheusbkey] \sources\$oem$\$1 will be copied on c:\ on installation
You can delete them after to gain disk space

sources\$i386$\$1\_drivers
Containt 5 gigabytes of drivers for different devices:

sources\$i386$\$1\_drivers_manual
This folder contain drivers that must be installed manually. Like video card drivers, ACPI, and some others

sources\$i386$\$1\_appsall
This folder contain the application installed on all computers (not all of the are installed)

sources\$i386$\$1\_appsmaison
"house" or personnal applications

2016-09-30

I optimized the usb key boot a lot with this patch package from microsoft

This procedure actually integrate a LOT of patches (about 270) in the usb key image/dvd:
Prerequisite:
Windows6.1-KB3020369-x64.msu
Patch package:
AMD64-all-windows6.1-kb3125574-v4-x64_2dafb1d203c8964239af3048b5dd4b1264cd93b9.msu

Integration to the usb key (windows 7 sp1 professional):

The windows 7 sp1 (64bits) dvd is in this folder:
C:\windows 7 sp1 fra pro unattend\

These command must be done in a command prompt (cmd.exe):
Get information about what version is the windows 7 sp1 DVD:
Dism /Get-WIMInfo /WimFile:"C:\windows 7 sp1 fra pro unattend\sources\install.wim"

Make a temporary folder to extract DVD data:
mkdir C:\Win7SP1ISO\offline

Extract DVD data essential to windows DVD installation into C:\Win7SP1ISO\offline:
Dism /Mount-WIM /WimFile:"C:\windows 7 sp1 fra pro unattend\sources\install.wim" /Name:"Windows 7 PROFESSIONAL" /MountDir:C:\Win7SP1ISO\offline

Delete this key if DISM say image is already mounted:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WIMMount\Mounted Images

Add the prerequisite package to the C:\Win7SP1ISO\offline folder:
Dism /Image:C:\Win7SP1ISO\offline /Add-Package /PackagePath:C:\_aef\updates\Windows6.1-KB3020369-x64.msu

Add the big patch package from microsoft to the C:\Win7SP1ISO\offline folder:
Dism /Image:C:\Win7SP1ISO\offline /Add-Package /PackagePath:C:\_aef\updates\AMD64-all-windows6.1-kb3125574-v4-x64_2dafb1d203c8964239af3048b5dd4b1264cd93b9.msu

Move the the C:\Win7SP1ISO\offline folder back to the DVD / usb image (this will not change the unattented status of the DVD):
Dism /Unmount-WIM /MountDir:C:\Win7SP1ISO\offline /Commit

2016-01-31
Batch file to patch some windows update at next pc restart:

--------- start phase04.bat file ---------------- use notepad.exe to create ---------------
rem phase 04 to 10

c:
rem folder where the usb copie the patchs when installing windows (source\$oem$\$1\_updates\)
cd c:\_updates\to20150102
for /F %%A in ('dir /b *.msu') do start /wait wusa.exe %%A /quiet /norestart
x:

reg add HKLM\Software\Microsoft\Windows\CurrentVersion\RunOnce /v NewSetupScript /t Reg_SZ /d "c:\_updates\phase05.bat" /f

rem echo phase04 done>"%USERPROFILE%\Desktop\A Faire - ToDo\phase04 done - phase04 faite.txt"

Shutdown /r /t 10
--------- end phase04.bat file ----------------

Step00:

Create a new usb key for boot:
(lexar usb key will do, sometimes kingston will mess up partitionning)

windows 7 usb boot

In short:
insert usb key (8 giga minumum I presume)
[windows] r
cmd
diskpart
(added list disk, on some pc, diskpart does list disk when executed)
list disk
select disk 11
(carefull here, it delete all)
clean
create partition primary
select partition 1
active
format fs=fat32
(wait for the format to complete)
assign
(let's say G: letter was assigned)
(if formated in ntfs: Bootsect.exe /nt60 G:)
copy all files from windows 7 bootable dvd to usb key

This file tell wich edition of windows is choosen:
G:\sources\ei.cfg

Step01:
You can stop here, and you will be able to boot on usb key (F12 and choose usb key to boot for most pc, F8 for BBS setup on old core 2 (yeah BBS setup mean boot menu)

You cannot really change the boot order in BIOS because the usb key boot will restart the windows installation at everyboot. (of course you could watch for the reboot, restart pc, remove usb boot from bios after the first phase of the windows installation)

A dvd read +/- 5 mb/sec, a usb key read +/- 20 mb/sec (usb 2)
It took 11 minutes from partition selection to desktop access to install windows 7 sp1 on a core 2 with a western digital hard disk of 500 gig

Step02:
Drivers folder (inf, cat)
On our newly created usb windows 7 sp1 boot, there is a folder called sources/$i386$\$1\drivers
Windows 7 will search all subfolders there and install drivers if the INF and CAT files are present (exe is not really a driver, just a compressed setup, in wich you can probably find drivers)

You can also get the drivers in raw form from the C:\Windows\System32\DriverStore\FileRepository but there is a lot of drivers there that are from the CAB files. And this folder is big.

Step03:
autounattend.xml file creation and location

root of the usb key: (not usb drive, just a key)
autounattend.xml

Step04:
WARNING: the usb key with this unattend file WILL delete all partitions on hard disk connected to SATA 0 connector
Be sure your C drive and D drive are not inverted on the connectors
Open your computer case to be sure the drive that will be erased is on SATA connector 0
Example:
if your current D drive is on connector SATA0, then D drive will be wiped/erased/data no more!

This usb key is not EFI bootable
That mean if the computer is very new, the key might not boot at all
You can go in BIOS and turn OFF EFI boot or SECURE boot off
Also, be sure to turn on AHCI ON, this type of disk access is faster

Step05:
The installation create an "administrateur" account
This account password (is empty)
It will expire after a few months
Just press "enter" if windows ask for a new one, or enter "" for password, and enter a new password not empty and a confirmation

Content of this file:
basic setup: will destroy disk 0 without mercy!

R01 2014-07-05 I had to change installation order (installing apps before register base modification was not working, also this bad order was preventing drivers from being installed)

r02 2014-12-29
problem: some drivers were flagged as boot-critical
problem: i was detecting drivers in wrong phase (winpe)
Shift F10 in error screen enabled me to see the winpe command prompt and then navigating in x:\windows\panther to consult logs and see my error
Solution:changed PnpCustomizationWinPE to PnpCustomizationNonWinPE

--------------------------- autounattend.xml ----------------------------
<?xml version="1.0" encoding="utf-8"?>
<unattend xmlns="urn:schemas-microsoft-com:unattend">
<settings pass="offlineServicing">
<component name="Microsoft-Windows-LUA-Settings" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<EnableLUA>false</EnableLUA>
</component>
</settings>

<settings pass="windowsPE">
<component name="Microsoft-Windows-International-Core-WinPE" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35"
language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SetupUILanguage>
<UILanguage>fr-FR</UILanguage>
<WillShowUI>Never</WillShowUI>
</SetupUILanguage>
<InputLocale>0c0c:00001009</InputLocale>
<SystemLocale>fr-FR</SystemLocale>
<UILanguage>fr-FR</UILanguage>
<UserLocale>fr-FR</UserLocale>
</component>
<component name="Microsoft-Windows-Setup" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS">
<DiskConfiguration>
<WillShowUI>OnError</WillShowUI>
<Disk wcm:action="add">
<DiskID>0</DiskID>
<WillWipeDisk>true</WillWipeDisk>
<CreatePartitions>
<CreatePartition wcm:action="add">
<Order>1</Order>
<Type>Primary</Type>
<Extend>true</Extend>
</CreatePartition>
</CreatePartitions>
<ModifyPartitions>
<ModifyPartition wcm:action="add">
<Format>NTFS</Format>
<Label>WIN7SP1</Label>
<Letter>C</Letter>
<Order>1</Order>
<Active>true</Active>
<PartitionID>1</PartitionID>
</ModifyPartition>
</ModifyPartitions>
</Disk>
</DiskConfiguration>
<ImageInstall>
<OSImage>
<InstallTo>
<DiskID>0</DiskID>
<PartitionID>1</PartitionID>
</InstallTo>
<WillShowUI>OnError</WillShowUI>
</OSImage>
</ImageInstall>
<UserData>
<AcceptEula>true</AcceptEula>
<FullName></FullName>
<Organization></Organization>
<ProductKey>
<WillShowUI>OnError</WillShowUI>
<Key>HYF8J-CVRMY-CM74G-RPHKF-PW487</Key>
</ProductKey>
</UserData>
</component>

<component name="Microsoft-Windows-PnpCustomizationNonWinPE" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DriverPaths>
<PathAndCredentials wcm:action="add" wcm:keyValue="1">
<Path>c:\_drivers</Path>
</PathAndCredentials>
</DriverPaths>
</component>

</settings>
<settings pass="specialize">
<component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RegisteredOrganization></RegisteredOrganization>
<TimeZone>Eastern Standard Time</TimeZone>
<RegisteredOwner></RegisteredOwner>
<AutoLogon>
<Password>
<Value></Value>
<PlainText>true</PlainText>
</Password>
<Username>administrateur</Username>
<LogonCount>10</LogonCount>
<Enabled>true</Enabled>
</AutoLogon>
<ProductKey>HYF8J-CVRMY-CM74G-RPHKF-PW487</ProductKey>
<ComputerName />
</component>
</settings>
<settings pass="oobeSystem">
<component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OOBE>
<ProtectYourPC>1</ProtectYourPC>
<NetworkLocation>Home</NetworkLocation>
</OOBE>
<FirstLogonCommands>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\VBSFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>1</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\txtFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>2</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\batFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>3</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\regFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>4</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\cmdFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>5</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\xmlFile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>6</Order>
<Description>notepad2</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Classes\logfile\Shell\Edit\command" /v "" /t REG_SZ /d "C:\WINDOWS\System32\Notepad2.exe %1" /f</CommandLine>
<Order>7</Order>
<Description>notepad2</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer" /v ForceClassicControlPanel /t REG_DWORD /d 1 /f</CommandLine>
<Order>20</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v ClassicViewState /t REG_DWORD /d 1 /f</CommandLine>
<Order>21</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v WebView /t REG_DWORD /d 0 /f</CommandLine>
<Order>22</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v HideIcons /t REG_DWORD /d 0 /f</CommandLine>
<Order>23</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v Hidden /t REG_DWORD /d 1 /f</CommandLine>
<Order>24</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v TaskbarGlomming /t REG_DWORD /d 0 /f</CommandLine>
<Order>25</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v TaskbarSizeMove /t REG_DWORD /d 1 /f</CommandLine>
<Order>26</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v TaskbarGlomLevel /t REG_DWORD /d 2 /f</CommandLine>
<Order>27</Order>
<Description>dont group icones in task bar explorer.exe setup</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Internet Explorer\IntelliForms" /v AskUser /t REG_DWORD /d 0 /f</CommandLine>
<Order>50</Order>
<Description>explorer.exe setup</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SYSTEM\CurrentControlSet\Control\CrashControl" /v AutoReboot /t REG_DWORD /d 0 /f</CommandLine>
<Order>51</Order>
<Description>blue screen crash</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SYSTEM\CurrentControlSet\Control\CrashControl" /v AUState /t REG_DWORD /d 1 /f</CommandLine>
<Order>52</Order>
<Description>blue screen crash</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v AUOptions /t REG_DWORD /d 1 /f</CommandLine>
<Order>60</Order>
<Description>windows update</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v AUState /t REG_DWORD /d 1 /f</CommandLine>
<Order>61</Order>
<Description>windows update</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer" /v ShellState /t REG_BINARY /d 2400000033080000000000000000000000000000010000000d0000000000000000000000 /f</CommandLine>
<Order>70</Order>
<Description>classic view</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer" /v IconUnderline /t REG_BINARY /d 030000 /f</CommandLine>
<Order>71</Order>
<Description>i dont know</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer" /v EnableAutoTray /t REG_DWORD /d 0 /f</CommandLine>
<Order>72</Order>
<Description>dont hide right side icons</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg delete "HKCU\Keyboard Layout\Preload" /va /f</CommandLine>
<Order>80</Order>
<Description>clavier langue requiert un reboot</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Keyboard Layout\Preload" /v 1 /t REG_SZ /d "00000c0c" /f</CommandLine>
<Order>81</Order>
<Description>clavier langue requiert un reboot</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Keyboard Layout\Substitutes" /v 00000c0c /t REG_SZ /d "00001009" /f</CommandLine>
<Order>82</Order>
<Description>keyboard language need restart sessiont</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion" /v DevicePath /t REG_EXPAND_SZ /d %SystemRoot%\inf;c:\_drivers /f</CommandLine>
<Order>83</Order>
<Description>will detect all drivers is asked fo hardware change</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v HideFileExt /t REG_DWORD /d 0 /f</CommandLine>
<Order>90</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced" /v ClassicViewState /t REG_DWORD /d 1 /f</CommandLine>
<Order>91</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\MediaPlayer\Preferences" /v AcceptedPrivacyStatement /t REG_DWORD /d 1 /f</CommandLine>
<Order>92</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\MediaPlayer\Preferences" /v FirstRun /t REG_DWORD /d 0 /f</CommandLine>
<Order>93</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Internet Explorer\Main" /v RunOnceHasShown /t REG_DWORD /d 1 /f</CommandLine>
<Order>94</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\Software\Microsoft\Internet Explorer\Main" /v RunOnceComplete /t REG_DWORD /d 1 /f</CommandLine>
<Order>95</Order>
<Description>Show file extensions in Explorer</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c xcopy c:\_appsmaison\jzip\*.* "c:\program files (x86)\jzip\" /y /c</CommandLine>
<Order>96</Order>
<Description>jzip 7zip</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c xcopy c:\_appsall\jzip\*.* "c:\program files (x86)\jzip\" /y /c</CommandLine>
<Order>97</Order>
<Description>jzip 7zip</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>REGSVR32.EXE "c:\program files (x86)\jzip\jZipShell.dll" /s</CommandLine>
<Order>98</Order>
<Description>jzip 7zip</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>msiexec /i c:\_appsall\screenhunterfree_prt_scr\setupscreenhunterfree.msi /quiet</CommandLine>
<Order>99</Order>
<Description>screenhunter</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>c:\_updates\dotnet\NDP452-KB2901907-x86-x64-AllOS-ENU.exe /q /norestart</CommandLine>
<Order>200</Order>
<Description>dotnet452</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_appsall\video_codecs_convert\K-Lite_Codec_Pack_1180_Standard.exe /verysilent</CommandLine>
<Order>203</Order>
<Description>codecs video</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_appsall\java_sun\jre-8u25-windows-i586.exe /s</CommandLine>
<Order>204</Order>
<Description>java32</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_appsall\java_sun\jre-8u25-windows-x64.exe /s</CommandLine>
<Order>205</Order>
<Description>java64</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_appsall\skype\Skype_silent.exe</CommandLine>
<Order>206</Order>
<Description>skype</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>c:\_appsmaison\microsoft_office_2013_64_fra_files\setup.exe /adminfile office2013unattend.MSP</CommandLine>
<Order>207</Order>
<Description>office2013</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>msiexec.exe /i c:\_appsmaison\Acrobat_X_Professional\AcroPro.msi EULA_ACCEPT=YES REGISTRATION_SUPPRESS=YES /qn</CommandLine>
<Order>208</Order>
<Description>AcroPro.msi</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c copy c:\_appsall\notepad2\notepad2.exe c:\windows\system32\notepad2.exe</CommandLine>
<Order>209</Order>
<Description>notepad2</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_appsall\chrome\chromeStandaloneSetup.exe /silent /install</CommandLine>
<Order>210</Order>
<Description>chrome</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>msiexec.exe /i "C:\_appsall\flashplayer\install_flash_player_20_active_x.msi" /qn</CommandLine>
<Order>211</Order>
<Description>install_flash_player_20_active_x</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>msiexec.exe /i "C:\_appsall\flashplayer\install_flash_player_20_plugin.msi" /qn</CommandLine>
<Order>212</Order>
<Description>install_flash_player_20_plugin</Description>
</SynchronousCommand>

<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c wmic useraccount where "name='administrateur'" set PasswordExpires=FALSE</CommandLine>
<Order>213</Order>
<Description>Disable password expiration for vagrant user</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c c:\_appsmaison\antivirus\avast_free_antivirus_setup.exe /silent /NORESTART /SP- /"Chrome"="false"</CommandLine>
<Order>215</Order>
<Description>antivirus avast</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>reg add HKLM\Software\Microsoft\Windows\CurrentVersion\RunOnce /v NewSetupScript /t Reg_SZ /d "c:\_updates\phase02.bat" /f</CommandLine>
<Order>216</Order>
<Description>patchbeforeie11</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\firefox\Firefox Setup 43.0.2.exe" -ms</CommandLine>
<Order>217</Order>
<Description>firefox</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\vcredist2010sp1\vcredist_x86.exe" /q /norestart</CommandLine>
<Order>218</Order>
<Description>vcredist2010sp1</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\vcredist2015\vc_redist.x86.exe" /q /norestart</CommandLine>
<Order>219</Order>
<Description>vcredist2015_32</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\vcredist2015\vc_redist.x64.exe" /q /norestart</CommandLine>
<Order>220</Order>
<Description>vcredist2015_64</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>msiexec /i c:\_appsall\MysqlOdbcConnector\mysql-connector-odbc-5.3.4-win32.msi /quiet</CommandLine>
<Order>221</Order>
<Description>mysql32odbc</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>msiexec /i c:\_appsall\MysqlOdbcConnector\mysql-connector-odbc-5.3.4-winx64.msi /quiet</CommandLine>
<Order>222</Order>
<Description>mysql64odbc</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\acrobat_reader_adobe\AdbeRdr11010_fr_FR.exe" /sPB /rs</CommandLine>
<Order>223</Order>
<Description>mysql64odbc</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c reg add "HKCU\SOFTWARE\Adobe\Acrobat Reader\11.0\AdobeViewer" /v EULA /t REG_DWORD /d 1 /f</CommandLine>
<Order>224</Order>
<Description>adobereadereulaaccepted</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c mkdir "c:\users\administrateur\desktop\A Faire - ToDo\"</CommandLine>
<Order>225</Order>
<Description>a faire apres reboot</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\patch windows phase 4-10 (4 heures reboot auto).lnk" /a:c /t:"c:\_updates\phase04.bat"</CommandLine>
<Order>226</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\activate windows.lnk" /a:c /t:"c:\_appsmaison\crack win7 Windows Loader sp1\Windows Loader.exe"</CommandLine>
<Order>229</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\activate office2013.lnk" /a:c /t:"c:\_appsmaison\microsoft_office_2013_64_fra_files\_activation_off2013_fra\Microsoft Toolkit.exe"</CommandLine>
<Order>230</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\Tweaks (setup profile).lnk" /a:c /t:"c:\_appsall\_01_tweaks.reg"</CommandLine>
<Order>231</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\Malwarebyte Setup.lnk" /a:c /t:"c:\_appsall\malwarebyte\mbam-setup-2.0.4.1028.exe"</CommandLine>
<Order>232</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\spywareblaster54 setup.lnk" /a:c /t:"c:\_appsall\spywareblaster\spywareblastersetup54.exe"</CommandLine>
<Order>233</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\virtualclonedrive setup.lnk" /a:c /t:"c:\_appsall\virtualclonedrive\SetupVirtualCloneDrive5470.exe"</CommandLine>
<Order>234</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\imageburn graveur dvd setup.lnk" /a:c /t:"c:\_appsall\imageburn\SetupImgBurn_2.5.7.0.exe"</CommandLine>
<Order>235</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd /c c:\_util\shortcut\Shortcut.exe /f:"%USERPROFILE%\Desktop\A Faire - ToDo\supprime les peripheriques et redetecte.lnk" /a:c /t:"c:\_drivers\scanfornewhardware.vbs"</CommandLine>
<Order>236</Order>
<Description></Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\vcredist2012upd4\vcredist_x86.exe" /q /norestart</CommandLine>
<Order>237</Order>
<Description>vcredist2012_32</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>cmd.exe /c "c:\_appsall\vcredist2012upd4\vcredist_x64.exe" /q /norestart</CommandLine>
<Order>238</Order>
<Description>vcredist2012_64</Description>
</SynchronousCommand>
<SynchronousCommand wcm:action="add">
<CommandLine>C:\Windows\System32\shutdown.exe /r /t 120</CommandLine>
<Order>250</Order>
<Description>restart</Description>
</SynchronousCommand>

</FirstLogonCommands>
<UserAccounts>
<LocalAccounts>
<LocalAccount wcm:action="add">
<Password>
<Value></Value>
<PlainText>true</PlainText>
</Password>
<Name>Administrateur</Name>
<Group>Administrators</Group>
</LocalAccount>
</LocalAccounts>
</UserAccounts>
<AutoLogon>
<Enabled>true</Enabled>
<Username>Administrateur</Username>
<LogonCount>3</LogonCount>
</AutoLogon>
<RegisteredOrganization>Maison</RegisteredOrganization>
<RegisteredOwner>Maison</RegisteredOwner>
</component>
</settings>
</unattend>

Monday, October 14, 2013

excel addin subs called from vba

Hi,

SITUATION:
excel 2007, 2010, 2013
tested on excel 2013 only
compiled as any cpu
visual studio 2012 with office kit installed

PROBLEM:
We have too many subs in many excel sheets in our compagny
I am about to make some new subs for sharepoint 2013 and they need to be accessible for everyone
As you know vba cannot really access sharepoint 2013 client object model (yeah yeah i could call the DLL with all parameters, but, no thanks)

SOLUTION:
Program a excel addin and include the subs in it

PROBLEM:
you cannot simply call a sub in a addin from VBA
you need to go through a test first ;)

So here is my resulting code:

after you create a normal excel addin, you get:
Public Class ThisAddIn

Private Sub ThisAddIn_Startup() Handles Me.Startup

End Sub

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

End Sub

end class

Your addin project must be called "exceladdin1" for this code

Change the code of the addin for this to be able to call any sub in your addin from vba:
(with parameter in bonus)
note: the test sub is "shared" to be able to call it from the other class created to be able to be called from vba

'=== use addin sub in vba in excel

'=== call from VBA:
'Sub CallVSTOMethod()
' Dim addIn As COMAddIn
' Dim automationObject As Object
' addIn = Application.COMAddIns("exceladdin1")
' automationObject = addIn.Object
' automationObject.ImportData("Hello world!")
'End Sub

'=== http://msdn.microsoft.com/en-us/library/vstudio/bb608614.aspx (video have the right code, demo have not it seems)
'=== video CallAddInFromVBA.wmv (working)

Imports System.Data
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
Public Interface IAddInUtilities
Sub ImportData(message As String)
End Interface

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)> _
Public Class AddInUtilities
Implements IAddInUtilities

' This method tries to write a string to cell A1 in the active worksheet.
Public Sub ImportData(message As String) Implements IAddInUtilities.ImportData
Call ThisAddIn.test(message)

End Sub
End Class

Public Class ThisAddIn

Private utilities As AddInUtilities

Protected Overrides Function RequestComAddInAutomationService() As Object
If utilities Is Nothing Then
utilities = New AddInUtilities()
End If
Return utilities
End Function

Public Shared Sub test(message As String)
MsgBox(message)
End Sub
'=== stas excel addin
'=== made with visual studio 2010
'=== debugged with excel 2013

'=== this addin rearrange 2 sheet source display: database, notes
'=== it generate a frame to enter data in it (bottom frame), sheet name: cartouche
'=== it calculate the right width for database and notes columns to fill an A2 form with the notes and data
'===
'=== goal: make the final result PDF to be approvable by 4 poeple (numeric signatures)
'=== goal: make final printed result usable in factory to adjust instruments settings for a machine

Private Sub ThisAddIn_Startup() Handles Me.Startup

End Sub

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

End Sub

end class

now put that in your vba macro

---------------------- vba macro
'=== call from VBA:
Sub CallVSTOMethod()
Dim addIn As COMAddIn
Dim automationObject As Object
addIn = Application.COMAddIns("exceladdin1")
automationObject = addIn.Object
automationObject.ImportData("Hello world!")
End Sub

Saturday, October 12, 2013

sharepoint 2013 copying files that have certain managed metadata terms to ntfs

Sharepoint 2013 - Visual studio 2012

SITUATION:
After migrating files to sharepoint and tagging them with managed metadata terms, our products are well managed. Every file needed to start a new project with a product model need approbation.

We have a site for each product, a document library for each product, and all our departements can work on the product itself before a new order (project) is processed.
So instead of starting a new order with an old order, we start it with a new product. That way, developpement and research can work on the product before we start a new order.

PROBLEM:
Unfortunatly, not all programs support sharepoint 2013 file access

So when a new order is started (new project or new submission) we copy all thoses files on a ntfs drive. Mainly because we have automation programs and cad software that does not support sharepoint 2013 web access very well.

Acrobat support the metadata terms tagging since 11.0.4, autocad does not. Webdav exist since a lot of years and is not yet supported by all software for accessing files. So i bet sharepoint with terms, will take about 20 years to be supported by all software.

Now when your managed metadata fields/columns in sharepoint are mandatory, you cannot even approve a document if the file was saved in sharepoint from a windows application that does not support sharepoint 2013, because the windows that ask you to enter the managed metadata does not pop up when the application does not support sharepoint. (office and acrobat have an activex that manage the save of the file to be able to choose the metadata while saving)

SOLUTION:
So programming such an active x to manage file saving for all the apllications in the world is too long for me. So I will simply put the new project in a normal windows ntfs file system when we start the new project.

I used the client object method to:
- query the main sharepoint site for products (http://sharepointsite/produits)
- query all the sub site (each sub site is a product)
- query all the documents library in each site (1 for each product)
- query the document library fields (columns) and extract the name of each column that use managed metadata
- present a list of choices to choose what product you want (sub site name, doc library name, metadata column name (must have same name as sub site)

Now the user choose: ACD (site name), Documents (library name), ACD (metadata fields with same name as site)

After that, i need to query the termstore to get sub product specification (type and number of rotors)
termstore:
- ACD
- RI
- ACD -- flux
- ACD -- type2
- ACD -- flux --- 4 rotors
- ACD -- flux --- 6 rotors

So i present the user with the second choice:
What type of ACD:
1 type01
2 type02
3 type03
4 type04

After this choice is made i get from the resultant term GUID the childrens terms from termstore
and present the third level of product choice:
type 02 was choosen
now choose sub specification:
1 2 rotors
2 4 rotors
3 6 rotors with stuff

I save the GUID of the 3 choices the user made in variables

Then i start scanning all the documents in the library that was choosen, in the site that was choosen

i also scanned the metadata field for all terms collections (multiple choices for each product)
Each document that have 1 of the 3 terms in the choices are selected to be copied in the new product
(in the managed metadata field of the document)

Note:
a document in sharepoint can now be tagger for ACD, type01, 4 rotor
if we make a new ACD we need the documents tagged ACD
if it's type01 we need the documents tagged type01
if a document is common for all type01, then we tag all sub terms (2 rotor, 4 rotor, 6 rotor)
etc.

I was told by many post on internet that this could not be done with client object model (COM) but it can.

PROBLEM:
the approved status of sharepoint is not very bright
if you show unnaproved files in your library, sharepoint will tell the file 3.1 is approved but it is not. only 3.0 is the right version
Now you can say hide unnaproved files, but the approver will still see then and the same problem come back

So i had to scan all version of all files to find last approved version by scanning the version number digits (3.1, i get version 3.0 url)

After that i asked kindly sharepoint to extract version 3.0 but it cannot be done
So i used the webclient to "download" the file with the version URL and it worked

Also, sharepoint was not always responding to a web query from client object model. So i had to use the timeout and make 3 request if neceesary, then a REAL error if all 3 request failed.

In short:
- choose product site, doc library that contain a metadata field that have the same name as site (this give term for product)
- ask termstore for subterms to make a sub choice
- choose product type (term)
- ask termstore for subterms to make a sub choice
- choose product sub type (number of rotors)
- scan files in the library
- scan terms fields of the file and choose every file that have one term common with any choice we made
- scan all version of the file and get URL of last major version (last approved version)
- download file and put it in a ntfs folder to start a new project

All this with COM (client object model) in a vb.net 2012 program running on computer client

Sorry i cannot post code here, it's a business project.
But i can gladly post some part of it if asked.

Monday, September 2, 2013

internet explorer 10 frame correction in web interface code

Hi,

I created a web interface to manage most of my vbs/wsh scripts

With the arrival of internet explorer 10, my frames were empty

So, in any of my script that use the web interface, change thoses lines:

set flef = oie.document.frames("left").document
set fmid = oie.document.frames("middle").document
set fbot = oie.document.frames("bottom").document

For these lines:

fileversion = objFSO.GetFileVersion("C:\program files\internet explorer\iexplore.exe")
finddot = instr(fileversion,".")
fileversion2 = left(fileversion,finddot-1)

if fileversion2 = "10" or fileversion2 = "11" then
'=== ie10 frame access
set flef = oie.parent.document.getElementByid("left").contentdocument
set fmid = oie.parent.document.getElementByid("middle").contentdocument
set fbot = oie.parent.document.getElementByid("bottom").contentdocument
else
set flef = oie.document.frames("left").document
set fmid = oie.document.frames("middle").document
set fbot = oie.document.frames("bottom").document
end if