August 25, 2010 at 3:20 pm
Hi Everyone,
'excelsheet1.xls' has a table generated ( output in one of the data flow task)
I have a 'excelsheet2.xls' which is a template (standard format at the work place--local drive)
I have reference fields pointing from 'excelsheet1.xls' into 'excelsheet2.xls'.
So whenever excelsheet1.xls is generated(new values), my excelsheet2.xls (template) is updated
I'm using send-mail-task to send this updated excelsheet2.xls to our email group
if i manually open the excelsheet2.xls, i'm able to see the updated values (it asks to save when we close)
problem: i'm not able to see the updated values in the email (it shows the last manually saved date)
should i write any script code (VB) to save the file before sending thru emails ? if so what's the code because i don't know VB
Please give your suggestions 🙂
Thanks
sachin
August 25, 2010 at 4:16 pm
If I'm understanding this correctly, 'spreadsheet2' had an external data link to grab data from 'spreadsheet1'. Is this correct?
If so, why do you need to worry about sending it out every time 'spreadsheet1' gets updated? When the users open up 'spreadsheet2', it will then update itself from 'spreadsheet1'. Just ensure that 'spreadsheet2's external data link is via UNC file name to a file share, and the users have read access, and you should be good to go.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 25, 2010 at 4:52 pm
ya, if the user opens it manually then there is no issue
but the requirement is:
excelsheet2 has to be sent thru email (email group) 🙁
August 25, 2010 at 9:14 pm
Well, in that case, read this article[/url] about automating Excel. You should be able to take out the T-SQL stuff and use just the script calls to have Excel open then save ExcelSheet2. Use a script task, and just open the file, then save it. Should be pretty simple stuff.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 21, 2010 at 1:03 pm
I see you are still an active member so hopefully you get an email for this.
I went to the above article, great by the way, and was to get the code to
function but I have a question.
I am actually trying to open an XML doc in Excel and do Save As an XLS document, which it does.
But when I right click on the XML file and do "Open With" Excel it asks a question how you want the XML doc opened and I choose "As an XML table". And the data comes in the way I need it.
In Excel if I create a macro it would look like the following:
Sub openXML()
ChDir "C:\Test"
Workbooks.OpenXML fileName:="C:\Test\xml_data2.xml", LoadOption:=xlXmlLoadImportToList
End Sub
My question is there a way to incorporate the LoadOption into
declare @xlWorkbook integer
execute @rs = master.dbo.sp_OAMethod @xlWorkbook, 'Open', @xlWorkBook OUTPUT, 'C:\Test\xml_data2.xml'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply