Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code for MS Query
Hi Everyone,
I have a piece of code in Excel 2000 to retrieve some data from Access 2000 using MS Query and it works fine. The problem is that I need to copy down the formulas to the right of the data and it does not do it even tough I have the line: FillAdjacentFormulas=True on it. The funny thing is that if I do a straight "refresh" in the worksheet it copies all the formulas perfectly. The reason why I need to do it using a macro is because I need the user to select some criteria from another worksheet. (I tested this and it works too) Here's the code: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\FF Files\Profit Model.mdb;DefaultDir=S:\FF Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _ ), Array("2048;PageTimeout=5;")), Destination:=Range("A10")) .CommandText = Array( _ ----SQL Statement Here---- .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Is there anything that I am missing or doing wrong? I thank you all in advance Gustavo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code for MS Query
Gustavo
It will not fill the adjacent formulas when you first create the querytable. I'm not sure why. If you add another Refresh line, it should work. .Refresh BackgroundQuery:=False .Refresh BackgroundQuery:=False End With I there a particular reason you're creating a new querytable every time? If you can use the same querytable but just need different criteria, you might consider using parameters. http://www.dicks-clicks.com/excel/Ex...htm#Parameters -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Gustavo" wrote in message ... Hi Everyone, I have a piece of code in Excel 2000 to retrieve some data from Access 2000 using MS Query and it works fine. The problem is that I need to copy down the formulas to the right of the data and it does not do it even tough I have the line: FillAdjacentFormulas=True on it. The funny thing is that if I do a straight "refresh" in the worksheet it copies all the formulas perfectly. The reason why I need to do it using a macro is because I need the user to select some criteria from another worksheet. (I tested this and it works too) Here's the code: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=S:\FF Files\Profit Model.mdb;DefaultDir=S:\FF Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _ ), Array("2048;PageTimeout=5;")), Destination:=Range("A10")) .CommandText = Array( _ ----SQL Statement Here---- .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Is there anything that I am missing or doing wrong? I thank you all in advance Gustavo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code for MS Query
Thanks a lot Dick!
I noticed that the extra refresh line works.It just takes twice as long to complete the task. Strange thing this one. I can't have the user entering a customer number everytime because we have over 25,000 and they just won't remember. I had a filtered list of them in another worksheet that they use to select and from there I am taking the criteria. It works just fine. I looked at the code in the website you recommended and I might use that method now. Thanks a lot for your help and response Gustavo -----Original Message----- Gustavo It will not fill the adjacent formulas when you first create the querytable. I'm not sure why. If you add another Refresh line, it should work. .Refresh BackgroundQuery:=False .Refresh BackgroundQuery:=False End With I there a particular reason you're creating a new querytable every time? If you can use the same querytable but just need different criteria, you might consider using parameters. http://www.dicks- clicks.com/excel/ExternalData6.htm#Parameters -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Gustavo" wrote in message ... Hi Everyone, I have a piece of code in Excel 2000 to retrieve some data from Access 2000 using MS Query and it works fine. The problem is that I need to copy down the formulas to the right of the data and it does not do it even tough I have the line: FillAdjacentFormulas=True on it. The funny thing is that if I do a straight "refresh" in the worksheet it copies all the formulas perfectly. The reason why I need to do it using a macro is because I need the user to select some criteria from another worksheet. (I tested this and it works too) Here's the code: With ActiveSheet.QueryTables.Add(Connection:=Array (Array( _ "ODBC;DSN=MS Access Database;DBQ=S:\FF Files\Profit Model.mdb;DefaultDir=S:\FF Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _ ), Array("2048;PageTimeout=5;")), Destination:=Range("A10")) .CommandText = Array( _ ----SQL Statement Here---- .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Is there anything that I am missing or doing wrong? I thank you all in advance Gustavo . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code for MS Query
Gustavo
If you are building the SQL statement in your code, then I still think you shouldn't need to create the querytable each time, even without parameters. You can use code like this Dim sSql As String sSql = "build your string here" Sheet1.QueryTables(1).CommandText = sSql Sheet1.QueryTables(1).Refresh I'll bet that would take care of the adjacent formula problem too. Good luck. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Gustavo" wrote in message ... Thanks a lot Dick! I noticed that the extra refresh line works.It just takes twice as long to complete the task. Strange thing this one. I can't have the user entering a customer number everytime because we have over 25,000 and they just won't remember. I had a filtered list of them in another worksheet that they use to select and from there I am taking the criteria. It works just fine. I looked at the code in the website you recommended and I might use that method now. Thanks a lot for your help and response Gustavo -----Original Message----- Gustavo It will not fill the adjacent formulas when you first create the querytable. I'm not sure why. If you add another Refresh line, it should work. .Refresh BackgroundQuery:=False .Refresh BackgroundQuery:=False End With I there a particular reason you're creating a new querytable every time? If you can use the same querytable but just need different criteria, you might consider using parameters. http://www.dicks- clicks.com/excel/ExternalData6.htm#Parameters -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Gustavo" wrote in message ... Hi Everyone, I have a piece of code in Excel 2000 to retrieve some data from Access 2000 using MS Query and it works fine. The problem is that I need to copy down the formulas to the right of the data and it does not do it even tough I have the line: FillAdjacentFormulas=True on it. The funny thing is that if I do a straight "refresh" in the worksheet it copies all the formulas perfectly. The reason why I need to do it using a macro is because I need the user to select some criteria from another worksheet. (I tested this and it works too) Here's the code: With ActiveSheet.QueryTables.Add(Connection:=Array (Array( _ "ODBC;DSN=MS Access Database;DBQ=S:\FF Files\Profit Model.mdb;DefaultDir=S:\FF Files;DriverId=25;FIL=MS Access;MaxBufferSize=" _ ), Array("2048;PageTimeout=5;")), Destination:=Range("A10")) .CommandText = Array( _ ----SQL Statement Here---- .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Is there anything that I am missing or doing wrong? I thank you all in advance Gustavo . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUERY & HELP: so slow executing VBA code... :S | Excel Worksheet Functions | |||
how can i query an sheet with a where clause....from c# code using | Charts and Charting in Excel | |||
Code Query | Excel Programming | |||
Query on small piece of code | Excel Programming | |||
query source code | Excel Programming |