Data Exchange

External Data/SQL query database name changed

Doug Hinschberger — Mon, 19 Jul 2010 21:06:27 GMT

Excel 2007

Querying a SQL server

I have dozens of spreadsheets that query DB_J. The SQL database was replaced with another db with a different name DB_B (part of a beta test for the s/w). Now my Excel queries need to be updated to look at the correct database.

Are there easy ways to do that? Can I "replace" or edit the workbook connections?

I'm moderately familiar with setting up and using ODBC connections, having to relearn stuff after doing it once to remember how I did it the first time, months ago. So I may not have provided enough info here.

Doug

Importing text files, best way? VBA, Access, SQL??

cferart — Mon, 07 Jun 2010 10:03:09 GMT

Hi, I am on a steep learning curve with Excel... I have some great success with importing and joining multiple .csv files via SQL AND QUERY.

So I wondered if I can do something like this with .txt files? Its not a once off situation, so I would like to automate it so others can do it.

Firstly any ideas, or advice about how to start? the .txt files are comma delimited and I can import one by one via the Data import external data process and then clean them up. However this is a tedious process.

One Idea I had was importing into Access (I am a complete beginner here) perhaps easier to do this in Access than in Excel?

As you can see I have lots of ideas??? Happy to take on board any suggestions to investigate further!

many thanks

Catherine

Importing data from large (840MB) XML file into Excel 2007

Cal Fuqua — Mon, 14 Jun 2010 23:08:56 GMT

The State of WA posts .xml data extracts each week. Too bad they are so large I think I'll need a Cray to open it.

Even trying to map and then import just one element, I get an XML parse error. Reason: "Not enough storage is available to complete this operation..."

I have at least 250 gigs of disck space free and about 3 gigs of memory.Running windows 7 32bit, intel core II duo T-6400.

Any suggestions?

External data/SQL queries

lizndill — Wed, 02 Jun 2010 14:31:24 GMT

I am hoping this forum can help....I am having a problem with a particular SQL query reference in Excel 2003. I create hundreds of excel reports with connections to SQL but this one report keeps dropping the connection. It seems to be as soon as the data is refreshed it somehow drops the connection and it essentially become static data. I have the query options set to refresh on open (as regular users need to view the report in real time). I am connecting to SQL 2005 (which was a recent update for us). It is currently importing about 3000 rows. I am not sure if it is the connection or query itself that is the problem or the extensive formulas that reference the data are somehow causing the error (I do not actually get an error message). There are tons of variables that I could share, but maybe if some one could tell me if they have experienced the same or simliar issue. I have tried renaming the file, saving a copy, moving the worksheets to a new file, etc.

Thank you!

Check for no data in Acess

Derek McGill — Sat, 27 Mar 2010 04:20:06 GMT

Hi all

How useing vba/Sql can I check if an Acess table has no data, then if true End Sub ?

Derek

Parameter query in MS Query from Excel

JimboSeoul — Wed, 09 Sep 2009 18:55:21 GMT

I hope someone can help with this issue with a parameter query.

I’m using MS Query from Excel to populate outside data I have access to at work. I have a query that works fine with an expression such as Like '%%Tony Roma%%' to search for names that appear in a particular field in the database. I tried converting this query to a parameter type by replacing Tony Roma in my example with [Enter your name] and the query fails to return data or to ask for a name. Can someone provide guidance on what I’m doing wrong?

Thanks much for your time and take care,

Excel 2007 parameter queries

JohnS — Tue, 08 Dec 2009 16:17:27 GMT

I have used MS Query with the facility to use a cell's content to supply the value of parameters. This has been successful until recently. Now when i try and run such a query I get an error messsage "SQL data type out of range" This happens whatever type of data is involved when ever the query is run, direc t from MS Query or trying to use a cell as a parameter. If I change the parameter brackets for a manual criterion all works.

