SSIS Script Task - Save A Copy of Excel Template Using ReadOnly variable name

Question

I want to be able to save a copy of an Excel workbook (after adding some data in some cells) as the name of the variable ClientName within a ForEachLoop container using the Script Task code below.

I have some code lifted from Stack OVerflow which already populates specific cells within an Excel Workbook template but I'm trying to save each Workbook on each iteration with the ClientName variable e.g. ClientName.xlsx

Public Sub Main()
    Dim ClientName As String
    Dim ClientAddress As String
    Dim CurrentDate As Date

    Dim m_XlApp = New Excel.Application
    Dim m_xlWrkbs As Excel.Workbooks = CType(m_XlApp.Workbooks, Excel.Workbooks)
    Dim m_xlWrkb As Excel.Workbook
    m_xlWrkb = m_xlWrkbs.Open("C:\Users\UserAZ\Documents\TemplateStatement.xlsx")

    Dim m_XlWrkSheet As Excel.Worksheet = CType(m_xlWrkb.Worksheets(1), Excel.Worksheet)

    'ClientName = (Dts.Variables("User::strClientName").Value).ToString()
    'MsgBox(ClientName)
    'ClientAddress = (Dts.Variables("User::strClientAddress").Value).ToString()
    'MsgBox(ClientAddress)
    'CurrentDate = Today
    'MsgBox(CurrentDate)

    m_XlWrkSheet.Range("A1").Value = ClientName
    m_XlWrkSheet.Range("A2").Value = ClientAddress
    m_XlWrkSheet.Range("B4").Value = CurrentDate

    'm_xlWrkb.Save()
    m_xlWrkb.SaveCopyAs("C:\Users\UserAZ\Documents\" & ClientName & ".xlsx")
    m_xlWrkb.Close(SaveChanges:=True)

    Marshal.ReleaseComObject(m_xlWrkb)
    Marshal.ReleaseComObject(m_xlWrkbs)
    m_XlApp.Quit()
    Marshal.ReleaseComObject(m_XlApp)


    Dts.TaskResult = ScriptResults.Success

End Sub

I was expecting that after each iteration in the For Loop Container the script task would save the copy of the template using the variable ClientName and move onto the next iteration and repeat.

However, it throws up a dialogue box asking if I wan to save a copy of TemplateStatement.xlsx. If I press don't save it actually moves onto the next iteration. But the whole point is to have it run without any manual intervention.

I'm not familar with VB.Net.

score 0 · Accepted Answer · answered Sep 11 '19 at 20:45

0

First, try to use SaveAs() instead of SaveCopyAs(). In addition, try adding the following lines to prevent showing any dialog:

m_XlApp = New Excel.Application
m_XlApp.visible = False
m_XlApp.DisplayAlerts = False

Dim m_xlWrkbs As Excel.Workbooks = CType(m_XlApp.Workbooks, Excel.Workbooks)
Dim m_xlWrkb As Excel.Workbook
m_xlWrkb = m_xlWrkbs.Open("C:\Users\UserAZ\Documents\TemplateStatement.xlsx")

m_xlWrkb.DoNotPromptForConvert = true   


'...

m_xlWrkb.SaveAs("C:\Users\UserAZ\Documents\" & ClientName & ".xlsx")
m_xlWrkb.Close(SaveChanges:=True)

Helpful links

Excel interop prevent showing password dialog

answered Sep 11 '19 at 20:45

Hadi

36,233
13
65
124

Hi Hadi. Thanks. I added your solution and the dialogue does not pop up e.g. SaveAs() and the other alert indicators etc. However, curious thing is when I comment the MsgBox code I get an error: DTS script task has encountered an exception in user code. When I uncomment the MsgBox lines each Excel Workbook is generated successfully but I have to keep clicking the OK dialogue box on each iteration (which shows me the value of the variables). What is causing this? Because the whole point is not to have to manually intervene on every iteration like these for 300 clients. – Shaye Sep 12 '19 at 12:32
@Shaye you have to uncomment the lines that you are assigning values to `ClientName` and `ClientAddress` and `CurrentDate` variables. I don't think it is related to `Msgbox` commands – Hadi Sep 12 '19 at 13:55

SSIS Script Task - Save A Copy of Excel Template Using ReadOnly variable name

1 Answers1

Helpful links