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;"&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"&nbsp;</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 40px ! important;"&nbsp;</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"<code style="color: #000000;"codefileNames = codefileNamesH</code</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #000000;"codefileNames = codefileNames &amp; vbLf &amp; fileName</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"<code style="color: #000000;"codefileNames = codefileNames &amp; </code<code style="color: blue;"" (File Not Found - No Update)"</code</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 40px ! important;"&nbsp;</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"<code style="color: #000000;"Msg = MsgH</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #000000;"Msg = Msg &amp; vbLf &amp; fileName</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"<code style="color: #000000;"Msg = Msg &amp; </code<code style="color: blue;"" (File Not Found - No Update)"</code</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;</code<span style="margin-left: 24px ! important;"&nbsp;</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"<code style="color: #006699; font-weight: bold;"If</code <code style="color: #000000;"(MsgBox(Msg &amp; vbLf &amp; 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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #000000;"numImported = 0</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"<code style="color: #006699; font-weight: bold;"If</code <code style="color: #000000;"(fileName &lt;&gt; vbNullString) </code<code style="color: #006699; font-weight: bold;"Then</code</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;</code<span style="margin-left: 96px ! important;"<code style="color: #000000;"Msg = Msg &amp; vbLf &amp; fileName &amp; </code<code style="color: blue;"" (Importing)"</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 80px ! important;"<code style="color: #000000;"Unload cWorkbook</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 80px ! important;"<code style="color: #000000;"Msg = Msg &amp; vbLf &amp; fileName &amp; </code<code style="color: blue;"" (Not valid file type)"</code</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</code<span style="margin-left: 64px ! important;"<code style="color: #000000;"Msg = Msg &amp; vbLf &amp; filePath &amp; </code<code style="color: blue;"" (Does Not Exist)"</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"&nbsp;</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #008200;"'Display Errors</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #006699; font-weight: bold;"If</code <code style="color: #000000;"(Msg &lt;&gt; </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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"<code style="color: #000000;"Msg = vbLf &amp; </code<code style="color: blue;""Errors for the following Excel Spreadsheets:"</code <code style="color: #000000;"&amp; Msg</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 32px ! important;"<code style="color: #000000;"Msg = numImported &amp; </code<code style="color: blue;"" Workbooks imported successfully!"</code <code style="color: #000000;"&amp; Msg</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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;"&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 16px ! important;"&nbsp;</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"<code style="color: #000000;".Import codePath</code</span</span</div
<div style="background-color: #f8f8f8;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code<span style="margin-left: 48px ! important;"&nbsp;</span</span</div
<div style="background-color: #ffffff;"<span<code&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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&nbsp;&nbsp;&nbsp;&nbsp;</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