Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Get External Data Queries
Application:Excel 2002
Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Get External Data Queries
MSQuery is only good for 'set it and forget it' solutions.
It's very good at doing the same thing every time. It's lousy if you ever have to change anything. Instead you might look into ADO or DAO for connecting to the datasource and retrieving data. I use ADO and only have to change my code if anything changes. Here's a sample: 'Set a reference to Microsoft ActiveX Data Objects 2.x Dim cn as New ADODB.Connection Dim rs as New ADODB.Recordset 'This is the connection string to your Access database cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\DatabasePath\DatabaseName.mdb;" 'Selects all from my Access query called ReportQuery. 'You can also put the entire sql string here. sql = "SELECT * FROM ReportQuery" 'Creates a Recordset using the connection and the query rs.Open sql, cn 'Pastes the results to A2 of the ActiveSheet ActiveSheet.Range("A2").CopyFromRecordset rs This is just an example. You can read more about using ADO by going to this link: http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnmdac/html/datechartoverview.asp tod -----Original Message----- Application:Excel 2002 Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Get External Data Queries
Neil
You also need to edit the Connection string. Look here http://www.dicks-clicks.com/excel/Ex....htm#ChangeSQL -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Neil" wrote in message ... Application:Excel 2002 Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. Help would be much appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Get External Data Queries
Hi Neil,
Query: I am currently importing data in an excel spreadsheet from Access using MS query via the "Get External Data" option. My data source keeps on changing so I am updating the query using the following route: Edit Query View the query using MS Query, look at the SQL statement and amend the file source, save query, go back to excel. The problem I have is when I open the spreadsheet it still thinks it is linked to previous Access databases which I have used in previous version of the query. How do I prevent this. Also is there a way of editing these queries through VBA. It is baffling me where the code for the import is bing held. You could download and test Dick Kusleika and my Query manager: www.jkp-ads.com/QueryManager.zip Regards, Jan Karel Pieterse Excel MVP JKP Application Development Services www.jkp-ads.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Inport External Data - new queries | Excel Worksheet Functions | |||
Excel 2002 External Data Queries and UNC !HELP! | Excel Discussion (Misc queries) | |||
External Data Queries - Data Range Properties v Spreadsheet Format | Excel Discussion (Misc queries) | |||
External Data Queries and Cookies | Excel Programming | |||
External data queries within applcation collection | Excel Programming |