CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    3

    How to correctly transponate Excel Rows in Columns without loosing Formulas

    Hello!


    I am currently trying to implement the exact same Excel functionality: "special paste->transponate" where a certain area (like 256 rows) are correctly transfered in columns in another sheet.
    Sadly I just cant manage to transponate the formula correctly, so that it is transponated as well.
    I am developing under .NET Framework 4 and using Microsoft.Office.Interop.Excel. I thought this should be easy, but I just cant make it work oO.

    Since I am using Excel 2003 I am limited to 256 cols maximum, wich is the reason for the following code.
    I tried out many different stuff by now, but cant make it work. This is the current code, please let me know what I am doing wrong here:

    Code:
     //--------------------------------------------------------------------------
            // convert col to row
            //--------------------------------------------------------------------------
            public void convertRow2Col(string workingTab)
            {
    
                xlInputTabName = workingTab;
                int TabNameCounter = 1;
    
                range_Input = xlWorkSheet_Input.UsedRange;
                range_Output = xlWorkSheet_Output.UsedRange;     // default value
    
                Range ran = xlWorkSheet_Output.get_Range("A:A", System.Type.Missing);
                ran.EntireColumn.ColumnWidth = 110;
    
                xlWorkBook_Output.Worksheets[1].Name = xlInputTabName;
    
                int prev_row_input = 1;
    
    
                //----------------------------------------------------------------------------------------
                //read and write numbers
                //----------------------------------------------------------------------------------------
                for (int row_input = 256; row_input <= range_Input.Rows.Count; row_input+=256)     
                {                                                                                        
                        try
                        {                           
                                string rangeStr = prev_row_input.ToString() + ":" + row_input.ToString();
                                Range input_rng = xlWorkSheet_Input.get_Range(rangeStr, Type.Missing); 
    
                                Object[,] transposedRange = (Object[,])xlApp_Input.WorksheetFunction.Transpose(input_rng.Formula);
                                xlApp_Output.ActiveSheet.Range("A1").Resize(transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1));
                               
                                // next sheet
                                xlSheetCounter++;
                                TabNameCounter++;
                                prev_row_input = row_input + 1;
                                this.xlApp_Output.Worksheets.Add();
                                xlWorkSheet_Output = (Excel.Worksheet)xlWorkBook_Output.Sheets[1];
                                xlWorkBook_Output.Worksheets[1].Name = xlInputTabName + TabNameCounter.ToString();
    
                        }
                        catch (COMException ex)
                        {
                                if (ex.Source != null)
                                    Console.WriteLine("IOException source: {0}", ex.Source);
                                throw;
                        }                  
                }
                                       
                xlWorkBook_Output.Save();
            }
    Thanks for all replies.

  2. #2
    Join Date
    May 2014
    Posts
    3

    Re: How to correctly transponate Excel Rows in Columns without loosing Formulas

    This is the most important part:

    string rangeStr = prev_row_input.ToString() + ":" + row_input.ToString();
    Range input_rng = xlWorkSheet_Input.get_Range(rangeStr, Type.Missing);

    Object[,] transposedRange = (Object[,])xlApp_Input.WorksheetFunction.Transpose(input_rng.Formula);
    xlApp_Output.ActiveSheet.Range("A1").Resize(transposedRange.GetUpperBound(0), transposedRange.GetUpperBound(1));

  3. #3
    Join Date
    May 2014
    Posts
    3

    Re: How to correctly transponate Excel Rows in Columns without loosing Formulas

    I was able to find the correct function, I was just dumb -,-

    -> range.Copy()
    -> range.SpecialPaste(params)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured