CODEWITHSUNDEEP
sql-servervbaexcelssis
luz_andre
luz_andre

Reputation: 35

Apply VBA code to Excel file from SSIS

Good evening everyone. I have to build a SSIS package that does as follows:

1) Execute a VBA code to a XLS file (Transpose a range into another range) 2) Save the XLS (In the same file or as a new file) 3) Import the modified XLS from the Transposed range.

Basically I have to transpose the data inside a XLS that I must import, and I didn't find a good way to do that in SSIS (Since the column range can change between files)

With this simple VBA script I can do that and make SSIS read the data in a very straightforward way. However I'm not finding a way to apply this code without modifying the Excel previously manually to add the script and run the VBA script. I want to automate this so the package prepares the xls, extracts the new data, and save it to a table.

Can anyone shed some ideas on how to apply this code or other ways to do this? The most important point I think is that it's a very specific range that I want to transpose.

Sub myTranspose()
    With Range("a18:ZZ27", Range("a18:ZZ27").End(xlDown))
        .Copy
        Range("a30").PasteSpecial Transpose:=True
    End With
End Sub

Upvotes: 1

Views: 3513

Answers (1)

sorrell
sorrell

Reputation: 1861

  1. Create a Script Task that is piped into a Data Flow task

    STask to DFtask

  2. Edit the Script Task by double clicking the Script Task and clicking the Edit Script button.

  3. Add references to Excel and CSharp as seen in this answer

  4. Add some code similar to the following:

    public void Main()
{
  string filepath = @"c:\temp\transpose.xlsx";
  Excel.Application xlApp;
  Excel._Workbook oWB;

  try
  {
    xlApp = new Excel.Application();
    xlApp.Visible = false;
    oWB = (Excel.Workbook)xlApp.Workbooks.Open(filepath);
    Excel.Range fromrng = xlApp.get_Range("B4", "F5");
    Object[,] transposedRange = (Object[,])xlApp.WorksheetFunction.Transpose(fromrng);
    Excel.Range to_rng = xlApp.get_Range("A8", "A8");
    to_rng = to_rng.Resize[transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1)];
    to_rng.Value = transposedRange;
    xlApp.ActiveWorkbook.Save();
    oWB.Close(filepath);

  }
  catch (Exception ex)
  {
    //do something
  }

Dts.TaskResult = (int)ScriptResults.Success;
}

  5. This gives the following result in the sample transpose.xlsx I created. Transpose

Upvotes: 2

Related Questions