Jump to content
Becca

Passing values from autoit to vba script

Recommended Posts

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.

#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!

Share this post


Link to post
Share on other sites
Subz

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

#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)

 

  • Thanks 1

Share this post


Link to post
Share on other sites
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!!

  • Like 1

Share this post


Link to post
Share on other sites
Earthshine

Subz is VERY generous. Yes


My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.