InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 09:12:40 GMT20http://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxYes Im happy that Excel is on the machine and that the right version is being used. I also went through this documentation http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8b52557b-cd13-4db6-87b6-230b7e051a9e/ http://www.microsoft.com/en-us/download/confirmation.aspx?id=227http://www.databasejournal.com/features/mssql/article.php/3921141/SSIS-Script-Task-and-Microsoft-Office-Automation.htmto ensure the correct Excel object is referenced.o If you are automating Microsoft Office Excel 2007, the type library appears as Microsoft Excel 12.0 Object Library in the References list. o If you are automating Microsoft Office Excel 2003, the type library appears as Microsoft Excel 11.0 Object Library in the References list. o If you are automating Microsoft Excel 2002, the type library appears as Microsoft Excel 10.0 Object Library in the References list o If you are automating Microsoft Excel 2000, the type library appears as Microsoft Excel 9.0 Object Library in the References list. o If you are automating Microsoft Excel 97, the type library appears as Microsoft Excel 8.0 Object Library in the References listSo Im happy with all that.Fri, 08 Mar 2013 09:39:12 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxDid you verify that Excel is installed on the machine you are trying to execute the SSIS package on?Fri, 08 Mar 2013 08:58:12 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxOk so I am further on with it now I have clicked build.I have added some more script around the code I already had' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task within Integration ServicesImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Sub FormatSpreadsheetPS_V3() My VBA Script End SubEnd ClassNow I have done this I have lots and lots of errors such as......'DisplayAlerts' is not a member of 'Microsoft.SqlServer.Dts.Runtime.Application'Name 'Workbooks' is not declared. Name 'curr' is not declared.Name 'ActiveWorkbook' is not declared.Name 'EXH' is not declared. And so on and so on so obviously I need to do something else. The good thing is that at least its doing something now.If any one has any best guesses what my next move should be it would be greatly apprieciated.(I imagine I may get a comment that just says declare everything, I understand that but some more input on how you go about doing this would be good)DebbieFri, 08 Mar 2013 05:51:14 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxAs an extra on this...I went back to the original error in the Integration Services script packageThe Binary Code for the script is not found. Please open the script in the designer by clicking edit script button and make sure it builds successfullyI hadnt realised I could click Build so I did and now I have a new error coming up (Which is at least getting somewhere)Statement is not valid in a namespace.Using this I think I might have found a forum post that does what I need. Gives you the extra bit of script and shos you where the code goeshttp://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/87f896aa-5fff-40d3-a054-b0cc4d742ad4Im not totally sure yet as I havent tested it but that was what I should have done... Clicked build and then used this to get further errorsFri, 08 Mar 2013 04:39:30 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxThanks for that. I wouldnt go for the second option, Its way beyond my capabilites.There are lots of docs on the internet saying you can add a script task in Integratin services and then deploy the vBA script from their (The macro)bu then it all gets vague and doesnt tell you the specifics on how to do it. Its very frustrating. I have code which works and an IS package to add it too. I just dont have the bit of script that would call the macro wthin the package.I have a meeting with my boss tomorrow where Im going to tell him Ive been beaten. Gutted!Thu, 07 Mar 2013 11:09:45 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxI was under the impression that you can output data to Excel from SSIS but you can't format it; which it sounds like it is what you are trying to do.The following is only a suggestion and would only work in Excel 2007 and higher.From office 2007 onwards, all the document types (docx,xlsx etc) are effectively XML files with a different extension; all of the formatting is held within the XML tags. You could use a script to generate the document headers and follow the DOM (available from the microsoft site) to actualy build the XML string that will create the document. It really depends on how complicated your formatting gets.I admit to never having used this technique in SSIS, but I did successfully write programs in Progress4GL which generated a xlsx document with cell formatting and the principle woudl be similar.Another alternative would be to have a .bat file that opens Excel with a specific file and macro that loops through the created spreadsheets and applies the formatting. It is a wicked combination of technologies and I wouldn't fancy managing the ongoing development but in one respect it is easier because the components are all separate and the stack can be built gradually.Excel Master file with a macro that runs on opening.bat file that calls Excel with the Master File which triggers the script to runExecute process task that calls the batch fileThu, 07 Mar 2013 10:46:30 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Phil Parkin (3/6/2013)[/b][hr][quote][b]Debbie Edwards (3/6/2013)[/b][hr]Im horribly close to having to sign this off as a no can do at the moment which is really dissapointing.From what I can gather I possibly need to execute the VBA Script using c# script within the IS Task but again there seeems to be no documentation on how to do this.But Again I cant get enough out of the information I have to know that that is right:([/quote]You need a Script Task in SSIS which will perform the Excel automation for you. This task would need to effectively load the spreadsheet in Excel - programmatically - and call the macro. That's roughly what the link I posted was doing - albeit from a native C# app rather than a script task (should not be much difference).Without C# or VB skills, I believe you will struggle to complete this task, I am afraid.[/quote]What I was hoping for is something likeThis is the extra bit of code you need to add and this is why...And here is where you put the VB Code you have already created.At the moment I dont know what section is what, or if I need to change the code I have already created to include more information.I thought there would be something somewhere that would take you through the process step by step so I could learn how to do it.Do I need to add C# code or can I stick with VB?From what you have just said I do have a script task in SSISThe Macro I have successfully does everything I need it too with the xlsThe only bit I have missing is where I call the macro within the taskThats the only bit Im missing. And I cant work out where that bit is in the link you sent me an how it works with the VB Script I already have.I do hate so being beaten when I feel like Im so near. :crazy:Thu, 07 Mar 2013 01:52:35 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Debbie Edwards (3/6/2013)[/b][hr]Im horribly close to having to sign this off as a no can do at the moment which is really dissapointing.From what I can gather I possibly need to execute the VBA Script using c# script within the IS Task but again there seeems to be no documentation on how to do this.But Again I cant get enough out of the information I have to know that that is right:([/quote]You need a Script Task in SSIS which will perform the Excel automation for you. This task would need to effectively load the spreadsheet in Excel - programmatically - and call the macro. That's roughly what the link I posted was doing - albeit from a native C# app rather than a script task (should not be much difference).Without C# or VB skills, I believe you will struggle to complete this task, I am afraid.Wed, 06 Mar 2013 10:07:44 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxIm horribly close to having to sign this off as a no can do at the moment which is really dissapointing.From what I can gather I possibly need to execute the VBA Script using c# script within the IS Task but again there seeems to be no documentation on how to do this.But Again I cant get enough out of the information I have to know that that is right:(Wed, 06 Mar 2013 09:47:35 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxI did. I read this along with lots of other articles but Im still far from understanding whats going on...I dont know C# code at all so by that point its gets very difficult to understand. Is it saying that you addusing System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using Excel = Microsoft.Office.Interop.Excel;Before you then add your VBA Script?Wed, 06 Mar 2013 08:13:33 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxDid you follow the link in my previous post? The word 'here' is a hyperlink.Wed, 06 Mar 2013 08:03:39 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxStill getting absoloutely no where with this.I have made sure that the script is referencing Microsoft Excel 11.0 Object Library because the Macro was created in Excel 2003I have a fully working MacroSub FormatSpreadsheetPS_V1()'' FormatSpreadsheet Macro''This version uses the C: directory with C:\Test, C:\Converted and C:\Test\NewJobs. The original Template.xlsx without Job Family'' Keyboard Shortcut: Ctrl+f Dim MyFile As String ' change to correct directory ChDir "C:\Test\"Application.DisplayAlerts = False' ' Sets up the variable "MyFile" to be each file in the directory MyFile = Dir(CurDir() & "\" & "*.csv") Do While MyFile <> "" 'MsgBox MyFile Workbooks.Open Filename:=MyFile Application.DisplayAlerts = FalseSet curr = ActiveWorkbook Workbooks.Open Filename:="C:\Test\NewJobs\ExcelHeaders.xlsm" Application.DisplayAlerts = FalseSet EXH = ActiveWorkbook Sheets("Headers").Select Rows("1:1").Select Selection.Copy curr.Activate Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Sheets(1).Select Sheets(1).Name = Left(ActiveSheet.Name, 4) 'fit to contents Cells.Select Selection.Columns.AutoFit 'Format Header Range("A1:O1").Select Range("N1").Activate Selection.Font.Bold = True Selection.Font.Underline = True Columns("A:N").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With' ActiveWorkbook.SaveAs Filename:="C:\Test\Converted\" + Left(ActiveSheet.Name, 4) + ".xlsm", _' FileFormat:=xlOpenXMLWorkbookMacroEnabled 'Add newPostWorksheet Sheets.Add After:=Sheets(Sheets.Count) Sheets("Sheet1").Select Sheets("Sheet1").Name = "NewPosts" ChDir "C:\Test\Converted" Workbooks.Open Filename:="C:\Test\NewJobs\NewJobs.xlsx" Application.DisplayAlerts = False Set NWJ = ActiveWorkbook Columns("A:A").Select Range("A:A").Activate Selection.Copy ActiveWorkbook.Close Application.DisplayAlerts = True Range("A1").Select ActiveSheet.Paste Range("A1:A14").Select 'Create Drop down list for new posts on original worksheet new version DE 22/02/2013'Go to the New Posts Worksheet and add a Name range for the list Sheets("NewPosts").Select Columns("A:A").Select ActiveWorkbook.Names.Add Name:="NewJobTypes", RefersToR1C1:="=NewPosts!C1" 'Then go back to worksheet This needs amending to be any worksheet not just the example one of 4001 'Sheets("4001").Select this is what originally appeared in the macro Sheets(1).Select 'This selects sheet 1 no matter what it is called '1st attempt I missed this out but this actually creates the column header in P1 Range("P1").Select ActiveCell.FormulaR1C1 = "NewPosts" Selection.Font.Bold = True Selection.Font.Underline = True 'Now select the whole column to add the validation list Columns("P:P").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=NewJobTypes" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Choose the new grade " .ErrorTitle = "Error" .InputMessage = "Choose the new grade for this person" .ErrorMessage = "You have chosen an incorrect grade" .ShowInput = True .ShowError = True End With 'Go back to the header row and clear validation Range("P1").Select With Selection.Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .ShowInput = True .ShowError = True End With 'Create Drop down list for new posts on original worksheet NOT WORKING COMMENTED THE WHOLE THING OUT DE ' Sheets(1).Select ' Range("P1").Select ' ActiveCell.FormulaR1C1 = "NewPosts" ' Selection.Font.Bold = True ' Selection.Font.Underline = True ' Selection.Columns.AutoFit ' Range("P2").Select ' Range("P2:P102").Select ' With Selection.Validation ' .Delete ' ' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=NewPosts!$A$1:$A$100" ' ' .IgnoreBlank = True ' .InCellDropdown = True ' .InputTitle = "Choose the new grade " ' .ErrorTitle = "Error" ' .InputMessage = "Choose the new grade for this person" ' .ErrorMessage = "You have chosen an incorrect grade" ' .ShowInput = True ' .ShowError = True ' End With '' 'Add VBA code to active sheet'Set EXH = ActiveWorkbook'Const MODULE_NAME As String = "Module1" ' Name of the module to transfer'Const TEMPFILE As String = "c:\Modul.bas" ' temp textfile'' '** export the module to a textfile' ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE'' 'import the module to the new workbook'' curr.VBProject.VBComponents.Import TEMPFILE'' 'kill the textfile' Kill TEMPFILE 'Protect worksheet and then unprotect editable areas ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("NewPosts").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets(1).Select ActiveSheet.Unprotect ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Columns("P:P") ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="x" + StrReverse(Left(ActiveSheet.Name, 4)) + "x" 'save as password protected excel file in converted folder Range("A1").Select ChDir "C:\Test\Converted\" 'This bit was erroring, possibly because its an XLSM and I am in 2003 'ActiveWorkbook.SaveAs Filename:="C:\Test\Converted\" + Left(ActiveSheet.Name, 4) + ".xlsm", 'FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="x" + Left(ActiveSheet.Name, 4) + "x", CreateBackup:=False 'Adding this in worked but the validation dissapears ' ActiveWorkbook.SaveAs Filename:="C:\Test\Converted\" + Left(ActiveSheet.Name, 4) + ".xls", _ ' FileFormat:=xlExcel5, Password:="x" + Left(ActiveSheet.Name, 4) + "x", CreateBackup:=False 'Created a mini macro at the end to save the file and it worked ActiveWorkbook.SaveAs Filename:= _ "C:\Test\Converted\" + Left(ActiveSheet.Name, 4) + ".xls", FileFormat:= _ xlExcel9795, Password:="x" + Left(ActiveSheet.Name, 4) + "x", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False Workbooks.CloseChDir "C:\Test\" MyFile = Dir() Loop 'MsgBox ("Done it")Application.DisplayAlerts = TrueEnd SubSo this is in the script task. But when I execute I getError: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.Now the last posts explained I need to change the code but didnt go into the how or what you had to do. I have searched for information for days and havent found anything useable.So Im still asking the question.... If anyone knows..... How do I change the abvoe VBA script from Excel 2003 to get it executing through Integration Services 2008:(Wed, 06 Mar 2013 07:46:03 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Phil Parkin (2/28/2013)[/b][hr][quote][b]Debbie Edwards (2/28/2013)[/b][hr][quote][b]Elliott Whitlow (2/27/2013)[/b][hr]I think the missing binary code is your clue. It often (usually?) means that there was a build error in your script. Open the script and check the error list. do a built explicitly and see what the output was.CEWII[/quote]Somethings missing for definite but if I run it from Excel its fine. And when I run through IS thats the only error message I get. IIm not sure what you mean by build expicitly? Im guessing that means just running it via Integration services, through the script task?[/quote]Sounds like you are trying to automate Excel from SSIS. You should know up front that Microsoft does not support this type of call from unattended server processes.You cannot call exactly the same script in SSIS as you developed inside Excel, unfortunately. The contexts in which the scripts run are different.[url=http://social.msdn.microsoft.com/Forums/bs-Latn-BA/exceldev/thread/2e33b8e5-c9fd-42a1-8d67-3d61d2cedc1c]Here [/url]is a link that may get you started.[/quote]That makes sense. Ive scoured the internet to find the proper way of doing this but havent had any success. Did you say you had a good link for how to do this? I couldnt find one on this post?DebbieFri, 01 Mar 2013 05:07:36 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Phil Parkin (2/28/2013)[/b][hr][quote][b]Debbie Edwards (2/28/2013)[/b][hr][quote][b]Elliott Whitlow (2/27/2013)[/b][hr]I think the missing binary code is your clue. It often (usually?) means that there was a build error in your script. Open the script and check the error list. do a built explicitly and see what the output was.CEWII[/quote]Somethings missing for definite but if I run it from Excel its fine. And when I run through IS thats the only error message I get. IIm not sure what you mean by build expicitly? Im guessing that means just running it via Integration services, through the script task?[/quote]Sounds like you are trying to automate Excel from SSIS. You should know up front that Microsoft does not support this type of call from unattended server processes.You cannot call exactly the same script in SSIS as you developed inside Excel, unfortunately. The contexts in which the scripts run are different.[url=http://social.msdn.microsoft.com/Forums/bs-Latn-BA/exceldev/thread/2e33b8e5-c9fd-42a1-8d67-3d61d2cedc1c]Here [/url]is a link that may get you started.[/quote]That makes sense. Ive scoured the internet to find the proper way of doing this but havent had any success. Did you say you had a good link for how to do this? I couldnt find one on this post?DebbieFri, 01 Mar 2013 02:18:29 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Debbie Edwards (2/28/2013)[/b][hr][quote][b]Elliott Whitlow (2/27/2013)[/b][hr]I think the missing binary code is your clue. It often (usually?) means that there was a build error in your script. Open the script and check the error list. do a built explicitly and see what the output was.CEWII[/quote]Somethings missing for definite but if I run it from Excel its fine. And when I run through IS thats the only error message I get. IIm not sure what you mean by build expicitly? Im guessing that means just running it via Integration services, through the script task?[/quote]Sounds like you are trying to automate Excel from SSIS. You should know up front that Microsoft does not support this type of call from unattended server processes.You cannot call exactly the same script in SSIS as you developed inside Excel, unfortunately. The contexts in which the scripts run are different.[url=http://social.msdn.microsoft.com/Forums/bs-Latn-BA/exceldev/thread/2e33b8e5-c9fd-42a1-8d67-3d61d2cedc1c]Here [/url]is a link that may get you started.Thu, 28 Feb 2013 07:28:30 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspx[quote][b]Elliott Whitlow (2/27/2013)[/b][hr]I think the missing binary code is your clue. It often (usually?) means that there was a build error in your script. Open the script and check the error list. do a built explicitly and see what the output was.CEWII[/quote]Somethings missing for definite but if I run it from Excel its fine. And when I run through IS thats the only error message I get. IIm not sure what you mean by build expicitly? Im guessing that means just running it via Integration services, through the script task?Thu, 28 Feb 2013 03:00:42 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxI think the missing binary code is your clue. It often (usually?) means that there was a build error in your script. Open the script and check the error list. do a built explicitly and see what the output was.CEWIIWed, 27 Feb 2013 12:59:42 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic1424600-364-1.aspxI have a Macro which works in Excel. It basically just formats headers and creates a validation list so the user can select something from the list and save the file.It starts Sub FormatSpreadsheetPS_V2()Ans ends End SubObviously. I have an integration package that creates lots of CSV files. The excel macro takes each one of these files and makes a nicely formatted excel document. I want it to run straight after the CSV file build.Before running I have a file system task that deletes all xls ready for the next batch. Next comes my script task, Its set as Microsoft Visual Basic 2008I’ve added the script and I have Set Delay validation to true but still getting this error when I try and execute.The Binary Code for the script is not found. Please open the script in the designer by clicking edit script button and make sure it builds successfullyIve seen this a lot whilst googling for help. Some people say you shouldn’t trigger Macros via Integration Services, Others say you need to download service packs (But that was for 2005 and I’m in 2008)Ive gone in to have a look and Sub FormatSpreadsheetPS_V2() is underlined in blue with the error ‘Statement is not valid in a name space’So Im guessing that I havent yet got the script right to actualy trigger in Integration Services. Maybe I need something else rather that the start and end. If anyone knows of any good help sheets on doing this or what I should be doing that would be great.DebbieWed, 27 Feb 2013 09:32:00 GMTDebbie Edwards