The original projects still work! When I write them anew though they fail. I must have changed an excel option but I'm blowed if I can see which one. Can anyone help me?

JohnS

Transfer Time from Microsoft office spread sheet 11.0 to excell

Murry — Thu, 29 Oct 2009 19:55:59 GMT

Setup for Problem: format Microsoft office spread sheet 11.0 cell to [hh]:mm:ss

: enter time 335:10:20

Using Excell button in tool bar transfering time to new Excell spread sheet. The time will to be 24 hours (311:10:20) less than time entered.

How Can I get around this problem?

Data Sources in Excel with VISTA

Kenmoore — Wed, 14 Oct 2009 09:51:25 GMT

Got a problem with Excel running on Vista and data sources, we have a set of excel spreadsheets which run fine on an XP system connected to our main SeaChange data base (UNIX) and Microsoft Query works fine as well so that we can change the data query. But when we try to run the same files on Vista the connection does not appear to function we have loaded the same ODBC driver as for XP and created the same data source links, but get the following error "Drivers SQLConnectAttr. failed" next error is "Could'nt read this file" ................. Any ideas?

MSQuery Parameter in WHERE to default to All (including null)

AFSkier — Wed, 19 Aug 2009 16:44:03 GMT

I need an Excel parameter prompt where the user can get a list of ALL items or only the KVI items.

I've tried WHERE Unit = [Enter Unit] OR [Enter Unit] IS NULL. It works great in Access, but I get a syntax error in MSQuery. I tried using a single
prompt in MSQuery, but when I input % wild card only brings back the K items & non of the nulls. Leaving blank in Excel returns no records.

The following is a test SQL that works in MSAccess, but not MSQuery for Excel. The field kvi in the table has K or nulls.

SELECT DISTINCT a1.item_id, a1.kvi_id
FROM p_item AS a1
WHERE (a1.kvi_id Like [Enter "K" for KVI only]) OR ([Enter "K" for KVI only]
Is Null)
ORDER BY a1.kvi_id;

--
Thanks, Kevin

data refresh is altering my formulas

gjbankos — Mon, 27 Jul 2009 16:19:56 GMT

I have an excel spreadsheet (both 2003 and 2007) that pull data from a SQL server table. The table is basically stores corporate metric information for each month. The problem I am having is that I have other cells that add up quarterly information and the formulas are changing when new months are entered into the SQL table and pulled into the excel spreadsheet, even though I have $ signs in the formula.

For example, my SQL table has 6 rows in it right now, Jan through June. I have a cell with a formula that adds up the Jan - March, Apr - June, Jul - Sept, and Oct - Dec. My cell that adds up Apr - June is =SUM(E$13:E$15). If I throw in a July row in my SQL table then refresh the data, my cell that adds up Apr - June changes to =SUM(E$13:E$16). The cells that add up my 3rd and 4th quarter numbers bump down a row.

I don't want my cells that look at my external data to change when new external data is coming in. Is there a setting that I'm missing? Thank you in advance.

refreshing linked queries from Access to Excel

SueHallwas — Fri, 26 Jun 2009 16:11:11 GMT

I have a large Access dbase with many related tables. To produce forecast accuracy charts, I have 5 subqueries & then a query for each of 80+ items. The charts are in Excel & I've imported the queries from Access. One field in the subqueries has to change each month from forecasted to actual and when I refresh the data in Excel, the columns are off and the macro doesn't work. Therefore I have to reimport all those 80+ queries each month (times 5 regions!!). Any suggestions would be greatly appreciated.

Access Export Query newline characters appear in Excel

Steve C — Tue, 16 Jun 2009 13:23:32 GMT

I have an Export Query set up in Access 2007 (linked to SQL Server 2005 tables), which exports to an Excel 2007 spreadsheet. On my PC, it displays correctly, but on another user's it shows the newline characters (or 'carriage returns', in 'memo' fields) as small boxes, which also print. Whilst I could write some VB to remove them from the spreadsheet, it occurs to me that there must be some setting that is different between the two PCs, which are both running the same version of Office 2007. Mine is running Vista Business, and the other XP, but I think the problem is also apparent on another Vista PC.

