I'm automating a workbook for some clients, and the process begins with a
data download from their local Multiple Listing Service (MLS). The code that
works is below. What I've been trying to do is find a means for replacing the
hard coded file/path -- C:\CMA\CMAData.csv -- with a variable passed from an
input box. Building the string is easy, but Excel doesn't accept the string
on execution of the code. The error message is : Run Time Error: 1004
Application-Defined or object- defined error. My suspicion is that this can't
be done by passing avariable into "QueryTables.Add."

My goal is to allow users to specify the path and file name on the fly
rather than forcing them into a fixed path and file.

Any ideas would be appreciated, and I thank you in advance for reading this.

Sub ImportCMAData()

Sheets("Data").Select

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\C MA\CMAData.csv", _
Destination:=Range("A1"))
.Name = "CMAData_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
'.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub