Assuming you are using Win32 Excel, and OLE, you should be able to accomplish what you want by converting this code into the equivalent perl:

Sub TransposeIt()
  Dim ROr As Range
  Set ROr = Sheets("Hoja2").Range("A1:C5") '5x3 Table {1,2,3;4,5,6;7,8
+,9;10,11,12;13,14,15}
  Sheets("Hoja3").Select
  Sheets("Hoja3").Range(Cells(1, 1), Cells(ROr.Columns.Count, ROr.Rows
+.Count)).FormulaArray = "=TRANSPOSE(Hoja2!" & ROr.Address & ")"
End Sub
[download]

From : Mr. Excel
Note: The TRANSPOSE function must be entered as an Array function.