Importing external data

[R. Choate]

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
OLEDBataba" _

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 !

 

[Gary Keramidas]

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.

 

[R. Choate]

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
OLEDBatabase" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase 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 " _
, _
"OLEDBon'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


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 Keramidas]

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 OLEDBon't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

 

[NickHK]

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 OLEDBatabase Password="""";", _
"Jet OLEDB:Engine Type=35;", _
"Jet OLEDBatabase 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 OLEDBon't Copy Locale on Compact=False;", _
"Jet OLEDB:Compact Without Replica Repair=False;", _
"Jet OLEDB:SFP=False"), Destination:=Range("A1"))

End With

NickHK

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

OLEDBatabase" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase

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 " _
, _
"OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact

Without Replica Repair=False;Jet OLEDB:SFP=False" _

 

[R. Choate]

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


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 OLEDBon't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("A1"))

 

[Gary Keramidas]

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

 

[R. Choate]

I adjusted your code for the text wrapping in the newsreader. That was not the problem. Your code had some slight difference from
Nick's in the handling of the array, causing the annoying "Type Mismatch". I don't blame you for giving up on it since there is some
working code, but I would still like to figure out why the code is saving the query and giving me problems when I delete data. That
macro recorder sure can write some stupid code.

Thanks for your help.

Richard

--
RMC,CPA


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

 

[NickHK]

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

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?

 

[R. Choate]

Thanks for your thoughts. I'll look into this further. I usually do my import/export via ADO instead of this garbage we've been
discussing. Turning off the warning is a bypass of the real issue of why the query is saved on the worksheet when I told it not to
save the query in the options when I set it up. It seems like my effort to not save the query at all was a waste of time, and Excel
saved it anyway. That is the part I would like to get to the bottom of. I've gotten some occasional decent auto-generated code, but
quite a lot of the time, it is flawed and/or extremely over-coded. You should see the SQL statements that Access generates
automatically. It is so overdone and has so many unneeded parenthesis that even the simplest of queries reads like 50 chapters from
Tolstoy.
--
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

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?

 

[NickHK]

R. Choate,
For what it's worth, if I uncheck the "Save Query Definition" box when
manually making the query, the effect is as you desire, although the macro
code is the same whether this option is checked or not.
I suspect you would need to go through the .QueryTables collection deleting
as you go to achieve through code.
ThisWorkbook.ActiveSheet.QueryTables(1).Delete

Excel 2K

NickHK

Thanks for your thoughts. I'll look into this further. I usually do my

import/export via ADO instead of this garbage we've been

discussing. Turning off the warning is a bypass of the real issue of why

the query is saved on the worksheet when I told it not to

save the query in the options when I set it up. It seems like my effort to

not save the query at all was a waste of time, and Excel

saved it anyway. That is the part I would like to get to the bottom of.

I've gotten some occasional decent auto-generated code, but

quite a lot of the time, it is flawed and/or extremely over-coded. You

should see the SQL statements that Access generates

automatically. It is so overdone and has so many unneeded parenthesis that

even the simplest of queries reads like 50 chapters from

 

[Guest]

With ActiveSheet.QueryTables.Add

No matter how you dice it that line adds the query to the QueryTables
collection, which is the same thing as saving it. I work with Access so I
don't know if Excel has a way of doing a temporary query or table, I know
Access does not. You will have to put a delete statement in a clean up
function that's activated when the worksheet closes (I'm assuming there is
some sort of worksheet unload event) or at some point you determine is
logical to eliminate the query.

And thanks to all I was just discussing with a colleague about how to get a
query into Excel via code. Thanks for detailed answer!

~Mark

 

[R. Choate]

Hi Mark,

I was a little confused by your final paragraph, but as to your main response to my post, I appreciate your information. I would
also add that I have used ADO extensively for queries and import/export both from and to Excel and Access in the past with great
success and without these "saved-query" problems. I would have used it this time except the import data that is my query source does
not have appropriate column headers and defined data ranges. I would have to write more code to open and format the source to make
it work properly in my SQL stmt. Anyway, I highly recommend ADO to you for getting a query into Excel, especially if it is from
Access. You can run the code from either app. I have examples of both if you would like them. The result is clean and problem free.
No saved query garbage.

Thank you again for your response to my post.

--
RMC,CPA


With ActiveSheet.QueryTables.Add

No matter how you dice it that line adds the query to the QueryTables
collection, which is the same thing as saving it. I work with Access so I
don't know if Excel has a way of doing a temporary query or table, I know
Access does not. You will have to put a delete statement in a clean up
function that's activated when the worksheet closes (I'm assuming there is
some sort of worksheet unload event) or at some point you determine is
logical to eliminate the query.

And thanks to all I was just discussing with a colleague about how to get a
query into Excel via code. Thanks for detailed answer!

~Mark