Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.datamap,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
I've inharited a spreadsheet that I need to change the query to an external source...
I've inharited a spreadsheet that connects to an external source, our SQL
2005 server. I've altered the query to reflect our server name and saved it in Excel as a query with a new name. If I press F5 I can select the range, then I click on the Edit Query icon . First it says "This query cannot be edited by the Query Wizard", I press OK. Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM, Statement(s) could not be prepared", which makes sense since their server is different then ours. If I press OK there it takes me to basically a blank screen/grid. If I click the SQL button there I can see their old query. If I click the Open button my query is in the list (I deleted their from this Open Query dialog box) and can select mine. My query opens fine and has my data there. How do I get this spreadsheet to look at my data rather then the old one I deleted? The Help menu was trying to get me to go to Edit/Links on the Excel menu, but Links is grayed out... I'm stuck, can someone help me out here? I'm using Excel 2003 if it matters Thanks Kelvin |
#2
Posted to microsoft.public.excel.datamap,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
I've inharited a spreadsheet that I need to change the query to an
Hi,
It might be safer, easier, and more flexible if you record a macro to do the thing from scratch. Then instead of modifying the query, you could modify code. If you have the original query, which worked, turn on the VBA macro recorder and right click the query range in the spreadsheet and choose Edit Query. Just click Next Next, Next, Finish and then turn the recorder off. The important part of the code will look like this: With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=C:\Class\Company 9040.mdb;DefaultDir=C:\Class;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeo" _ ), Array("ut=5;")) .CommandText = Array( _ "SELECT TH_Employee.DeptNo, TH_Employee.EmplNo, TH_Employee.FirstName, TH_Employee.LastName, TH_Employee.PhoneNo, TH_Employee.Street, TH_Employee.City, TH_Employee.State, TH_Employee.ZipCode, TH_Employ" _ , _ "ee.`With%`, TH_Employee.PerfRate, TH_Employee.BirthDate, TH_Employee.Dependents, TH_Employee.Check, TH_Employee.ArchiveDate" & Chr(13) & "" & Chr(10) & "FROM `C:\Class\Company 9040`.TH_Employee TH_Employee" _ ) .Refresh BackgroundQuery:=False End With Of course your code connects to an Oracle database not an Access one so there will be differences. A step at a time try modifying the code and test after each change by running the macro. -- Thanks, Shane Devenshire "Kelvin" wrote: I've inharited a spreadsheet that connects to an external source, our SQL 2005 server. I've altered the query to reflect our server name and saved it in Excel as a query with a new name. If I press F5 I can select the range, then I click on the Edit Query icon . First it says "This query cannot be edited by the Query Wizard", I press OK. Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM, Statement(s) could not be prepared", which makes sense since their server is different then ours. If I press OK there it takes me to basically a blank screen/grid. If I click the SQL button there I can see their old query. If I click the Open button my query is in the list (I deleted their from this Open Query dialog box) and can select mine. My query opens fine and has my data there. How do I get this spreadsheet to look at my data rather then the old one I deleted? The Help menu was trying to get me to go to Edit/Links on the Excel menu, but Links is grayed out... I'm stuck, can someone help me out here? I'm using Excel 2003 if it matters Thanks Kelvin |
#3
Posted to microsoft.public.excel.datamap,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
I've inharited a spreadsheet that I need to change the query to an
Thanks for the advice, I'll give that a try
Kelvin "ShaneDevenshire" wrote in message ... Hi, It might be safer, easier, and more flexible if you record a macro to do the thing from scratch. Then instead of modifying the query, you could modify code. If you have the original query, which worked, turn on the VBA macro recorder and right click the query range in the spreadsheet and choose Edit Query. Just click Next Next, Next, Finish and then turn the recorder off. The important part of the code will look like this: With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=C:\Class\Company 9040.mdb;DefaultDir=C:\Class;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeo" _ ), Array("ut=5;")) .CommandText = Array( _ "SELECT TH_Employee.DeptNo, TH_Employee.EmplNo, TH_Employee.FirstName, TH_Employee.LastName, TH_Employee.PhoneNo, TH_Employee.Street, TH_Employee.City, TH_Employee.State, TH_Employee.ZipCode, TH_Employ" _ , _ "ee.`With%`, TH_Employee.PerfRate, TH_Employee.BirthDate, TH_Employee.Dependents, TH_Employee.Check, TH_Employee.ArchiveDate" & Chr(13) & "" & Chr(10) & "FROM `C:\Class\Company 9040`.TH_Employee TH_Employee" _ ) .Refresh BackgroundQuery:=False End With Of course your code connects to an Oracle database not an Access one so there will be differences. A step at a time try modifying the code and test after each change by running the macro. -- Thanks, Shane Devenshire "Kelvin" wrote: I've inharited a spreadsheet that connects to an external source, our SQL 2005 server. I've altered the query to reflect our server name and saved it in Excel as a query with a new name. If I press F5 I can select the range, then I click on the Edit Query icon . First it says "This query cannot be edited by the Query Wizard", I press OK. Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM, Statement(s) could not be prepared", which makes sense since their server is different then ours. If I press OK there it takes me to basically a blank screen/grid. If I click the SQL button there I can see their old query. If I click the Open button my query is in the list (I deleted their from this Open Query dialog box) and can select mine. My query opens fine and has my data there. How do I get this spreadsheet to look at my data rather then the old one I deleted? The Help menu was trying to get me to go to Edit/Links on the Excel menu, but Links is grayed out... I'm stuck, can someone help me out here? I'm using Excel 2003 if it matters Thanks Kelvin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I've inharited a spreadsheet that I need to change the query to an external source... | Excel Discussion (Misc queries) | |||
Using an Excel Spreadsheet as A Data source for Miscrosoft Query | Excel Programming | |||
Change external data source for multiple worksheets | Excel Discussion (Misc queries) | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Query source location - how do I change? | Excel Programming |