Apply VBA code to Excel file from SSIS

Question

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

Does the column range change between files in a predictable way? As in, one type of file always has 3 columns and the other has 6. I ask because this could make for a simpler solution. — sorrell
– sorrell, Commented Feb 12, 2015 at 7:26
No, the files keep changing the number of columns because they are automatic files generated with data about Years and Months. All of that data is on columns instead of lines. And this year they have a set number of columns but next year they will defnitely grow (add to the current number of columns) — luz_andre
– luz_andre, Commented Feb 12, 2015 at 9:28
Maybe try a script task to transpose the data. then save and import? — sorrell
– sorrell, Commented Feb 12, 2015 at 13:08
It was one of the ideas, but how can I start the script from SSIS? It must be executed when the package runs. — luz_andre
– luz_andre, Commented Feb 12, 2015 at 16:12

Community · Accepted Answer · 2017-05-23 11:43:55Z

2

Create a Script Task that is piped into a Data Flow task
Edit the Script Task by double clicking the Script Task and clicking the Edit Script button.
Add references to Excel and CSharp as seen in this answer

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;
}

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

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Feb 12, 2015 at 17:44

sorrell

1,8712 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

luz_andre Over a year ago

That's exactly what I needed. Thank you very much, I'll be able to read from the new range (A8:B12 range in the e.g) with the data in the correct form and with fixed columns. Thank you! I really need to sharp my C# (no pun intended) skills, it really opens a wide range of options, didn't know that C# could work directly with XLS files.

Collectives™ on Stack Overflow

Apply VBA code to Excel file from SSIS

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related