I have found numerous routines for replacing Chr(10) and Chr(13), but nothing about this apparent discrepancy between similar setups.

Any ideas?

Formulas in hidden columns not copying down when updating Queries

Omar — Sat, 30 May 2009 19:35:45 GMT

I'm using MSQUERY to pull data from our business system so I can do further summary and evaluation. I have multiple columns of formulas to the right of the query. I am having trouble with formulas in hidden columns not copying down. Formulas in columns to the right of the blank areas will copy down correctly. See the attached screen shot to see what I found when I unhid the columns. I've got dozens of these queries set up, and so far only have noticed this on this one sheet.

I've been using this design for a couple years now, but only recently noticed the problem. It just happens I upgraded to Office 2007. I can't guarantee that the problem showed up when I made the switch. The best I can say is I noticed the problem at the same time. I have pdf reports that I will be analysing to see if the problem occured before.

Part of the attached screen shot is my External Data Range Properties to show how I have set things. Specifically, what are the differences in the Insert Cells/Insert Rows choices? I can't seem to find any help file that tells me why I would choose one over the others.

One other thing I'm thinking about is that I have six columns hidden in one group. Is that an issue? I don't think I have that situation on any of my other queries.

I'd appreciate any thoughts/comments. I will also be doing some testing to resolve this. The spreadsheet is kind of important that it be trusted. It just happens to calculate the commission for our sales people.

(Excel to Word) Mail Merge - data not in first sheet

Simon Herbert — Mon, 20 Apr 2009 12:49:41 GMT

I have a named range in a workbook that is being used as source data for a Mail Merge in Word.

Any ideas why the Word document only uses the first (left most) sheet when running the merge even though it has been created using the named range (which exists in a sheet to the right)?

If I swap the sheets round it works fine...

This is using version 2000 of both apps, I have not tried with newer versions yet.

Just curious as to why if using a specific named range it still has to be in the first sheet!

Importing data from query - too few parameters?

Back2Basics — Mon, 06 Apr 2009 15:21:17 GMT

I am using office 2003

I am trying to import data from a database that I am using. However I would like to import the data from a query within the DB.

The query prompts for a "team member name" and only shows the items from the main table that are not completed.

I have tried to import that data using MS Query (Data->Import external data-> new database query)

But when i run through the wizard I simple get an error stating that it has 1 too few parameters. I remember this being talked about at the excel user conference the other day, but can't find the files that cover it - and so can not remember how this is done.

Any help would be appreciated.

Lookup from MSWord list

mjherz — Mon, 06 Apr 2009 15:33:32 GMT

In my workbook I have a column of indices; each row is numbered:

EFS 1.1

EFS 2.1

EFS 2.1.1

EFS 3.1

etc.

In my MSWord document, I have a Word list with the same indices.

How can I pull the data from the Word list into the matching Excel rows? I would like to use an external data function; when I modify the

Word file, I want to see the changes reflected in my Excel doc.

Any help would be greatly appreciated.

Import from csv file into existing spreadsheet

genesis1923 — Thu, 05 Mar 2009 23:42:10 GMT

Hello

I would appreciate some pointers on the best way to proceed with setting up an automated input of data from a csv file.

I have an existing spreadsheet which requires the input of dozens of pieces of information each time it is run. I have the required information in a csv file which is generated weekly.

I want to know the best way of automatically opening the csv file and loading the data into specific cells in the existing spreadsheet. The format of the csv file is always the same. For example, 3rd field on line 1 of csv file will always go to cell B37, 5th field on line 7 will always go to cell D42 and so on.

Your help would be very much appreciated.

Passing a list from Excel to MS Query

Omar — Sat, 26 Jan 2008 19:57:12 GMT

