I'm at wits end. I've tried to be a little lazy and I've used the macro recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box. However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code it creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I don't get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split, and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns red on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there some boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

post the code and maybe someone can stitch it into one line (if that's what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

Here is the code below. I have also put everything on one line with the same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
..blahblah
..blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message ...
post the code and maybe someone can stitch it into one line (if that's what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

R. Choate,
This compiles. Whether correct or not, only you can tell.
You can combine more elements of the connection on one line. I have done
like for clarity of each part of the conn str.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;", _
"Provider=Microsoft.Jet.OLEDB.4.0;", _
"Password="""";", _
"User ID=Admin;", _
"Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls;", _
"Mode=Share Deny Write;", _
"Extended Properties=""HDR=YES;", _
""";", _
"Jet OLEDB:System database="""";", _
"Jet OLEDB:Registry Path="""";", _
"Jet OLEDB:Database Password="""";", _
"Jet OLEDB:Engine Type=35;", _
"Jet OLEDB:Database Locking Mode=0;", _
"Jet OLEDB:Global Partial Bulk Ops=2;", _
"Jet OLEDB:Global Bulk Transactions=1;", _
"Jet OLEDB:New Database Password="""";", _
"Jet OLEDB:Create System Database=False;", _
"Jet OLEDB:Encrypt Database=False;", _
"Jet OLEDB:Don't Copy Locale on Compact=False;", _
"Jet OLEDB:Compact Without Replica Repair=False;", _
"Jet OLEDB:SFP=False"), Destination:=Range("A1"))

End With

NickHK

