A Dynamic Query Worksheet?

I have the unfortunate task of converting a SQL Server database into 
stand-alone Excel file that will store all the data, as well as act a
a "front-end' for browsing the data.

I'd like to have an opening page of many different hyperlinks whic
would send the user to a second worksheet and also pass a variable. 
The second worksheet would list the results of a query that would us
the variable as a parameter and would get its data from a huge table o
a 3rd Spreadsheet.

Is this idea possible? If so, I'd appreciate any ideas, tips, hints o
how to get this done.


Message posted from http://www.ExcelForum.com

6/22/2004 2:50:31 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies

Similar Articles

[PageSpeed] 15

Second time I've posted this in as many minutes! From the Excel help o

The following example creates a hyperlink to cell F10 on the workshee
named Annual in the workbook Budget Report.xls, which is stored on th
Internet at the location named www.business.com/report. The cell on th
worksheet that contains the hyperlink displays the contents of cell D
as the jump text:

report.xls]Annual!F10", D1)

As for passing the data, simply link the target spreadsheet to the on
you're jumping from. i.e. have both sheets open. Select the targe
cell. Type =. Select the source sheet and the cell where the sourc
data is. Now when you open the target sheet you will be prompted wit
something like " This worksheet has data linked to another worksheet
do you wish to update it" or something like that

Message posted from http://www.ExcelForum.com

6/22/2004 3:09:58 PM
Dear Indra

We use SQL Server at work as our Data Warehouse, and MS 
Excel as our reporting tool (Much cheaper than buying 
Crystal reports or Business Objects). If your parameters 
are unlikely to change it is probably better to build a 
view (query) in SQL Server and link to it from Excel by 
using <Data/Get external data/New database query> then 
following the steps in the wizard to select your data 
source. This in turn will open MS Query where you can 
select the view from SQL Server and then drop the fields 
into MS Query. After you are happy that you have all the 
fields return the data to Excel and finish off the 
instructions from the wizard. Depending on how you want to 
work it, you may want to set the spreadsheet to refresh on 
open, but this could be time consuming if you have large 
volumes of data. It is probably easier to set a button 
that the user can click to refresh the data on the 
individual sheet.

If your parameters will change depending on the the user, 
you can prompt the user for a value by going through the 
same process above, but linking to the SQL Server database 
table in MS Query, and then adding a parameter criteria in 
the criteria area of the relevant field (will look like ---
 [Please enter the month you want to view] ---), and once 
again finishing off the wizard.

The same process is also possible when wanting to provide 
users with pivot tables to view, just use the pivot table 
wizard in the data menu.

Obviously your front sheet can then hyperlink to the pages 
that it needs to.

Sorry if I have been a bit wordy, but it is probably 
better to get the process right before you start (The 
woeful voice of experience!!)

Hope this helps

Paul Falla
>-----Original Message-----
>I have the unfortunate task of converting a SQL Server 
database into a
>stand-alone Excel file that will store all the data, as 
well as act as
>a "front-end' for browsing the data.
>I'd like to have an opening page of many different 
hyperlinks which
>would send the user to a second worksheet and also pass a 
>The second worksheet would list the results of a query 
that would use
>the variable as a parameter and would get its data from a 
huge table on
>a 3rd Spreadsheet.
>Is this idea possible? If so, I'd appreciate any ideas, 
tips, hints on
>how to get this done.
>Message posted from http://www.ExcelForum.com/
paul_falla (99)
6/22/2004 3:53:17 PM
Thank you for the reply,
but unfortunately, I need this to be purely stand-alone, 100% in Excel
with the assumption that there will be no access to the network.

But, I appreciate the response

Message posted from http://www.ExcelForum.com

6/22/2004 5:00:13 PM
Indra7 wrote ...

> unfortunately, I need this to be purely stand-alone, 100% in Excel,
> with the assumption that there will be no access to the network.

