Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Viewing the result of an sql query in excel.
Hi I try this Q here instead since i was recommended this:
"Hi Kjell There used to be a way to dynamically link Excel to databases, but I think MS had to remove it due to some kind of lawsuit. Best would be to ask this in the Excel.programming newsgroup, as the specialists there will know for sure. -- Cindy Meister (Word MVP)" : My QUESTION: Hi. Background: I have created an add-in for excel that opens a form that lets the user graphically select what data he wants to view. The result of the input is formatted as an sql string, and I want to show the result of the query in an excel document. * the result file i quite big. 256*100 000 rows i might add I am considering the following: 1. Copy data from the Sql server to an Excel sheet. Something like this: Sheet1.Range("A1").CopyFromRecordset myrecordset 2. Is there a way, programmatically, to let excel directly show the result from the sql-query through a "view-connection " to the database? So to say that Excel always shows what data the sql server contains. I would prefer the last solution if that is possible. Or is there any other smart way to do this? Best Regards Kjell Nilsson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Viewing the result of an sql query in excel.
The Giant Killer here is this one little line:
* the result file i quite big. 256*100 000 rows i might add. Now I read that one of 2 ways (neither of which is very good): 256 columns by 100,000 rows or 256 groups of 100,000 rows Prior to Excel 2007, the maximum number of rows on an Excel spreadsheet is 65,536. Excel 2007 will handle 2^20 rows (1,045,576 rows) per sheet. With this in mind, if your 256x100,000 is columns by rows, Excel 2007 could handle the load, but if it is 256*100,000 = 25,600,000 rows, then you're going to need Excel and 26 worksheets in it and some special processing to split the data across the sheets [I happen to have just such an Excel tool already built :-) ... but it reads from a .txt, .dat or .csv type file= :-( ] Because of this limitation I'm going to say that even the answer to #2 is no. Excel really doesn't do it's data retrieval 'live' and in real time. It's more like returning a snapshot recordset using the SQL statement. "kjell nilsson" wrote: Hi I try this Q here instead since i was recommended this: "Hi Kjell There used to be a way to dynamically link Excel to databases, but I think MS had to remove it due to some kind of lawsuit. Best would be to ask this in the Excel.programming newsgroup, as the specialists there will know for sure. -- Cindy Meister (Word MVP)" : My QUESTION: Hi. Background: I have created an add-in for excel that opens a form that lets the user graphically select what data he wants to view. The result of the input is formatted as an sql string, and I want to show the result of the query in an excel document. * the result file i quite big. 256*100 000 rows i might add I am considering the following: 1. Copy data from the Sql server to an Excel sheet. Something like this: Sheet1.Range("A1").CopyFromRecordset myrecordset 2. Is there a way, programmatically, to let excel directly show the result from the sql-query through a "view-connection " to the database? So to say that Excel always shows what data the sql server contains. I would prefer the last solution if that is possible. Or is there any other smart way to do this? Best Regards Kjell Nilsson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Viewing the result of an sql query in excel.
To add to other comments:
I would question the worth of showing the user that much data. Would they actually look at it all ? Provide the user some means to filter the results and only display those of interest. That would greatly simplify you task and provide the user with <useful data. There is no point in just duplicating such a large dataset and requiring XL2007 for it to work. As for updating the data, you can use a database query and set the fresh period to some suitable period. Whilst I would not advise a 'live" update (<30 sec), every 5 minutes or so should be OK, depending on amount of data and network/server speeds. NickHK "kjell nilsson" wrote in message ... Hi I try this Q here instead since i was recommended this: "Hi Kjell There used to be a way to dynamically link Excel to databases, but I think MS had to remove it due to some kind of lawsuit. Best would be to ask this in the Excel.programming newsgroup, as the specialists there will know for sure. -- Cindy Meister (Word MVP)" : My QUESTION: Hi. Background: I have created an add-in for excel that opens a form that lets the user graphically select what data he wants to view. The result of the input is formatted as an sql string, and I want to show the result of the query in an excel document. * the result file i quite big. 256*100 000 rows i might add I am considering the following: 1. Copy data from the Sql server to an Excel sheet. Something like this: Sheet1.Range("A1").CopyFromRecordset myrecordset 2. Is there a way, programmatically, to let excel directly show the result from the sql-query through a "view-connection " to the database? So to say that Excel always shows what data the sql server contains. I would prefer the last solution if that is possible. Or is there any other smart way to do this? Best Regards Kjell Nilsson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy paste SQL query result to excel spreadsheet with formatting | Excel Worksheet Functions | |||
Query on how I would calculate a result in Excel? | Excel Worksheet Functions | |||
Importing result from Access query to Excel but the result only c. | Excel Discussion (Misc queries) | |||
Micosoft Query does not export result set to excel | Excel Worksheet Functions | |||
Returning the result of an SQL/Query to Excel | Excel Programming |