Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 9:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
I 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 Sub

Obviously.

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 2008

I’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 successfully

Ive 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.

Debbie
Post #1424600
Posted Wednesday, February 27, 2013 12:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1424717
Posted Thursday, February 28, 2013 3:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
Elliott Whitlow (2/27/2013)
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


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?
Post #1424964
Posted Thursday, February 28, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
Debbie Edwards (2/28/2013)
Elliott Whitlow (2/27/2013)
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


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?


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.

Here is a link that may get you started.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1425039
Posted Friday, March 1, 2013 2:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
Phil Parkin (2/28/2013)
Debbie Edwards (2/28/2013)
Elliott Whitlow (2/27/2013)
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


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?


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.

Here is a link that may get you started.


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?

Debbie
Post #1425397
Posted Friday, March 1, 2013 5:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
Phil Parkin (2/28/2013)
Debbie Edwards (2/28/2013)
Elliott Whitlow (2/27/2013)
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


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?


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.

Here is a link that may get you started.


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?

Debbie
Post #1425442
Posted Wednesday, March 6, 2013 7:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
Still 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 2003

I have a fully working Macro

Sub 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 = False
Set curr = ActiveWorkbook
Workbooks.Open Filename:="C:\Test\NewJobs\ExcelHeaders.xlsm"
Application.DisplayAlerts = False
Set 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.Close
ChDir "C:\Test\"
MyFile = Dir()
Loop
'MsgBox ("Done it")
Application.DisplayAlerts = True
End Sub


So this is in the script task. But when I execute I get

Error: 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

Post #1427413
Posted Wednesday, March 6, 2013 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
Did you follow the link in my previous post? The word 'here' is a hyperlink.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1427433
Posted Wednesday, March 6, 2013 8:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
I 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 add

using 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?

Post #1427441
Posted Wednesday, March 6, 2013 9:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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

Post #1427507
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse