In a previous post I described how to set up a batch file to create a list of files in a directory, specifically MS Excel files.

Sample Excel file with VBA in it.

Question:

Have you ever wanted to bring the data from a set of standardized files into one file using automation?

If so, this blog represents step 2—generating the list remotely. Refer to my previous post to learn how the batch file works.

Tools: MS Excel, MS Windows

Fast Answer–MS Excel VBA Code:

Sub Build_Bat()
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Build_Bat generates a batch file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FileNum As Integer
Dim Filepath As String
Dim Folder As String

Filepath = <<Enter your file path here with a closing \>> & "filelist.bat"

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Filepath = "C:\Users\Alexander zbiciak\Batch Example\" & "filelist.bat"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

'create spot for batch file

FileNum = FreeFile()

'open file

Open Filepath For Output As #FileNum

'write batch file

Print #FileNum, "pushd " & Folder
Print #FileNum, "filetorun -options"
Print #FileNum, "dir /b *.xls? > fileslist.txt"

Close #FileNum

End Sub

Before Macro is run:

After Macro is run:

How To:

First open up Excel and be sure you have the developer tab enabled in your ribbon:

File >> Options >> Customize Ribbon >> Developer

Select the Developer Tab and then the Visual Basic Icon to open your editor:

Inside the editor you will need to add a module if there are none or open one you want to add this code to:

With your module open copy the code below and paste it in (be sure to change your file path):

Sub Build_Bat()
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Build_Bat generates a batch file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FileNum As Integer
Dim Filepath As String
Dim Folder As String

Filepath = <<Enter your file path here with a closing \>> & "filelist.bat"

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Filepath = "C:\Users\Alexander zbiciak\Batch Example\" & "filelist.bat"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’

'create spot for batch file

FileNum = FreeFile()

'open file

Open Filepath For Output As #FileNum

'write batch file

Print #FileNum, "pushd " & Folder
Print #FileNum, "filetorun -options"
Print #FileNum, "dir /b *.xls? > fileslist.txt"

Close #FileNum

End Sub

Test your macro by opening the file path in the Windows file explorer. Leave this open so you can watch the VBA output:

With the file explorer window to the side, place your cursor inside the VBA code and then click the play button:

Congratulations—You have now published a batch file from MS Excel.