none
How to with worksheets RRS feed

  • Question

  • Hello, I have four separate, and different forms that already exist. What I'm trying to do is collect all four of them into one excel workbook, each on it's own page, and write different data to each of them at the same time.

    Currently I can write to each of them independently, but really would like them to be in one book.

    Just for a quick explanation, I have a C# form where all the data needed is entered. Once I click enter, each of the 4 forms are populated with the data they require.

    My second issue is this, when I fill a form, and need an additional page for that same form, how can add it, and continue filling the data?

    This is what I have so far for two of the four forms. Any help would be great, thank you!

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Reflection;

    namespace FormFiller
    {
        public partial class Form1 : Form
        {

            public Form1()
            {
                InitializeComponent();
            }


            private void IPS()
            {
                Excel._Worksheet IPS;
                Excel.Application myExcelApp;
                Excel.Workbooks myExcelWorkbooks;
                Excel.Workbook myExcelWorkbook;
                object misValue = System.Reflection.Missing.Value;
                myExcelApp = new Excel.Application();
                myExcelApp.Visible = true;
                myExcelWorkbooks = myExcelApp.Workbooks;
                String fileName = "C:\\Users\\tsmeester\\Documents\\New_IPS.xlsx";
                myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;                   
                IPS = myExcelWorksheet;
                
                IPS.get_Range("D3").Value2 = txtCustNumber.Text;
                IPS.get_Range("E3").Value2 = txtCustName.Text;
                IPS.get_Range("K3").Value2 = txtPartName.Text;
                IPS.get_Range("K4").Value2 = txtBpRev.Text;
                IPS.get_Range("Q1").Value2 = txtApproval.Text;
                IPS.get_Range("Q2").Value2 = txtDate.Text;
                IPS.get_Range("Q3").Value2 = txtSheetRev.Text;
                IPS.get_Range("R4").Value2 = txtChangeDesc.Text;
                IPS.get_Range("Y2").Value2 = cbOperation.SelectedItem;
                IPS.get_Range("AH2").Value2 = cbNextOp.SelectedItem;
                IPS.get_Range("AJ3").Value2 = txtInprocessFreq.Text;
                IPS.get_Range("AJ4").Value2 = txtCmmFreq.Text;
            }

            private void OAS()
            {           
                Excel._Worksheet OAS;
                Excel.Application myExcelApp;
                Excel.Workbooks myExcelWorkbooks;
                Excel.Workbook myExcelWorkbook;
                object misValue = System.Reflection.Missing.Value;
                myExcelApp = new Excel.Application();
                myExcelApp.Visible = true;
                myExcelWorkbooks = myExcelApp.Workbooks;
                String fileName = "C:\\Users\\tsmeester\\Documents\\New_OAS.xlsx";
                myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
                Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
                OAS = myExcelWorksheet;            
                OAS.get_Range("A2").Value2 = (txtCustNumber.Text) + "  " + (txtCustName.Text);        
                OAS.get_Range("AG2").Value2 = txtPartName.Text;
                OAS.get_Range("AA2").Value2 = txtBpRev.Text;
                OAS.get_Range("BQ4").Value2 = txtApproval.Text;
                OAS.get_Range("AW4").Value2 = txtDate.Text;
                OAS.get_Range("AR4").Value2 = txtSheetRev.Text;            
                OAS.get_Range("O4").Value2 = cbOperation.SelectedItem;  
                OAS.get_Range("BH4").Value2 = txtInprocessFreq.Text;
                OAS.get_Range("BC2").Value2 = txtCmmFreq.Text;
            }




            private void btnEnter_Click(object sender, EventArgs e)
            {
                IPS();
                OAS();
            }
        }
    }

    • Moved by CoolDadTx Thursday, February 5, 2015 3:05 PM Office related
    Thursday, February 5, 2015 1:52 PM

Answers

  • Hi,

    >>I have a C# form where all the data needed is entered. Once I click enter, each of the 4 forms are populated with the data they require

    Currently I can write to each of them independently, but really would like them to be in one book

    Are you means that  you want to write data in one workbook with four separate worksheets If I am right, in your code, you use activeSheet which points to one worksheet, if you want to write data for different sheets, you should try sheet name or sheet index to get different sheets.

    >>My second issue is this, when I fill a form, and need an additional page for that same form, how can add it, and continue filling the data?

    Does it mean add a new sheet to the workbook?

    The following code will show you how to use worksheet index to write data and create a new worksheet.

           private void button1_Click(object sender, EventArgs e)

            {

                string path="d:\\test.xlsx";

                Excel.Application excelapp = new Excel.Application();

                excelapp.Visible = true;

                Excel.Workbooks myworkboooks = excelapp.Workbooks;

                Excel.Workbook myworkbook = myworkboooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                Excel.Worksheet worksheet1 = (Excel.Worksheet)myworkbook.Sheets[1];

                worksheet1.Range["D3"].Value = "sheet1";

                Excel.Worksheet worksheet2 = (Excel.Worksheet)myworkbook.Sheets[2];

                worksheet2.Range["A1"].Value = "sheet2";

                Excel.Worksheet worksheet3 = (Excel.Worksheet)myworkbook.Sheets[3];

                worksheet3.Range["D3"].Value = "sheet3";

                Excel.Worksheet worksheet4 = (Excel.Worksheet)myworkbook.Sheets[4];

                worksheet4.Range["A1"].Value = "sheet4";

           

                // add a newsheet

                myworkbook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);  

            }

    By the way, if you have more than one question, better to post your questions separately, for more community members will be involved, you could get more help.

    Thanks for your understanding.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Thursday, February 12, 2015 11:29 AM
    Sunday, February 8, 2015 7:25 AM
    Moderator