| Author |
Topic |
|
bnhcomputing
Starting Member
22 Posts |
 Posted - 2005-09-23 : 14:25:55
|
| I have about twenty (20) user defined functions / stored procedures.I would like to export the results of all 20 to a single Excel sheet.Ex. Query1 export data to B9:N24 Query 2 export data to B32:E42 Procedure1 export data to B50:P60 AND SO ON….I am completely new to DTS so have now idea how to do this. It looks like DTS would let me make sheets for each query, but I haven't found a way to put multiple result sets on the same sheet.Thanks in advance for all suggestionsHubert Hoffman |
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-23 : 15:29:06
|
| I came across Named ranges that allow me to do this (GREAT!). Slightly different problem now, formatting is messed up. Numberic data is in scientific notation, and regardless what format cell option I select, it stays that way.It appears that DTS is putting my numeric data in Excel as text.--- Additional Info ---Borders & Shading in Excel are also removed, but only from the (numeric as text)cells. Text output to the named excel range keeps the border formatting.Tried to format the output range as numeric first. After DTS execution, formating is changed as described.--- ---Suggestions on how to tell DTS to transfer numeric data as numeric instead of text, and how to keep the borders/shading? |
 |
|
|
rheitzman
Starting Member
16 Posts |
Posted - 2005-09-23 : 17:22:11
|
| You may find it easier to import the SQL data instead of exporting to Excel.Use Excel's Data, Import External Data... feature.You can set properties to refresh on open. |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-23 : 17:53:18
|
| Good suggestion. However, I need to create the excel sheet, from a template, on the fly for a WEB app I'm working on. Thus, DTS. I see there are references to this exact issue on google, but no solution is listed.Hubert Hoffman |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-26 : 10:39:57
|
| Based on the info in the link, I tried:select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\IN.xls', 'SELECT * FROM [Sheet1$]')I also tried:select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\IN.xls', 'SELECT * FROM [Sheet1]')The file only has one sheet named Sheet1. I even tried adding a named range called Sheet1 but it still doesn't work.I get ....could not find the object 'Sheet1$'.... OR ....could not find the object 'Sheet1'.... Any ideas why it doesn't like this?Hubert Hoffman |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-26 : 11:01:32
|
| OK, I am learning slowly...I moved the output excel sheet to the server. Now I am getting:Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this query.It still looks like it't trying to convert numeric data to text. I formatted all the numeric columns as number before trying.Additionally, I tried: select 55.982,0 from sql_Table The data ends up in excel as text.Any ideas how to force numeric output to Excel?Hubert Hoffman<----- Edit Added 09/26/05 ----->From my research, it appears that DTS data is ALWAYS exported to Excel as character (text) data. It has been suggested that it may be possible to overcome this with Excel automation (ie. once the data is in as text, take control of excel, and reformat the data).Does anyone have a simple example of how to do this as an active-X script within the DTS?Example: Use DTS to Export the data. Use the active-x vbscript to open the sheet in excel, and reformat all the text data and numerics again.As always any and all suggestions..... |
|
|
|
gaos
Starting Member
6 Posts |
Posted - 2005-09-26 : 16:30:50
|
| To load data into a single Excel sheet from multiple queries, I used a temp table in database, load the data from various queries to the table first, then export to excel.To format the data in Excel after loading, I recorded an Excel Macro to format it, then run the macro in VB script:Set XLApp = CreateObject("Excel.Application") Set XLBook=XLApp.workbooks.open(FilePath&FileName)XLApp.Run "PERSONAL.XLS!FormatHKReport" |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-26 : 22:01:42
|
| Interesting concept, can an executing macro DELETE itself?I can't have any marcos in the excel when I'm done, or the user will get warning messages.Hubert Hoffman |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-27 : 01:32:23
|
| >>The file only has one sheet named Sheet1. I even tried adding a named range called Sheet1 but it still doesn't work.When running that query make sure that the file is closed and not used by any other applicationsMadhivananFailing to plan is Planning to fail |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-09-27 : 16:38:31
|
| As gaos suggested, I have created an excel macro to do the formatting. When run from the Excel desktop app, it works. However, via scripting it does not.Here is my DTS activeX scriptFunction Main() DIM XLApp, XLBook, VBComp Set XLApp = CreateObject("Excel.Application") Set XLBook=XLApp.workbooks.open("U:\TESTOUT.xls") XLApp.Run "TESTOUT.XLS!FormatReport" Set VBComp = XLBook.VBProject.VBComponents("Module1") XLBook.VBProject.VBComponents.Remove VBComp Set VBComp = Nothing XLBook.Save XLApp.Quit Set XLBook = Nothing Set XLApp = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionI get: Error Code: 0 Error Source = Microsoft Excel Error Description: Programmatic access to Visual Basic Project is not trusted.I have the Excel sheet, but I need to remove the Macro before I can send it out, otherwise the end user gets prompted.Can a Macro delete isself?Any ideas?Hubert Hoffman |
|
|
|
gaos
Starting Member
6 Posts |
Posted - 2005-09-27 : 18:00:36
|
| If you don't want the alert, and you can disable it:xlapp.DisplayAlerts=flasexlapp.AlertBeforeOverwriting=falseAlso, you can put the macro in PERSONAL.XLS book instead of data book if you do not want end users to see the macro. |
|
|
|
|
Multiple Queries to a single Excel sheet