Can you bend the rules a little? I'm thinking you could convert to a
local file-based database e.g. a Jet .mdb file: data and schema is
wrapped up in one file and you get at least some attempt at DRI and
reasonable SQL-92 compliance for queries. Even having the data in a
separate .xls file would mean you could query the data using ADO and
SQL using the Jet provider i.e. no DRI but the same reasonable SQL-92
syntax (you can't query an open workbook due to the memory leak bug).
If you are compelled to store the data in the same .xls, could you get
away with dynamically creating a (closed) copy of the workbook at
run-time when you need to query the data?

Using data in workbook and no other file will mean you cannot reuse
any of your existing SQL queries. I'm sure your client will be
interested in the savings associated with code reuse rather than a
total re-engineering.


jamiecollins (192)
6/23/2004 9:47:38 AM

Similar Artilces:

Trying to write a formula that changes which worksheet to used
I am trying to write a formula that uses a different worksheet in the current workbook depending on the value from a lookup result and/or the direct entry of a worksheet name in one cell. For instance, if my lookup result is November, I want to pull information from cells on the November worksheet, if December, information from the December worksheet, etc. This way, all I have to do is change the lookup value and/or the entry to get different results based on the appropriate worksheet. This is useful in repetitive activities using the same layout but different numbers. Any help would...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Controling queries displayed when clicking Load from Query button?
I want to be able to control the queries that are displayed when the Load from Query toolbar button is displayed. Can anyone tell me how the filter box list gets populated? Thanks. Paco ...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

Query to conditionally handle duplicates
I'm creating a Bill of Materials using a one-to-many relationship table between InternalPN and ExternalPN. So the query sometimes returns more than one record when an InternalPN is specified, because there are multiple External PN's. I would like to alert the user to this in my query by substituting the word "Multiple" for the ExternalPN. I can find multiples easily enough with the Query Wizard & so have created a separate query called QryPNMultiple. I'm trying to refer to that query in a dlookup statement in my BOM query. I've tried this: Expr1:...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...

Averaging across worksheets
I have a bunch of worksheets showing rental income data across four different cities. Within each city there are about eight (8) different real estate types (ie 1 bedroom, 2 bedroom etc.). How can I calculate an average (in a new worksheet) of a specific real estate type across the four cities when the respective data ranges in each city do not appear in the same rows in each worksheet? However, the ranges will be in the same columns across each worksheet. Furthermore, in a different column on each worksheet will be the name of the 'real estate type' which is consistent across all ci...

Suggestion: Make all find and queries case sensative or none We've discovered that the queries are not case sensative while the find functions (for example, the find function in the requisition vendor applet) tend to be case sensative. This creates a lot of confusion for the end users and a lot of "I can't find..." support calls. Consistency is a good thing, especially in a user interface. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Dynamic Range
Hi All, I have the following two dynamic ranges =OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),1) =OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),2) Note: there will be two ranges namecount & namelist The formula which would be using the above range is =IF(MAX(namecount)<ROW(1:1),"",VLOOKUP(ROW(1:1),namelist,2)) Big question!!!, can I join these ranges together in to one statement. if so than how ??? Thansks, Rashid Hi! If I follow what you want: Named range: List Refers to: =OFFSET(PBR!$A$1:$B$1,,,COUNTA(PBR!$A:$A)) Then: =IF(MAX(INDEX(List,,1))<ROW(1:1),"",VLOOKUP(R...

Business Card query
I wish to produce my own business cards for a self catering flat with details on one side and a picture on the other side. So far I have produced the details and the photograph in Word but now want to match the two sides so that when printed they coincide. I wondered if Publisher has this facility. Blair What is on the right on the front will have to be on the left for side two. Might do better with either two pages or two separate publications. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Blair" <Darrach@aol.co...

How to run a date based query for many months
Hello, I have one query based on a parameter "MyDate". I enter a full date ( dd/mm/yyyy french format but no matter ) and the query result is expressed by the month ( yyyy_mm) eg : i enter 25/12/2009 and the result is expressed in 2009_12 I have to run this query for n months before the entered date and 2 months after this date. Is it possible to get a SQL code for this ? Can you help me ? Thanks by anticipation. It would help if you posted the SQL of your query. Also, your requirement as stated is a bit vague. If you enter 25/12/2009 an...

How to return to current worksheet in a macro
I'm trying to make a macro that copies a few lines from worksheet1 into the current worksheet2. The problem is that the name of worksheet2 can change so I can't use Windows("worksheet2").Activate to return the focus before doing a paste. How do I do this please? Hi normally no need at all for select / activate. use something like worksheets("sheet2").range("A1:A100").copy _ destination:=worksheets("sheet1").range("A1:A100") -- Regards Frank Kabel Frankfurt, Germany "CWatters" <colin.watters@pandoraBOX.be> sch...

dynamic menus
I want to be able to create a hierarchy of menus under the view menu. I don't know at design time how many items will be under each submenu. I have been able to access and add to the view menu itself. By I have not been able to add anything under that added menu. I know it shuold be a popup menu. but when I make it a popup, I get an assert when I try to open the view menu. Here's what I have so far (some experimentation still remains): m_pWndMenu = GetMenu(); //int count = m_pWndMenu->GetMenuItemCount(); //CString string; //m_pWndMenu->GetMenuString(2, string, MF_BY...

Link in Outlook to an excel worksheet
I would like to no how to create a link in an outlook email to a worksheet in an excel workbook. Equivilent to Insert/file as hyperlink However, I want the path to specify the worksheet name so the paticular worksheet will be activated when the workbook is opened. Any help would be appreciated.. Hi, you could write a simple Auto_Open macro to select the sheet you want There's plenty of help on Auto_Open macros on exceltip.com or via google search. Ar -- HiAr ----------------------------------------------------------------------- HiArt's Profile: http://www.excelforum.com/mem...

Dynamics Workflow windows in Dynamics
Hi there I have installed Dynamics Workflow for Dynamics version 10 successfully. I have created a simple Purchase Order workflow. I have clicked the Activate Workflow button in this company. However when the users capture a Purchase Order it does not have the additional workflow compnonents on the Purchase Order window e.g. Submit etc. I have logged out and back in again. What am I missing? Regards Robin The workflow functionality in GP 10 requires the deployment of MOSS 2007 (Enterprise Edition). Not a trivial thing. "Robinv" wrote: > Hi there > > I have ...

Querying Dates
Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the st...

Creating a dynamic chart
I posted here a few days ago and was given some ideas on how to do this. However, I seem lost. I have a main worksheet which is updated from several worksheets by using code. On this worksheet is approximately 15-20 columns something like this. trimester year RW RW% WIW WIW% etc 1st 2001 80 89% 79 75% 2nd 2001 90 89% 84 87% 3rd 2001 87 88% 92 89% 1st 2002 88 88% 90 88% etc etc What I would like to do is create one chart and by selecting the title of the column from either a combo box or by using a bu...