"R. Choate" wrote in message
...
Here is the code below. I have also put everything on one line with the
same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any
obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at
it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.blahblah
.blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message
...
post the code and maybe someone can stitch it into one line (if that's
what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code
it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I
don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns
red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there
some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

i tried to break it for you, not sure if it will wrok or not. try nick's
option


With ActiveSheet.QueryTables.Add(Connection:=Array _
("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password= """";User ID=Admin;" & _
"Data Source=C:\Documents and Settings\Administrator\Desktop\QBAug.xls", _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" & _
"Database "",Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" & _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" &
_
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;"
& _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

--


Gary


"R. Choate" wrote in message
...
Here is the code below. I have also put everything on one line with the
same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any
obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at
it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.blahblah
.blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message
...
post the code and maybe someone can stitch it into one line (if that's
what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I
don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns
red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

Hi Gary,

Your version drew a type mismatch but Nick's worked. My question now is, why does the code run with a comma in the middle of the
file extension? I understand that this is an array and it requires commas between each element, but why a comma in the middle of a
word or in the middle of a file extension? That is ridiculous.

Nick, if you are reading this, thank you for at writing some code which makes more sense and is much cleaner than the "auto-code". I
have other questions about the code I would like to ask, but since the code works, I guess I shouldn't push my luck.

HOWEVER, does anybody know how I can import this without it telling me that I'm deleting a saved query when I delete the data later?
I don't want any saved queries and I don't understand it.

Thanks !

--
RMC,CPA


"Gary Keramidas" wrote in message ...
i tried to break it for you, not sure if it will wrok or not. try nick's
option


With ActiveSheet.QueryTables.Add(Connection:=Array _
("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password= """";User ID=Admin;" & _
"Data Source=C:\Documents and Settings\Administrator\Desktop\QBAug.xls", _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" & _
"Database "",Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" & _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" &
_
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;"
& _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

--


Gary


"R. Choate" wrote in message
...
Here is the code below. I have also put everything on one line with the
same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any
obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at
it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.blahblah
.blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message
...
post the code and maybe someone can stitch it into one line (if that's
what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I
don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns
red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

it's broken by outlook express, i am going to forget about it since you have
a solution

--


Gary


"R. Choate" wrote in message
...
Hi Gary,

Your version drew a type mismatch but Nick's worked. My question now is,
why does the code run with a comma in the middle of the
file extension? I understand that this is an array and it requires commas
between each element, but why a comma in the middle of a
word or in the middle of a file extension? That is ridiculous.

Nick, if you are reading this, thank you for at writing some code which
makes more sense and is much cleaner than the "auto-code". I
have other questions about the code I would like to ask, but since the
code works, I guess I shouldn't push my luck.

HOWEVER, does anybody know how I can import this without it telling me
that I'm deleting a saved query when I delete the data later?
I don't want any saved queries and I don't understand it.

Thanks !

--
RMC,CPA


"Gary Keramidas" wrote in message
...
i tried to break it for you, not sure if it will wrok or not. try nick's
option


With ActiveSheet.QueryTables.Add(Connection:=Array _
("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password= """";User ID=Admin;" & _
"Data Source=C:\Documents and Settings\Administrator\Desktop\QBAug.xls", _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" &
_
"Database "",Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" & _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" &
_
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;"
& _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

--


Gary


"R. Choate" wrote in message
...
Here is the code below. I have also put everything on one line with the
same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any
obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at
it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.blahblah
.blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message
...
post the code and maybe someone can stitch it into one line (if that's
what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters, too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code
it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I
don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns
red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there
some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA

R. Choate,
The macro output is just VBA's way of splitting the long connection string
into something that is acceptable as a parameter to the function.
In order to understand why VBA decides to split there (between the file
extension), you would have to understand the algorithm used. AFAIK you do
not have to use the "Array(.." syntax if the connection under a certain
number of characters, possibly 255.
In a way, you should count yourself lucky that can get any auto-generated
code and do not have to come up with that from the top of your head.

In terms of avoiding the warning on deletion, you could do it from a button
click, between .DisplayAlerts=False/True lines. otherwise Excel thinks that
is being helpful.

NickHK


"R. Choate" wrote in message
...
Hi Gary,

Your version drew a type mismatch but Nick's worked. My question now is,
why does the code run with a comma in the middle of the
file extension? I understand that this is an array and it requires commas
between each element, but why a comma in the middle of a
word or in the middle of a file extension? That is ridiculous.

Nick, if you are reading this, thank you for at writing some code which
makes more sense and is much cleaner than the "auto-code". I
have other questions about the code I would like to ask, but since the
code works, I guess I shouldn't push my luck.

HOWEVER, does anybody know how I can import this without it telling me
that I'm deleting a saved query when I delete the data later?
I don't want any saved queries and I don't understand it.

Thanks !

--
RMC,CPA


"Gary Keramidas" wrote in message
...
i tried to break it for you, not sure if it will wrok or not. try nick's
option


With ActiveSheet.QueryTables.Add(Connection:=Array _
("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password= """";User ID=Admin;" & _
"Data Source=C:\Documents and Settings\Administrator\Desktop\QBAug.xls", _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" &
_
"Database "",Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" & _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:"
&
_
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;"
& _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

--


Gary


"R. Choate" wrote in message
...
Here is the code below. I have also put everything on one line with the
same goal. I ended up getting one of those type mismatch
runtime errors even though the code looked right and didn't have any
obvious syntax errors. The editor did not turn my code red or
anything...it just wouldn't work. Anyway, feel free to try your hand at
it.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\QBAug.xls" _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet " _
, _
"OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.blahblah
.blahblah
End With
--
RMC,CPA


"Gary Keramidas" wrote in message
...
post the code and maybe someone can stitch it into one line (if that's
what
you really want) or at least break it where it can make more sense

the _ means the line is split and sometimes in may use " characters,
too.

--


Gary


"R. Choate" wrote in message
...
I'm at wits end. I've tried to be a little lazy and I've used the macro
recorder to write some code for me to import external data
from a tab-delimited text file. My first problem is that I just want to
import the data. I don't want to save the query and I go
into the properties when I'm doing the import and I uncheck that box.
However, after it imports the query, when I later delete the
data, it tells me there is a query saved with that data and I'll be
deleting it if I check "Yes". This is about to make me scream.
Please tell me how I can import the data without this problem.

Secondly, still on the issue of the macro recorder writing code. I am
importing data from 4 sources onto 4 worksheets independently
of each other. Two of the imports are from other Excel files. The code
it
creates for those imports is REALLY driving me up the
wall. Below is the 1st couple of lines of code:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and
Settings\Administrator\Desktop\KoreAug.x" _
, _
"ls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databa" _

Notice that it cuts off the file extension, xls, right at the "x"! I
don't
get it. Making matters worse, it then puts ", _" on the
next line, and begins the next line with "ls;".

If I try to fix this stupid code and eliminate the comma and the split,
and just put the whole name on the same line, I either get
errors when I run it the code (type mismatch?) or the whole code turns
red
on me for syntax problems. Does anybody have any idea
what in the wild world of sports is going on here? Finally, is there
some
boilerplate code I could use for just importing some basic
data from a table in an external xls file without all of this BS?

Thank you very much in advance !
--
RMC,CPA