Tag Archives: list

VBA To publish a Batch File from MS Excel

Posted on by
1

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

ExcelBatch3

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

ExcelBatch4

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

ExcelBatch5

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

ExcelBatch6

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

ExcelBatch7

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

ExcelBatch8

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

Batch for Obtaining MS Excel File List

Posted on by
2

Ever open up a folder in Windows and wonder how can I get a copy of all the file names in the folder?
Often you need a list, sometimes, you need the list to automate a process. The post will cover using Batch files for obtaining a list of Excel files.

Tools:  Windows operating system and Notepad
Application:  Windows directories and network drives*

Fast answer:

In notepad save this code as a .bat file:

pushd C:YourFilePath\Batch Example
filetorun -options
dir /b *.xls? > fileslist.txt

How To:

First we will create a folder for testing. I named my folder Batch Example. I placed the batch file in my directory as follows:

Batch1

Open the folder and leave the Windows folder explorer open while you perform these next steps.

Batch2

Open Notepad:

We will use notepad to author your batch file—also referred to as a .bat file. So open your Notepad and place the following code in. (Be sure to rewrite your file path)

Code:

pushd C:<<YourFilePath>>\Batch Example
filetorun -options
dir /b *.xls? > fileslist.txt

Your code should look like the picture below except for the file path:

Batch3

Save your file as filelist.bat in the Batch Example folder:

Save As

Batch4

File name: filelist.bat and click save.

Batch5

Note: it is ok that “Save as type” still says Text Documents (*.txt)

Switch to the file explorer and you will see the batch file:

Batch6

To use the batch function you must now double click it to run it or use the command line, etc.

Go ahead and double click it:

Fileslist.txt is created

Batch7

The file will contain all of the .xls type files in the folder.

To verify open it up. You will see it is empty:

Batch8

Now add a couple test MS Excel files to the folder (must contain the file extension “.xls” or “.xls” variants: “.xlsm’, “.xlsx”, “xlsb”)

Note—you may need to turn on file extension setting to see them in the file explorer—they are there even if the extension visibility is turned off)

Here I have created several test books in different MS Excel formats:

Batch9

Double click the .bat file to run it and to make a new fileslist.txt file. After the batch file runs, open the fileslist.txt file and you will have a list of all the MS Excel ( .xls type) files:

Batch10

If you need to edit the batch file, you will have to open notepad first, then open the batch file from notepad.

Open Notepad
Select the correct path
Switch the Text Documents (*.txt) to All Files(*.*)

Batch11

What the Batch file is doing:

A Batch file is a list of command line commands, each line represents what you would be doing in the command prompt.

Line 1: “pushd” command

pushd C:\Users\Alexander zbiciak\Batch Example
filetorun -options
dir /b *.xls? > fileslist.txt

The pushd changes the current directory to the specified directory and stores the previous directory to popd.

In the command line the first line I typed the same string:

pushd C:\Users\Alexander zbiciak\Batch Example

The Command line shows the directory to be changed to:

C:\Users\Alexander zbiciak\Batch Example

Line 2: filetorun – options

pushd C:\Users\Alexander zbiciak\Batch Example
filetorun -options
dir /b *.xls? > fileslist.txt

filetorun –options is optional, the .bat will run without it.

Line 3: dir /b *.xls? >

pushd C:\Users\Alexander zbiciak\Batch Example
filetorun -options
dir /b *.xls? > fileslist.txt

dir /b *xls? > will search the directory for files containing the string .xls and write them to the fileslist.txt file.

The * will allow anything to be in front of the .xls
The ? will only allow up to one character after the .xls
The control helps avoid pulling in non-Excel files. In the picture you can see some unfortunately named text files, but they are not brought into the fileslist.txt file:

Batch13

The batch file ignored “xls.txt and xlsb.xls.txt” You can see the file output in the preview window to the right.

See it in action at the command prompt:

Start over with no batch file or fileslist.txt file in the directory—if we were to run the commands we would see the following:

pushd C:\Users\Alexander zbiciak\Batch Example

Batch14

dir /b

Batch15

dir /b *.xls?

Batch16

dir /b *.xls? Limited the output to only .xls type files

dir /b *.xls? > fileslist.txt
dir /b

Batch17

Adding in the >fileslist.txt will assign the list to a textfile names fielslist.txt

For more information see also:

https://technet.microsoft.com/en-us/library/bb490890.aspx

Vlookup: Learn it, Love it and Leave it… Part 2 Enhancements

Posted on by
1

In Part 2, I showed you how to add the dynamic aspects to a Vlookup formula, but we left a lot of room for error. In particular the user may enter data that cannot be found in the data. I showed how entering an “a” for item # failed, likewise the user may wish to enter January, not knowing or realizing Jan is the column header. Part 2 Enhancements shows you how to add validation rules to speed reporting by helping the user enter only what is needed.

Tools to be used: Data Validation.

First we want to be sure that the user only selects valid data.

To do this we will look at our two selections we made Item # and Month in cells C16 and D16. Select cell C16 and then go to:

Ribbon Data tab >> Data Validation >> Validation Criteria >> select List

Click on Data Validation to open the contextual/dialogue box for your inputs:

DataValidation

 In the dialogue box navigate to the Allow: box.

Use selection box by activating the drop down and then select List.

DataValidationList

In Source:  enter the limited range (array) that has all of your items.

Data Validation List Source

For this example we will use the data table itself. In more advanced files we would use a control table to limit our possible items from a master list.

We will enter: $A$3:$A$12.

 

 

Once you hit enter, the cell C16 will now show a drop down arrow allowing you to select only what is in the list you referenced.

Data Validated Cell

 

You can do the same for the Months by selecting cell D16 and applying the list limitation with the source set to $C$2:$N$2. You can either manually type this or select the range with your mouse. Use the Practice tab of the linked file to build your own.

 

Congratulations, you have now limited the user entries and also helped ensure they are selecting the right items. You are on your way to building reports.