|
Importing external data
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 |
Importing external data
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 |
Importing external data
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 |
Importing external data
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 |
Importing external data
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 |
Importing external data
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 |
Importing external data
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 |
Importing external data
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 "Gary Keramidas" wrote in message ... 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 |
Importing external data
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 |
Importing external data
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 "NickHK" wrote in message ... 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 |
Importing external data
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 "R. Choate" wrote in message ... 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 "NickHK" wrote in message ... 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 |
Importing external data
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" wrote: 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 "NickHK" wrote in message ... 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 |
Importing external data
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 "MarkInSalemOR" wrote in message ... 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" wrote: 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 "NickHK" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com