A batch file is, roughly, a list of commands for the command interpreter. The commands for SQLite are commands for SQLite. In other words, you'll need some way to send those commands to the SQLite shell. One possible answer is to create a little text file with the list of commands, and redirect it into the shell. You could either make that text file as a separate file, or have the batch file create it so the script is somewhat self contained:

@echo off

rem Change to the directory of this batch file, so "double clicking" on this file
rem will operate on files in the same folder as this script.
cd /d "%~dp0"

rem Create a temp file to execute commands
 >temp_sql_commands.txt echo .mode csv
>>temp_sql_commands.txt echo .output FinalReport.csv
>>temp_sql_commands.txt echo SELECT * FROM theTable

rem Call SQLite with the command script
sqlite3 "My Database.db" < temp_sql_commands.txt

rem Remove the helper text file
del temp_sql_commands.txt

echo All done, created FinalReport.csv

This works when I copy it and paste it into the command prompt.

When pasting into the Command Prompt,your first line starts & focuses on the SQLite CLI; the second and subsequent lines go to the CLI where they make sense.

When executing the same set of lines/statements from a batch file,all the lines are echoed to the Command Prompt NOT the SQLite CLI. That's why it does not work.

You are almost there!

First, your batch file (it should have extension BAT or CMD such that double click on it from within File Explorer will execute it).

"D:\SQLite32\sqlite3.exe" < "e:\temp\aa2.txt"

That is, specify the fully qualified name of SQLIte3.EXE, in quotes (permits the path to have embedded spaces) followed by the fully qualified name of another file, e:\temp\aa2.txt in my example.

The left chevron, <, redirects input from e:\temp\aa2.txt to SQLite3.EXE

Second your SQLite lines of code in e:\temp\aa2.txt

.open 'D:/SQLite32/DB/CHINOOK.DB'
.mode csv
.once  e:/temp/A_sqlite.csv
select * from employees;

The .once command redirects output to the file e:/temp/A_sqlite.csv: this file will be overwritten if it exists without warning.

Adapt the contents of the files (the two blocks of code) to your circumstances and it should work else raise your queries here.

Also, consider the option to send the output of your SQL statement directly to Excel (thereby giving your users even less to do!).

.once -x
select * from employees;

The above will start a new Excel instance and send the output directly to that instance (which should be installed on the computer). In Excel you will need to AutoFit Column Width.

but have to hit return to get it to finish

Usually, you would simply insert a blank line in your file to overcome this; in this case, you need to add semi-colon at the end of your SQL statement.

PS: You can (should) use / rather than \ for path separators since / is safe(r) for SQLite and fully accepted in Windows.

Thanks to both of you who responded to my question. It works! I was able to create a hyperlink from Excel that calls the batch file which either creates (Option 1) or calls an existing (Option 2) text file which creates and saves the output file. Can't make it much easier for the casual user.

Can't make it much easier for the casual user.

You can.

As things stand, the extract or result of your SQL statement is static. In practice that means the following:

1. the user has a historical snapshot of the underlying data (which has some merits and can be useful for forensic analysis).
2. The user has to run the script afresh to get the underlying data (which might have changed).

As you appear to be driving everything from Excel, you might consider refreshable workbooks.

For this you'll need:

a. To set up, pursue Data | Get Data | From Other Sources | From ODBC within Excel.

b. CARE! These links that follow will download the drivers For ODBC, you will need either the 32-bit driver or the 64-bit driver depending on whether you have the 32- or 64- bit version of Excel.

Then, in Excel, the user clicks Data | Refresh All to refresh the data by re-reading the underlying data from SQLite.

Advantages:

1. Your SQL is not exposed (in text files).
2. The user is empowered - they can choose when to refresh the data.
3. Subject to available memory, all the data you present to the user can be contained in one workbook and in different worksheets. This is easier to maintain than a series of individual text files.
4. You might also have charts based on the data drawn from Excel; these will refresh automatically as the data changes.
5. Any cosmetic rendering such as number formatting or font changes also tend to stick.
   

This works well for internal users.

Disadvantages:

The ODBC drivers mentioned above are statically linked to a historical version of SQLite and you have no control over the timing of their update. That means you will forgo the opportunity to use the latest enhancements in SQLite.

PS: If you download and install the ODBC drivers, you will NOT need to download any SQLite pre-compiled binaries.

I've considered outputting to a CSS-formatted HTML table.

Have a look at the options for .mode; it includes html.

like find all transactions between two dates or all individuals named Smith.

Easily user-accomplished in Excel either with data filter or with pivot tables.

Regarding: Disadvantages...The ODBC drivers...are statically linked to a historical version of SQLite. ... You will forgo the opportunity to use the latest enhancements in SQLite.

Do note that Mr. Werner released versions which use a DLL external to the ODBC driver.

Though marked "experimental" I recall using one without issue -- though perhaps your mileage will vary.

From page: http://www.ch-werner.de/sqliteodbc/

sqliteodbc_dl.exe (EXPERIMENTAL: variant which needs System.Data.SQLite.dll or sqlite3.dll in the Windows system folder)

sqliteodbc_w64_dl.exe (EXPERIMENTAL: variant which needs System.Data.SQLite.dll or sqlite3.dll in the Windows system folder)