Drabbacs

1) How should the .CommandText = Array() phrase look when
using a dynamic filename?

I rarely use Array(). You need it if your SQL string is more than 255
characters, but only in certain circumstances. In your case, just use

..CommandText = Filename1 & "$"

It would probably work with

..CommandText = Array(Filename1 & "$")

but you really don't need it.


2) In general when dealing with multiple files in a macro,
how do you keep track and switch focus between them?


Switch focus - you don't. There should be no need to switch the focus of
the workbooks except at the very end of your macro (to make sure the one you
want the user to see is active). Otherwise, I use object variables

Dim wbStart as Workbook
Dim wbText as Workbook

Set wbStart = ActiveWorkbook

Set wbText = Workbooks.OpenText (...)

Then you can use wbStart and wbText to refer to whichever one you need.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com