| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Code
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Code
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating the code in multiple workbooks | Excel Programming | |||
| Updating linked spreadsheets | Excel Programming | |||
| Updating Excel spreadsheets | Excel Discussion (Misc queries) | |||
| Updating spreadsheets | Excel Discussion (Misc queries) | |||
| Change vba code in multiple spreadsheets | Excel Programming | |||
Hybrid Mode
