Passing values from autoit to vba script

[Becca]

With help from Juvigy and Subz I have an autoit script calling a vba script to merge the files in a specified folder into an Excel workbook. Now I need to add a layer.... I need the autoit script to loop through a series of folders and call the vba script to merge the files in each folder into an Excel workbook and name the workbook using the folder name. So I need to pass the folder location into the vba script. The code below creates a workbook with the first folder name but does not merge the files inside the folder and stops with an error on the vba script call.

expandcollapse popup

#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>


Local $iLoopIndex = 1
Local $DirPath = @DesktopDir & "/MergingExcelTest2/"
Local $FolderName = ""
Local $FilesLocation=""


NavFiles()

Func NavFiles()
    ; Nav through folders in the designated directory.
    Local $aDirList = _FileListToArray($DirPath, "*")
    If @error = 1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.")
        Exit
    EndIf
    If @error = 4 Then
        MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.")
        Exit
    EndIf
   ; Loop through folders and merge workbooks in each folder
 While $iLoopIndex<=$aDirList[0]
    ; Get name of folder to use as the merged filename
    $FolderName = $aDirList[$iLoopIndex]
    ; Put path to folder into a variable
    $FilesLocation = $DirPath&$aDirList[$iLoopIndex]&"/"
     MsgBox($MB_SYSTEMMODAL, "", "Path to files is: " & $FilesLocation & @CRLF)
  MsgBox($MB_SYSTEMMODAL, "", "Folder name is: " & $FolderName & @CRLF)
    CombineWkbks ($FilesLocation,$FolderName)
    $iLoopIndex = $iLoopIndex+1
    WEnd
EndFunc   ;==>NavFiles




Func CombineWkbks ($FilesLocation,$FolderName)

   Local $sModule = @DesktopDir & "/Module2.bas"
   Local $sMacro

    $sMacro &= 'Attribute VB_Name = "Module2"' & @CRLF
    $sMacro &= 'Sub MergeWkbks()' & @CRLF
    $sMacro &= 'Path = WScript.Arguments(0)' & @CRLF
    $sMacro &= 'Filename = Dir(Path &"*.xls*")' & @CRLF
    $sMacro &= '    Do While Filename <> ""' & @CRLF
 $sMacro &= '    Debug.Print.Filename' & @CRLF
    $sMacro &= '    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True' & @CRLF
    $sMacro &= '            For Each Sheet In ActiveWorkbook.Sheets' & @CRLF
    $sMacro &= '            Sheet.Copy After:=ThisWorkbook.Sheets(1)' & @CRLF
    $sMacro &= '    Next Sheet' & @CRLF
    $sMacro &= 'Workbooks(Filename).Close' & @CRLF
    $sMacro &= 'Filename = Dir()' & @CRLF
    $sMacro &= 'Loop' & @CRLF
    $sMacro &= 'End Sub' & @CRLF
    Local $hFileOpen = FileOpen($sModule, 2)
    FileWrite($hFileOpen, $sMacro)
    FileClose($hFileOpen)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
Local $sWorkbook = @DesktopDir & "/" & $FolderName & ".xlsx"
$oExcel.VBE.ActiveVBProject.VBComponents.Import($sModule)
_Excel_BookSaveAs($oWorkbook, $sWorkbook)
$oExcel.Run("MergeWkbks", $FilesLocation)
_Excel_Close($oExcel)
EndFunc ;==>Func CombineWkbks

And then here is the vba script

Attribute VB_Name = "Module2"
Sub MergeWkbks()
Path = WScript.Arguments(0)
Filename = Dir(Path &"*.xls*")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
            For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

Any help would be greatly appreciated. This is my first project using autoit and vba scripting. Thank you!

[Subz]

You know you could do this all in Autoit without the macro, example below:

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Local $sDirPath = @DesktopDir & "\MergingExcelTest2"
;~ Nav through folders in the designated directory.
Local $aDirList = _FileListToArray($sDirPath, "*", 2)
    If @error = 1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.")
        Exit
    EndIf
    If @error = 4 Then
        MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.")
        Exit
    EndIf

Local $aXlsList, $oWorkBook, $oReadBook, $iWorkSheets = 1
Local $oExcel = _Excel_Open(True, False, True, True, True)
For $i = 1 To $aDirList[0]
    $aXlsList = _FileListToArray($sDirPath & "\" & $aDirList[$i], "*.xls", 1, True)
    If @error = 1 Then
        MsgBox($MB_SYSTEMMODAL, "", "Path:" & $sDirPath & "\" & $aDirList[$i] & " is invalid.")
        ContinueLoop
    EndIf
    If @error = 4 Then
        MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.")
        ContinueLoop
    EndIf
    $oWorkBook = _Excel_BookNew($oExcel)
    For $j = 1 To $aXlsList[0]
        $oReadBook = _Excel_BookOpen($oExcel, $aXlsList[$j], True)
        For $k = 1 To $oReadBook.Sheets.Count
            _Excel_SheetCopyMove($oReadBook, $k, $oWorkBook, $iWorkSheets, False, True)
            $iWorkSheets += 1
        Next
        _Excel_BookClose($oReadBook)
    Next
    $iWorkSheets = 1
    _Excel_BookSaveAs($oWorkBook, $sDirPath & "\" & $aDirList[$i] & ".xlsx")
    _Excel_BookClose($oWorkBook)
Next
_Excel_Close($oExcel)

 

[Becca]

OMG! Thank you SO much. I just ran it and it worked like a charm. I really appreciate the generosity of this forum. Is there any way to vote for solutions besides clicking the heart/trophy icon?

Thank you again Subz. I really appreciate it!!

[Earthshine]

Subz is VERY generous. Yes