How do I insert a variable for the file/path in aQuery Table?

[JoeB]

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:\CMA\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

[Martin Fishlock]

Try:

Sub ImportCMAData()
dim szFileName as string
szFileName="C:\CMA\CMAData.csv"
szFileName = InputBox("File name and path?", _
"Enter file name and path...", szFileName)
Sheets("Data").Select

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & szFileName,
Destination:=Range("A1"))
.Name = "CMAData_1"

........

You may need to adjust the other specifications a little.

HTH


>

"JoeB" wrote:

> 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:\CMA\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
>

[JoeB]

It works! Mine was close, but "no cigar."

Happy Holidays Martin, and thank you for the solution!

Joe

"Martin Fishlock" wrote:

> Try:
>
> Sub ImportCMAData()
> dim szFileName as string
> szFileName="C:\CMA\CMAData.csv"
> szFileName = InputBox("File name and path?", _
> "Enter file name and path...", szFileName)
> Sheets("Data").Select
>
> With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & szFileName,
> Destination:=Range("A1"))
> .Name = "CMAData_1"
>
> .......
>
> You may need to adjust the other specifications a little.
>
> HTH
>
>
> >
>
> "JoeB" wrote:
>
> > 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:\CMA\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
> >