ActiveSheet.QueryTables.Add

I am importing a CSV file and want to be selective in my data. I need to
bypass any record that does not have a char "V" in the specified column. The
above subject is also the standard QueryTables.Add statement. Any
suggestions?
--
Rick Rack

What is ADO, and where can I find it.
--
Rick Rack


"quartz" wrote:

> Hi,
>
> The QueryTable is a great tool, but it gives you all or nothing. My approach
> would be to import the entire file into a clean sheet, delete the info you
> don't need, then transfer the data to its final destination.
>
> Alternatively, you could switch to ADO and use a SQL string to define what
> you want to retrieve.
>
> HTH/
>
> "Rick" wrote:
>
> > I am importing a CSV file and want to be selective in my data. I need to
> > bypass any record that does not have a char "V" in the specified column. The
> > above subject is also the standard QueryTables.Add statement. Any
> > suggestions?
> > --
> > Rick Rack

Hello,

ADO stands for ActiveX Data Objects and is a technology currently supported
by Microsoft that enables you to deliver a SQL string to retrieve and/or
modify a wide variety of files including: MS-Access, MS-Excel, text files,
other types of databases, such as Oracle, and even MS-Word tables.

There is actually a lot of information out there if you google it, or seach
Microsoft's web site, but a good place to start is the link below:

http://www.erlandsendata.no/english/...php?t=envbadac

Copy the above into your browser and GO.

HERE are a few others to try:

http://msdn.microsoft.com/library/de...doprovinfo.asp

http://support.microsoft.com/default...en-us%3b257819

HTH/


"Rick" wrote:

> What is ADO, and where can I find it.
> --
> Rick Rack
>
>
> "quartz" wrote:
>
> > Hi,
> >
> > The QueryTable is a great tool, but it gives you all or nothing. My approach
> > would be to import the entire file into a clean sheet, delete the info you
> > don't need, then transfer the data to its final destination.
> >
> > Alternatively, you could switch to ADO and use a SQL string to define what
> > you want to retrieve.
> >
> > HTH/
> >
> > "Rick" wrote:
> >
> > > I am importing a CSV file and want to be selective in my data. I need to
> > > bypass any record that does not have a char "V" in the specified column. The
> > > above subject is also the standard QueryTables.Add statement. Any
> > > suggestions?
> > > --
> > > Rick Rack

Thank You for the reply, I'll give them a try. Wish me luck.
--
Rick Rack


"quartz" wrote:

> Hello,
>
> ADO stands for ActiveX Data Objects and is a technology currently supported
> by Microsoft that enables you to deliver a SQL string to retrieve and/or
> modify a wide variety of files including: MS-Access, MS-Excel, text files,
> other types of databases, such as Oracle, and even MS-Word tables.
>
> There is actually a lot of information out there if you google it, or seach
> Microsoft's web site, but a good place to start is the link below:
>
> http://www.erlandsendata.no/english/...php?t=envbadac
>
> Copy the above into your browser and GO.
>
> HERE are a few others to try:
>
> http://msdn.microsoft.com/library/de...doprovinfo.asp
>
> http://support.microsoft.com/default...en-us%3b257819
>
> HTH/
>
>
> "Rick" wrote:
>
> > What is ADO, and where can I find it.
> > --
> > Rick Rack
> >
> >
> > "quartz" wrote:
> >
> > > Hi,
> > >
> > > The QueryTable is a great tool, but it gives you all or nothing. My approach
> > > would be to import the entire file into a clean sheet, delete the info you
> > > don't need, then transfer the data to its final destination.
> > >
> > > Alternatively, you could switch to ADO and use a SQL string to define what
> > > you want to retrieve.
> > >
> > > HTH/
> > >
> > > "Rick" wrote:
> > >
> > > > I am importing a CSV file and want to be selective in my data. I need to
> > > > bypass any record that does not have a char "V" in the specified column. The
> > > > above subject is also the standard QueryTables.Add statement. Any
> > > > suggestions?
> > > > --
> > > > Rick Rack

Rick wrote:
> I am importing a CSV file and want to be selective in my data. I need to
> bypass any record that does not have a char "V" in the specified column.
> The above subject is also the standard QueryTables.Add statement. Any
> suggestions?

Rick: Unless I'm misunderstanding something, you can be selective with a
QueryTables.Add statement. You pass a SQL statement to QueryTables.Add and
use the SQL statement to limit the records it imports. Here's an example:

Sub AddQTFromCSV()

Dim sConn As String
Dim sSQL As String

sConn = "ODBC;DSN=Text Files;" & _
"DefaultDir=C:\Documents and Settings\****\My Documents;" & _
"DriverId=27;MaxBufferSize=2048;PageTimeout=5;"

sSQL = "SELECT Field1, Field2, Field3, Field4, Field5" & _
" FROM `C:\Documents and Settings\****\My Documents`\Book1.csv" & _
" WHERE (Field2='V')"

With Sheet3.QueryTables.Add(sConn, Sheet3.Range("A1"), sSQL)
.Refresh
End With

End Sub

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