SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found


IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found

Author
Message
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24630 Visits: 5314
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
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52946 Visits: 21195
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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("PTongue").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("P2Tongue102").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("PTongue")
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

Sad
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52946 Visits: 21195
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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?
Debbie Edwards
Debbie Edwards
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2150 Visits: 696
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

Sad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search