I've tried this several ways, but no success so far. Is it possible to pass multiple values from an Excel cell as parameters in a Data query?

In MS Query, this looks like "IN (1,2,3,4,5)". Or, calling a parameter looks like "[List]" which can be tied to one worksheet cell. However, I've not been able to combine the two yet. I also have the need to do the opposite, as in "show me everything but the items in this list." "NOT IN (1,2,3,4,5)"

My primary motivation is to allow someone else to maintain the spreadsheet. Currently, every time the list changes, I need to go into MSQuery and manually maintain it. Since this is a report that ties into our sales compensation verification procedures, I'd prefer that it would be easier for others to update the spreadsheet.

The only solution I've come up with so far is to create multiple parameters in MSQuery tying to multiple individual cells, but I expect this list to have 12 to 20 items. This could be quite cumbersome.

Thanks for any ideas on this. Even if it is to say it's just not possible.

Omar

Microsoft OLE DB Provider for OLAP Services 8.0 - Connect Error

kellynkts — Thu, 16 Oct 2008 03:08:09 GMT

This was my input:

Data > Import External Data > New Database Query > OLAP Cubes > New Data Source

Using Microsoft OLE DB Provider for OLAP Services 8.0 to connect to a HTTP server (Analysis server)

This was the outcome:

Unable to open connection. A connection with the server cannot be established.

Pls help me.

Username saved somewhere in ODBC connection to i-series

DenisR — Tue, 16 Sep 2008 10:37:30 GMT

We have several shared spreadsheets used to make queries on an i-Series (using Excel 2003).

It worked fine for a while but one day the spreadsheets started to save a user name in the connection. That means that other users need always to change the user name when updating the query.

The ODBC connection on all clients are created as "System DSN" now but I won't tell that they have never been "User DSN".

I have searched for a while now whithout finding where this user name is stored. So I am posting here hoping that somene can help me.

Regards,

Denis

Display the Edit Query Dialog

sam — Sun, 18 May 2008 15:55:29 GMT

I have a query on a sheet (pulling Data from another Excel File).

I want to display the Edit Query Dialog whenever the user Activates the sheet.

1) I dont want to use the Sendkeys method

2) I have tried the below code in the Worksheet_Activate event and It errors out for some reason....

Private Sub Worksheet_Activate()

Range("A4").Select ' first cell in the query
Application.CommandBars("External Data").Controls("&EditQuery...").Execute

End Sub

TIA

Sam

Excel 2003

Oracle User Name in MS Query

Anonymous — Fri, 04 Apr 2008 14:49:02 GMT

I am using MS Query to pull data from an Oracle source into Excel for an application that I am distributing to other users. My Oracle username is showing up when the query is refreshed and I would like to blank this out so that other users to not have to clear it before entering their own username. I cannot find an option anywhere to take this out, so I am wondering if the functionality exists. Any assistance would be appreciated.

Thank You.

How can an XL file know if its on Sharepoint?

Alex J — Fri, 04 Apr 2008 18:59:28 GMT

Not sure if this post belongs in the XL Services forum or here....

Using pathnames in VBA (for instance to change directory or open another file in the same directory) gets more complicated when the file resides on SharePoint.

XL2003 has a new (at least to me) parameter [ThisWorkbook.FullNameURLEncoded] which makes it easier - but the file might be on a standard drive or on SharePoint. How to know whether to use [ThisWorkbook.FullName] or [ThisWorkbook.FullNameURLEncoded] ?

MS Query "Function Sequence Error"

Jim Thomlinson — Fri, 28 Mar 2008 23:38:11 GMT

I am running into an issue creating a parameter query in MS Query going against a DB2 database. When I execute the query I get a "Function Sequence Error" message and the query does not run. Our DB2 is version 8.1. I am using Office XP. Regular queries run just fine. I used to be able to run parameter queries some time ago.

I tried Googling this but did not come up with any definative answers.