Full Script for Excel Spreadsheet Import Automation

Ian G · Feb 24, 2010

I know this post is really old and I doubt I will get a reply.. Can you post your working script for excel spreadsheets? This is exactly what I have been looking for. Good work!

Phil Hibbs wrote:

Fixed it - I cannot use the module name as a collection index, I needto loop
07-Jan-10

Fixed it - I cannot use the module name as a collection index, I nee
to loop through them checking the Name property of the VBComponent

Phil Hibbs.

Previous Posts In This Thread:

Updating the VBA code in multiple spreadsheets
I asked about this a while ago, now I have a solution. I have use
AutoIt (http://www.autoitscript.com/) , but I think this could also b
done in VB

It can work in one of two ways

1. Run it, select the file that contains the new VBA code, then selec
the Excel spreadsheet to updat
2. Drag and drop a set of files onto a compiled version, and you wil
only be prompted for the VBA cod

The reason I did the latter rather than just multi-selecting in th
File Open Dialog is that I want to be able to process multiple file
across multiple directories, so I search in Explorer and then drag
set of search results onto the executable

The first line of the VBA code file must be in this format
`Name=MyModul

This specifies the module that will be removed, and the newly importe
module will be given this name

This is the code of the AutoIt script

$oExcel = ObjCreate("Excel.Application"
$oExcel.Visible =
$ModuleCode = FileOpenDialog("Select Excel File", "C:\", "VBA Modul
Code (*.txt;*.bas)", 1
If @error Then Exi
$CodeFile = FileOpen( $ModuleCode, 0
$ModuleName = FileReadLine( $CodeFile
FileClose( $CodeFile
If StringLeft( $ModuleName, 6 ) = "'Name=" The
$ModuleName = StringMid( $ModuleName, 7
If $CmdLine[0] > 0 The
$FileName = "
For $i = 1 To $CmdLine[0
$FileName &= "|" & $CmdLine[$i
Nex
$FileName = StringMid( $FileName, 2 ) ; remove the first
characte
Els
$FileName = FileOpenDialog("Select Excel File", "C:\", "Exce
Workbooks (*.xls)", 1
If @error Then Exi
EndI
$xlscount =
For $xls In StringSplit( $FileName, "|", 2
ReplaceMacro( $xls, $ModuleName, $ModuleCode
$xlscount +=
Nex
MsgBox( 1, "Finished", $xlscount & " files updated"
Els
MsgBox( 1, "Error", "First line must begin with 'Name=
EndI

Func ReplaceMacro( $FileName, $ModuleName, $ModuleCode
$oExcel.WorkBooks.Open($FileName
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponent
For $oModule in $oModule
If $oModule.Type = 1 And $oModule.Name = $ModuleName The
$oModules.Remove( $oModule
EndI
Nex
$oModules.Import( $ModuleCode
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponent
$ModuleCount =
For $oModule in $oModule
$ModuleCount +=
If $ModuleCount = $oModules.Count The
$oModule.Name = $ModuleNam
EndI
Nex
$oExcel.ActiveWorkbook.Sav
$oExcel.ActiveWorkbook.Clos
$oExcel.Qui
EndFun

-
Phil Hibbs.

I am now trying to port this to Excel, and I am hitting a problem wherethe
I am now trying to port this to Excel, and I am hitting a problem wher
the Remove method fails with "Object does not support this method o
property"

Sub UpdateVBA(
Dim oExcel As Applicatio
Dim oComponent As Objec

Set oExcel = New Excel.Applicatio
Set oBook = oExcel.Workbooks.Open("C:\Test.xls", 0, False, , ,
True
Set oComponent = oBook.VBProject.VBComponents("TestModule"
oBook.VBProject.VBComponents.Remove (oComponent) ' <== FAI
oBook.VBProject.VBComponents.Import ("C:\TestModule.txt"
oBook.VBProject.VBComponent
(oBook.VBProject.VBComponents.Count).Name = "TestModule
oBook.Clos
oExcel.Qui

End Su

Any ideas

Phil Hibbs.

Fixed it - I cannot use the module name as a collection index, I needto loop
Fixed it - I cannot use the module name as a collection index, I nee
to loop through them checking the Name property of the VBComponent

Phil Hibbs.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Generic Feed Parsers Redux
http://www.eggheadcafe.com/tutorial...6-acd41f462063/generic-feed-parsers-redu.aspx

Phil Hibbs · Feb 24, 2010

I know this post is really old and I doubt I will get a reply..
Can you post your working script for excel spreadsheets?
This is exactly what I have been looking for. Good work!

Here's the module. The layout of the sheet that stores the names of
the spreadsheets and modules to update should be clear from the
constants. You might also need to enable Microsoft Scripting Runtime,
in the Tools->References menu in the VBA code editor window.

'Name=UpdateVBA
'Ver=1.0
'Author=Phil Hibbs
'Copyright=Capgemini 2009-2010
'
' Updates the VBA code in multiple spreadsheets from text files
'
' Buttons generated by http://www.grsites.com/generate/resultbyid/6275218/

Const SelectCol = 1
Const PathCol = 2
Const FileCol = 3
Const ModuleCol = 4
Const CodeCol = 5
Const DoneCol = 6

Const HeaderRow = 1

Sub UpdateVBA()
Dim oExcel As Application
Dim oBook As Workbook
Dim i As Integer
Dim j As Integer
Dim FileName As String
Dim PrevName As String
Dim oComponents As Object
Dim ErrNum As Long

On Error GoTo ErrorHandler

Set oExcel = New Excel.Application
oExcel.DisplayAlerts = False

i = HeaderRow + 1
While Cells(i, FileCol) <> ""
Cells(i, DoneCol) = ""
i = i + 1
Wend

i = HeaderRow + 1
While Cells(i, FileCol) <> ""
If Cells(i, SelectCol) <> "" Then
Cells(i, DoneCol).Activate
PrevName = FileName
FileName = Cells(i, FileCol)
If Cells(i, PathCol) <> "" Then FileName = Cells(i,
PathCol) & "\" & FileName
If PrevName <> FileName Then
If Not oBook Is Nothing Then
oBook.Close SaveChanges:=True
End If
Set oBook = oExcel.Workbooks.Open(FileName, 0,
False, , , , True)
End If
Set oComponents = oBook.VBProject.VBComponents
For j = oComponents.Count To 1 Step -1
If oComponents(j).Name = Cells(i, ModuleCol).Text Then
oComponents.Remove oComponents(j)
Exit For
End If
Next j
oComponents.Import (Cells(i, CodeCol))
oComponents(oComponents.Count).Name = Cells(i, ModuleCol)
oExcel.Run ("UpdateMacro")
Cells(i, DoneCol) = "ü"
End If
i = i + 1
Wend
If Not oBook Is Nothing Then
oBook.Close SaveChanges:=True
End If

Exit Sub

ErrorHandler:
'Store the error
ErrNum = Err.Number
If Err.Description = "The macro 'UpdateMacro' cannot be found."
Then Resume Next

oExcel.Quit

Err.Raise ErrNum

End Sub

Sub FillFiles()

Dim oFSO As FileSystemObject 'The File System Object used for
all File IO
Dim i As Integer
Dim StartPath As String
Dim Path As String

i = Selection.Row
Path = Cells(i, PathCol)
If Path = "" Then
If InStr(Cells(i - 1, PathCol), ":") > 0 Then
StartPath = Cells(i - 1, PathCol)
End If
Path = GetSelectedFolder(StartPath)
If Path = "" Then
Exit Sub
End If
End If

Cells(i, PathCol) = Path
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(Path)

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
If Cells(i, PathCol) = "" Then
Cells(i, PathCol).Formula = "=" & Num2Col(PathCol) &
Trim(i - 1)
End If
Cells(i, FileCol) = file.Name
i = i + 1
End If
Next file
Set oFSO = Nothing

End Sub

Function GetSelectedFolder(Optional strPath As String) As String
Dim objFldr As FileDialog
Set objFldr = Application.FileDialog(msoFileDialogFolderPicker)
With objFldr
.Title = "Select a folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GetSelectedFolder = "": Exit Function
GetSelectedFolder = .SelectedItems(1)
End With
Set objFldr = Nothing
End Function

Function Num2Col(ColNum As Integer) As String

Dim Col As Integer
Dim Letter As Integer

Letter = 0
Num2Col = ""

If ColNum > 26 Then
Letter = ColNum / 26
Num2Col = Chr(Letter + 64)
End If
Letter = ColNum Mod 26
Num2Col = Num2Col & Chr(Letter + 64)

End Function

Ian G · Feb 26, 2010

This code expects you to have a list of code/forms in column A2:A# and a list of excel spreadsheets in B2:B#. It is far from perfect but would be a great starting point for someone trying to do the same. Enjoy!

<div class="reCodeBlock" style="border: 1px solid #7f9db9; overflow-y: auto;">
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Option</code> <code style="color: #000000;">Explicit</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Sub</code> <code style="color: #000000;">PushPatch()</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">updatesWorkbookName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cSheet </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Worksheet</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, xlsfileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, fileNameCell </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">fileName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNames </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, codefileNamesH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">Msg </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, MsgH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">updatesWorkbookName = ActiveWorkbook.Name</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cSheet = Workbooks(updatesWorkbookName).ActiveSheet</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get names of code files and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNamesH = </code><code style="color: blue;">"Code modules to push out:"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNames = codefileNamesH</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">codefileNameRange = cSheet.Range(</code><code style="color: blue;">"A2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 1).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">codefileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codefileNames = codefileNames & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">codefileNames = codefileNames & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(codefileNames = codefileNamesH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No code files listed or exist to push out"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">MsgH = </code><code style="color: blue;">"These files will be updated with code modules, is this ok?"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Msg = MsgH</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get the names of the workbooks and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">xlsfileNameRange = cSheet.Range(</code><code style="color: blue;">"B2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 2).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = Msg & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg = MsgH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No valid workbooks found to update with modules"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>      </code><span style="margin-left: 24px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">numImported </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Integer</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">filePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cWorkbook </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeFilePaths </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Ask user if can update</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(MsgBox(Msg & vbLf & codefileNames, vbOKCancel, </code><code style="color: blue;">"Update all these workbooks?"</code><code style="color: #000000;">) = vbOK) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeFilePaths = codefileNameRange.Resize(ColumnSize:=1)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = </code><code style="color: blue;">""</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">numImported = 0</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Loop through workbooks and Open, Remove Modules, Import Modules, Close.</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">filePath = fileNameCell.Value</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">fileName = Dir(filePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName <> vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cWorkbook = Workbooks.Open(filePath)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #006699; font-weight: bold;">Not</code> <code style="color: #000000;">(cWorkbook </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(ImportModules(cWorkbook, codeFilePaths)) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #008200;">'Error in Importing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Importing)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">numImported = numImported + 1</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #008200;">'Close the workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook.Close SaveChanges:=</code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Unload cWorkbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook = </code><code style="color: #006699; font-weight: bold;">Nothing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Not valid file type)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & filePath & </code><code style="color: blue;">" (Does Not Exist)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Display Errors</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg <> </code><code style="color: blue;">""</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = vbLf & </code><code style="color: blue;">"Errors for the following Excel Spreadsheets:"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = numImported & </code><code style="color: blue;">" Workbooks imported successfully!"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Call</code> <code style="color: #000000;">MsgBox(Msg)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #000000;">EndPush:</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Workbooks(updatesWorkbookName).Activate</code></span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Sub</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Function</code> <code style="color: #000000;">ImportModules(wb </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook, modulePathsArray </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">ErrorImporting </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">False</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">modulePathsArray</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Dir(codePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Left(codeName, InStrRev(codeName, </code><code style="color: blue;">"."</code><code style="color: #000000;">, -1) - 1)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">With</code> <code style="color: #000000;">wb.VBProject.VBComponents</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Remove .Item(codeName)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Import codePath</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'Check if the module imported ok</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(.Item(codeName) </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">With</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">codePath</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ImportModules = ErrorImporting</code></span></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Function</code></span></div>
</div>

Ian G wrote:

Full Script for Excel Spreadsheet Import Automation
24-Feb-10

I know this post is really old and I doubt I will get a reply.. Can you post your working script for excel spreadsheets? This is exactly what I have been looking for. Good work!

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorial...f-1716445b26bc/sending-smtp-email-from-w.aspx

Ian G · Feb 26, 2010

Option Explicit

Sub PushPatch()
Dim updatesWorkbookName As String
Dim cSheet As Worksheet
Dim codefileNameRange As Range, xlsfileNameRange As Range, fileNameCell As Range
Dim fileName As String
Dim codefileNames As String, codefileNamesH As String
Dim Msg As String, MsgH As String

updatesWorkbookName = ActiveWorkbook.Name
Set cSheet = Workbooks(updatesWorkbookName).ActiveSheet

'Get names of code files and check they exist
codefileNamesH = "Code modules to push out:"
codefileNames = codefileNamesH
Set codefileNameRange = cSheet.Range("A2", cSheet.Cells(Rows.Count, 1).End(xlUp).Address)
For Each fileNameCell In codefileNameRange
On Error Resume Next
fileName = Dir(fileNameCell.Value, vbDirectory)
codefileNames = codefileNames & vbLf & fileName
If (fileName = vbNullString) Then
'File Does not Exist
codefileNames = codefileNames & " (File Not Found - No Update)"
End If
Next fileNameCell
If (codefileNames = codefileNamesH) Then
MsgBox ("No code files listed or exist to push out")
GoTo EndPush
End If

MsgH = "These files will be updated with code modules, is this ok?"
Msg = MsgH
'Get the names of the workbooks and check they exist
Set xlsfileNameRange = cSheet.Range("B2", cSheet.Cells(Rows.Count, 2).End(xlUp).Address)
For Each fileNameCell In xlsfileNameRange
On Error Resume Next
fileName = Dir(fileNameCell.Value, vbDirectory)
Msg = Msg & vbLf & fileName
If (fileName = vbNullString) Then
'File Does not Exist
Msg = Msg & " (File Not Found - No Update)"
End If
Next fileNameCell
If (Msg = MsgH) Then
MsgBox ("No valid workbooks found to update with modules")
GoTo EndPush
End If

On Error Resume Next
Dim numImported As Integer
Dim filePath As String
Dim cWorkbook As Workbook
Dim codeFilePaths As Variant
'Ask user if can update
If (MsgBox(Msg & vbLf & codefileNames, vbOKCancel, "Update all these workbooks?") = vbOK) Then
codeFilePaths = codefileNameRange.Resize(ColumnSize:=1)
Msg = ""
numImported = 0
'Loop through workbooks and Open, Remove Modules, Import Modules, Close.
For Each fileNameCell In xlsfileNameRange
filePath = fileNameCell.Value
fileName = Dir(filePath, vbDirectory)
If (fileName <> vbNullString) Then
Set cWorkbook = Workbooks.Open(filePath)
If Not (cWorkbook Is Nothing) Then
If (ImportModules(cWorkbook, codeFilePaths)) Then
'Error in Importing
Msg = Msg & vbLf & fileName & " (Importing)"
Else
numImported = numImported + 1
End If
'Close the workbook
cWorkbook.Close SaveChanges:=True
Unload cWorkbook
cWorkbook = Nothing
Else
Msg = Msg & vbLf & fileName & " (Not valid file type)"
End If
Else
Msg = Msg & vbLf & filePath & " (Does Not Exist)"
End If
Next fileNameCell

'Display Errors
If (Msg <> "") Then
Msg = vbLf & "Errors for the following Excel Spreadsheets:" & Msg
End If
Msg = numImported & " Workbooks imported successfully!" & Msg
Call MsgBox(Msg)
End If

EndPush:
Workbooks(updatesWorkbookName).Activate
End Sub

Function ImportModules(wb As Workbook, modulePathsArray As Variant) As Boolean
Dim codePath As Variant
Dim codeName As String
Dim ErrorImporting As Boolean
ErrorImporting = False

On Error Resume Next
For Each codePath In modulePathsArray
codeName = Dir(codePath, vbDirectory)
codeName = Left(codeName, InStrRev(codeName, ".", -1) - 1)
With wb.VBProject.VBComponents
.Remove .Item(codeName)
.Import codePath

'Check if the module imported ok
If (.Item(codeName) Is Nothing) Then
ErrorImporting = True
End If
End With
Next codePath
ImportModules = ErrorImporting
End Function

Ian G wrote:

Wrote my Own - Mass Import Modules & UserForms to Multiple Excel Spreadsheets
25-Feb-10

This code expects you to have a list of code/forms in column A2:A# and a list of excel spreadsheets in B2:B#. It is far from perfect but would be a great starting point for someone trying to do the same. Enjoy!

<div class="reCodeBlock" style="border: 1px solid #7f9db9; overflow-y: auto;">
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Option</code> <code style="color: #000000;">Explicit</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Sub</code> <code style="color: #000000;">PushPatch()</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">updatesWorkbookName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cSheet </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Worksheet</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, xlsfileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, fileNameCell </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">fileName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNames </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, codefileNamesH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">Msg </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, MsgH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">updatesWorkbookName = ActiveWorkbook.Name</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cSheet = Workbooks(updatesWorkbookName).ActiveSheet</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get names of code files and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNamesH = </code><code style="color: blue;">"Code modules to push out:"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNames = codefileNamesH</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">codefileNameRange = cSheet.Range(</code><code style="color: blue;">"A2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 1).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">codefileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codefileNames = codefileNames & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">codefileNames = codefileNames & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(codefileNames = codefileNamesH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No code files listed or exist to push out"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">MsgH = </code><code style="color: blue;">"These files will be updated with code modules, is this ok?"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Msg = MsgH</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get the names of the workbooks and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">xlsfileNameRange = cSheet.Range(</code><code style="color: blue;">"B2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 2).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = Msg & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg = MsgH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No valid workbooks found to update with modules"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>      </code><span style="margin-left: 24px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">numImported </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Integer</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">filePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cWorkbook </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeFilePaths </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Ask user if can update</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(MsgBox(Msg & vbLf & codefileNames, vbOKCancel, </code><code style="color: blue;">"Update all these workbooks?"</code><code style="color: #000000;">) = vbOK) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeFilePaths = codefileNameRange.Resize(ColumnSize:=1)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = </code><code style="color: blue;">""</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">numImported = 0</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Loop through workbooks and Open, Remove Modules, Import Modules, Close.</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">filePath = fileNameCell.Value</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">fileName = Dir(filePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName <> vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cWorkbook = Workbooks.Open(filePath)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #006699; font-weight: bold;">Not</code> <code style="color: #000000;">(cWorkbook </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(ImportModules(cWorkbook, codeFilePaths)) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #008200;">'Error in Importing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Importing)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">numImported = numImported + 1</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #008200;">'Close the workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook.Close SaveChanges:=</code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Unload cWorkbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook = </code><code style="color: #006699; font-weight: bold;">Nothing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Not valid file type)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & filePath & </code><code style="color: blue;">" (Does Not Exist)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Display Errors</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg <> </code><code style="color: blue;">""</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = vbLf & </code><code style="color: blue;">"Errors for the following Excel Spreadsheets:"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = numImported & </code><code style="color: blue;">" Workbooks imported successfully!"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Call</code> <code style="color: #000000;">MsgBox(Msg)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #000000;">EndPush:</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Workbooks(updatesWorkbookName).Activate</code></span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Sub</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Function</code> <code style="color: #000000;">ImportModules(wb </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook, modulePathsArray </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">ErrorImporting </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">False</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">modulePathsArray</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Dir(codePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Left(codeName, InStrRev(codeName, </code><code style="color: blue;">"."</code><code style="color: #000000;">, -1) - 1)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">With</code> <code style="color: #000000;">wb.VBProject.VBComponents</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Remove .Item(codeName)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Import codePath</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'Check if the module imported ok</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(.Item(codeName) </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">With</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">codePath</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ImportModules = ErrorImporting</code></span></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Function</code></span></div>
</div>

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
More Fun with Fluent NHibernate Automapping
http://www.eggheadcafe.com/tutorial...9-81ee42171b00/more-fun-with-fluent-nhib.aspx

Ian G · Feb 26, 2010

Option Explicit

Sub PushPatch()
Dim updatesWorkbookName As String
Dim cSheet As Worksheet
Dim codefileNameRange As Range, xlsfileNameRange As Range, fileNameCell As Range
Dim fileName As String
Dim codefileNames As String, codefileNamesH As String
Dim Msg As String, MsgH As String

updatesWorkbookName = ActiveWorkbook.Name
Set cSheet = Workbooks(updatesWorkbookName).ActiveSheet

'Get names of code files and check they exist
codefileNamesH = "Code modules to push out:"
codefileNames = codefileNamesH
Set codefileNameRange = cSheet.Range("A2", cSheet.Cells(Rows.Count, 1).End(xlUp).Address)
For Each fileNameCell In codefileNameRange
On Error Resume Next
fileName = Dir(fileNameCell.Value, vbDirectory)
codefileNames = codefileNames & vbLf & fileName
If (fileName = vbNullString) Then
'File Does not Exist
codefileNames = codefileNames & " (File Not Found - No Update)"
End If
Next fileNameCell
If (codefileNames = codefileNamesH) Then
MsgBox ("No code files listed or exist to push out")
GoTo EndPush
End If

MsgH = "These files will be updated with code modules, is this ok?"
Msg = MsgH
'Get the names of the workbooks and check they exist
Set xlsfileNameRange = cSheet.Range("B2", cSheet.Cells(Rows.Count, 2).End(xlUp).Address)
For Each fileNameCell In xlsfileNameRange
On Error Resume Next
fileName = Dir(fileNameCell.Value, vbDirectory)
Msg = Msg & vbLf & fileName
If (fileName = vbNullString) Then
'File Does not Exist
Msg = Msg & " (File Not Found - No Update)"
End If
Next fileNameCell
If (Msg = MsgH) Then
MsgBox ("No valid workbooks found to update with modules")
GoTo EndPush
End If

On Error Resume Next
Dim numImported As Integer
Dim filePath As String
Dim cWorkbook As Workbook
Dim codeFilePaths As Variant
'Ask user if can update
If (MsgBox(Msg & vbLf & codefileNames, vbOKCancel, "Update all these workbooks?") = vbOK) Then
codeFilePaths = codefileNameRange.Resize(ColumnSize:=1)
Msg = ""
numImported = 0
'Loop through workbooks and Open, Remove Modules, Import Modules, Close.
For Each fileNameCell In xlsfileNameRange
filePath = fileNameCell.Value
fileName = Dir(filePath, vbDirectory)
If (fileName <> vbNullString) Then
Set cWorkbook = Workbooks.Open(filePath)
If Not (cWorkbook Is Nothing) Then
If (ImportModules(cWorkbook, codeFilePaths)) Then
'Error in Importing
Msg = Msg & vbLf & fileName & " (Importing)"
Else
numImported = numImported + 1
End If
'Close the workbook
cWorkbook.Close SaveChanges:=True
Unload cWorkbook
cWorkbook = Nothing
Else
Msg = Msg & vbLf & fileName & " (Not valid file type)"
End If
Else
Msg = Msg & vbLf & filePath & " (Does Not Exist)"
End If
Next fileNameCell

'Display Errors
If (Msg <> "") Then
Msg = vbLf & "Errors for the following Excel Spreadsheets:" & Msg
End If
Msg = numImported & " Workbooks imported successfully!" & Msg
Call MsgBox(Msg)
End If

EndPush:
Workbooks(updatesWorkbookName).Activate
End Sub

Function ImportModules(wb As Workbook, modulePathsArray As Variant) As Boolean
Dim codePath As Variant
Dim codeName As String
Dim ErrorImporting As Boolean
ErrorImporting = False

On Error Resume Next
For Each codePath In modulePathsArray
codeName = Dir(codePath, vbDirectory)
codeName = Left(codeName, InStrRev(codeName, ".", -1) - 1)
With wb.VBProject.VBComponents
.Remove .Item(codeName)
.Import codePath

'Check if the module imported ok
If (.Item(codeName) Is Nothing) Then
ErrorImporting = True
End If
End With
Next codePath
ImportModules = ErrorImporting
End Function

Ian G wrote:

Wrote my Own - Mass Import Modules & UserForms to Multiple Excel Spreadsheets
25-Feb-10

This code expects you to have a list of code/forms in column A2:A# and a list of excel spreadsheets in B2:B#. It is far from perfect but would be a great starting point for someone trying to do the same. Enjoy!

<div class="reCodeBlock" style="border: 1px solid #7f9db9; overflow-y: auto;">
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Option</code> <code style="color: #000000;">Explicit</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Sub</code> <code style="color: #000000;">PushPatch()</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">updatesWorkbookName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cSheet </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Worksheet</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, xlsfileNameRange </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range, fileNameCell </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Range</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">fileName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codefileNames </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, codefileNamesH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">Msg </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code><code style="color: #000000;">, MsgH </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">updatesWorkbookName = ActiveWorkbook.Name</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cSheet = Workbooks(updatesWorkbookName).ActiveSheet</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get names of code files and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNamesH = </code><code style="color: blue;">"Code modules to push out:"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">codefileNames = codefileNamesH</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">codefileNameRange = cSheet.Range(</code><code style="color: blue;">"A2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 1).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">codefileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codefileNames = codefileNames & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">codefileNames = codefileNames & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(codefileNames = codefileNamesH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No code files listed or exist to push out"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>          </code><span style="margin-left: 40px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">MsgH = </code><code style="color: blue;">"These files will be updated with code modules, is this ok?"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Msg = MsgH</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Get the names of the workbooks and check they exist</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">xlsfileNameRange = cSheet.Range(</code><code style="color: blue;">"B2"</code><code style="color: #000000;">, cSheet.Cells(Rows.Count, 2).</code><code style="color: #006699; font-weight: bold;">End</code><code style="color: #000000;">(xlUp).Address)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">fileName = Dir(fileNameCell.Value, vbDirectory)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName = vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'File Does not Exist</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = Msg & </code><code style="color: blue;">" (File Not Found - No Update)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg = MsgH) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">MsgBox (</code><code style="color: blue;">"No valid workbooks found to update with modules"</code><code style="color: #000000;">)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">GoTo</code> <code style="color: #000000;">EndPush</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>      </code><span style="margin-left: 24px ! important;"> </span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">numImported </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Integer</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">filePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">cWorkbook </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeFilePaths </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #008200;">'Ask user if can update</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(MsgBox(Msg & vbLf & codefileNames, vbOKCancel, </code><code style="color: blue;">"Update all these workbooks?"</code><code style="color: #000000;">) = vbOK) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeFilePaths = codefileNameRange.Resize(ColumnSize:=1)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = </code><code style="color: blue;">""</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">numImported = 0</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Loop through workbooks and Open, Remove Modules, Import Modules, Close.</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">fileNameCell </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">xlsfileNameRange</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">filePath = fileNameCell.Value</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">fileName = Dir(filePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(fileName <> vbNullString) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Set</code> <code style="color: #000000;">cWorkbook = Workbooks.Open(filePath)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #006699; font-weight: bold;">Not</code> <code style="color: #000000;">(cWorkbook </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(ImportModules(cWorkbook, codeFilePaths)) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #008200;">'Error in Importing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Importing)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                        </code><span style="margin-left: 96px ! important;"><code style="color: #000000;">numImported = numImported + 1</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #008200;">'Close the workbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook.Close SaveChanges:=</code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Unload cWorkbook</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">cWorkbook = </code><code style="color: #006699; font-weight: bold;">Nothing</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>                    </code><span style="margin-left: 80px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & fileName & </code><code style="color: blue;">" (Not valid file type)"</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">Else</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">Msg = Msg & vbLf & filePath & </code><code style="color: blue;">" (Does Not Exist)"</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">fileNameCell</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #008200;">'Display Errors</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(Msg <> </code><code style="color: blue;">""</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">Msg = vbLf & </code><code style="color: blue;">"Errors for the following Excel Spreadsheets:"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">Msg = numImported & </code><code style="color: blue;">" Workbooks imported successfully!"</code> <code style="color: #000000;">& Msg</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">Call</code> <code style="color: #000000;">MsgBox(Msg)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #000000;">EndPush:</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">Workbooks(updatesWorkbookName).Activate</code></span></span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Sub</code></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"> </span></div>
<div style="background-color: #ffffff;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">Function</code> <code style="color: #000000;">ImportModules(wb </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #000000;">Workbook, modulePathsArray </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Variant</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">codeName </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">String</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Dim</code> <code style="color: #000000;">ErrorImporting </code><code style="color: #006699; font-weight: bold;">As</code> <code style="color: #006699; font-weight: bold;">Boolean</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">False</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">On</code> <code style="color: #006699; font-weight: bold;">Error</code> <code style="color: #006699; font-weight: bold;">Resume</code> <code style="color: #006699; font-weight: bold;">Next</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">For</code> <code style="color: #006699; font-weight: bold;">Each</code> <code style="color: #000000;">codePath </code><code style="color: #006699; font-weight: bold;">In</code> <code style="color: #000000;">modulePathsArray</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Dir(codePath, vbDirectory)</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #000000;">codeName = Left(codeName, InStrRev(codeName, </code><code style="color: blue;">"."</code><code style="color: #000000;">, -1) - 1)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">With</code> <code style="color: #000000;">wb.VBProject.VBComponents</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Remove .Item(codeName)</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #000000;">.Import codePath</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"> </span></span></div>
<div style="background-color: #ffffff;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #008200;">'Check if the module imported ok</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">If</code> <code style="color: #000000;">(.Item(codeName) </code><code style="color: #006699; font-weight: bold;">Is</code> <code style="color: #006699; font-weight: bold;">Nothing</code><code style="color: #000000;">) </code><code style="color: #006699; font-weight: bold;">Then</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>                </code><span style="margin-left: 64px ! important;"><code style="color: #000000;">ErrorImporting = </code><code style="color: #006699; font-weight: bold;">True</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>            </code><span style="margin-left: 48px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">If</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>        </code><span style="margin-left: 32px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">With</code></span></span></div>
<div style="background-color: #f8f8f8;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #006699; font-weight: bold;">Next</code> <code style="color: #000000;">codePath</code></span></span></div>
<div style="background-color: #ffffff;"><span><code>    </code><span style="margin-left: 16px ! important;"><code style="color: #000000;">ImportModules = ErrorImporting</code></span></span></div>
<div style="background-color: #f8f8f8;"><span style="margin-left: 0px ! important;"><code style="color: #006699; font-weight: bold;">End</code> <code style="color: #006699; font-weight: bold;">Function</code></span></div>
</div>

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Creating a WPF Custom Control
http://www.eggheadcafe.com/tutorial...9-c0b45fb68b78/creating-a-wpf-custom-con.aspx