ExcelBanter - Updating the VBA code in multiple spreadsheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Updating the VBA code in multiple spreadsheets (https://www.excelbanter.com/excel-programming/438101-updating-vba-code-multiple-spreadsheets.html)

Updating the VBA code in multiple spreadsheets

I asked about this a while ago, now I have a solution. I have used
AutoIt (http://www.autoitscript.com/) , but I think this could also be
done in VB.

It can work in one of two ways:

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

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

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

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

This is the code of the AutoIt script:

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

Func ReplaceMacro( $FileName, $ModuleName, $ModuleCode )
$oExcel.WorkBooks.Open($FileName)
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
For $oModule in $oModules
If $oModule.Type = 1 And $oModule.Name = $ModuleName Then
$oModules.Remove( $oModule )
EndIf
Next
$oModules.Import( $ModuleCode )
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
$ModuleCount = 0
For $oModule in $oModules
$ModuleCount += 1
If $ModuleCount = $oModules.Count Then
$oModule.Name = $ModuleName
EndIf
Next
$oExcel.ActiveWorkbook.Save
$oExcel.ActiveWorkbook.Close
$oExcel.Quit
EndFunc

--
Phil Hibbs.

Updating the VBA code in multiple spreadsheets

I'm now trying to port this to Excel, and I'm hitting a problem where
the Remove method fails with "Object doesn't support this method or
property":

Sub UpdateVBA()
Dim oExcel As Application
Dim oComponent As Object

Set oExcel = New Excel.Application
Set oBook = oExcel.Workbooks.Open("C:\Test.xls", 0, False, , , ,
True)
Set oComponent = oBook.VBProject.VBComponents("TestModule")
oBook.VBProject.VBComponents.Remove (oComponent) ' <== FAIL
oBook.VBProject.VBComponents.Import ("C:\TestModule.txt")
oBook.VBProject.VBComponents
(oBook.VBProject.VBComponents.Count).Name = "TestModule"
oBook.Close
oExcel.Quit

End Sub

Any ideas?

Phil Hibbs.

Updating the VBA code in multiple spreadsheets

Fixed it - I can't use the module name as a collection index, I need
to loop through them checking the Name property of the VBComponent.

Phil Hibbs.

Full Script for Excel Spreadsheet Import Automation

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 need
to loop through them checking the Name property of the VBComponent.

Phil Hibbs.

Previous Posts In This Thread:

On Thursday, January 07, 2010 8:32 AM
Phil Hibbs wrote:

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

It can work in one of two ways:

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

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

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

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

This is the code of the AutoIt script:

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

Func ReplaceMacro( $FileName, $ModuleName, $ModuleCode )
$oExcel.WorkBooks.Open($FileName)
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
For $oModule in $oModules
If $oModule.Type = 1 And $oModule.Name = $ModuleName Then
$oModules.Remove( $oModule )
EndIf
Next
$oModules.Import( $ModuleCode )
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents
$ModuleCount = 0
For $oModule in $oModules
$ModuleCount += 1
If $ModuleCount = $oModules.Count Then
$oModule.Name = $ModuleName
EndIf
Next
$oExcel.ActiveWorkbook.Save
$oExcel.ActiveWorkbook.Close
$oExcel.Quit
EndFunc

--
Phil Hibbs.

On Thursday, January 07, 2010 12:24 PM
Phil Hibbs wrote:

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 where
the Remove method fails with "Object does not support this method or
property":

Sub UpdateVBA()
Dim oExcel As Application
Dim oComponent As Object

Set oExcel = New Excel.Application
Set oBook = oExcel.Workbooks.Open("C:\Test.xls", 0, False, , , ,
True)
Set oComponent = oBook.VBProject.VBComponents("TestModule")
oBook.VBProject.VBComponents.Remove (oComponent) ' <== FAIL
oBook.VBProject.VBComponents.Import ("C:\TestModule.txt")
oBook.VBProject.VBComponents
(oBook.VBProject.VBComponents.Count).Name = "TestModule"
oBook.Close
oExcel.Quit

End Sub

Any ideas?

Phil Hibbs.

On Thursday, January 07, 2010 12:24 PM
Phil Hibbs wrote:

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 need
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/tutorials...sers-redu.aspx

Full Script for Excel Spreadsheet Import Automation

On Feb 24, 5:48*am, Ian G wrote:
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

Wrote my Own - Mass Import Modules & UserForms to Multiple ExcelSpreadsheets

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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p; </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #000000;"numImported = 0</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;            </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </code<span style="margin-left: 80px ! important;"<code style="color: #000000;"Unload cWorkbook</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;                </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    &nbs p;            </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    &nbs p;                </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    &nbs p;            </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #008200;"'Display Errors</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </code<span style="margin-left: 48px ! important;"<code style="color: #000000;".Import codePath</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;       </code<span style="margin-left: 48px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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/tutorials...il-from-w.aspx

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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p; </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #000000;"numImported = 0</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;            </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </code<span style="margin-left: 80px ! important;"<code style="color: #000000;"Unload cWorkbook</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;                </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    &nbs p;            </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    &nbs p;                </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    &nbs p;            </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #008200;"'Display Errors</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </code<span style="margin-left: 48px ! important;"<code style="color: #000000;".Import codePath</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;       </code<span style="margin-left: 48px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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/tutorials...uent-nhib.aspx

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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;     </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p; </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #000000;"numImported = 0</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;            </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                 &n bsp;  </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </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    &nbs p;                </code<span style="margin-left: 80px ! important;"<code style="color: #000000;"Unload cWorkbook</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;                </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    &nbs p;            </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    &nbs p;                </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    &nbs p;            </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </code<span style="margin-left: 32px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;   </code<span style="margin-left: 32px ! important;"<code style="color: #008200;"'Display Errors</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;   </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    &nbs p;       </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;   </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    &nbs p;       </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    &nbs p;       </code<span style="margin-left: 48px ! important;"<code style="color: #000000;".Import codePath</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code    &nbs p;       </code<span style="margin-left: 48px ! important;" </span</span</div
<div style="background-color: #ffffff;"<span<code    &nbs p;       </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    &nbs p;       </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    &nbs p;            </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    &nbs p;       </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    &nbs p;   </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/tutorials...ustom-con.aspx