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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » IS 2008 Triggering an Excel Macro via Script...

18 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Debbie Edwards

Posted Wednesday, February 27, 2013 9:32 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

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

Elliott Whitlow

Posted Wednesday, February 27, 2013 12:59 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Debbie Edwards

Posted Thursday, February 28, 2013 3:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

Elliott Whitlow (2/27/2013)

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

Phil Parkin

Posted Thursday, February 28, 2013 7:28 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226, Visits: 9,458

Debbie Edwards (2/28/2013)

Elliott Whitlow (2/27/2013)

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1425039

Debbie Edwards

Posted Friday, March 01, 2013 2:18 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

Phil Parkin (2/28/2013)

Debbie Edwards (2/28/2013)

Elliott Whitlow (2/27/2013)

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

Debbie Edwards

Posted Friday, March 01, 2013 5:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

Phil Parkin (2/28/2013)

Debbie Edwards (2/28/2013)

Elliott Whitlow (2/27/2013)

Post #1425442

Debbie Edwards

Posted Wednesday, March 06, 2013 7:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

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

Sad

Post #1427413

Phil Parkin

Posted Wednesday, March 06, 2013 8:03 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226, Visits: 9,458

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1427433

Debbie Edwards

Posted Wednesday, March 06, 2013 8:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

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

Debbie Edwards

Posted Wednesday, March 06, 2013 9:47 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 415, Visits: 621

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

Post #1427507

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions