Hello, I have a code whats inserts .txt document into excel and saves it and than opens the excel file.
How can I do on colum A text to columns function whan excel is already opened ?
Here is my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private Excel.Application m_objExcel = null;
private Excel.Workbooks m_objBooks = null;
private Excel._Workbook m_objBook = null;
private object m_objOpt = System.Reflection.Missing.Value;
private object m_strSampleFolder = "C:\\Documents and Settings\\JPeksa\\Desktop\\";
private void button1_Click(object sender, EventArgs e)
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.OpenText(m_strSampleFolder + "08.2010 - Augusts - Ericsson.txt", Excel.XlPlatform.xlWindows, 1,
Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
false, true, false, false, false, false, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objBook = m_objExcel.ActiveWorkbook;
m_objBook.SaveAs(m_strSampleFolder + "Book6.xls", Excel.XlFileFormat.xlWorkbookNormal,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Visible = true;
m_objExcel.Workbooks.Open(m_strSampleFolder + "Book6.xls", Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
private void button2_Click(object sender, EventArgs e)
{
//what to do here ?
}
}
}
Mohammad HussainPosted Aug 24, 2023, 5:36 PM
Janis PeksaPosted Dec 1, 2010, 7:35 AM
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Excel.Application Excel;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel = new Excel.Application();
Excel.Visible = true;
Excel.Workbooks.Open(this.textBox1.Text);
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Columns["A:A"].Select();
Excel.Selection.TextToColumns(Destination: Excel.Range["A1"], DataType: Microsoft.Office.Interop.Excel.XlTextParsingType.xlFixedWidth, TextQualifier: Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone, ConsecutiveDelimiter: false, TAB: false, Semicolon: false, Comma: false, Space: true, Other: false, OtherChar: "|");
}
private void button3_Click(object sender, EventArgs e)
{
OpenFileDialog OFD = new OpenFileDialog();
if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
this.textBox1.Text = OFD.FileName;
}
}
private void button4_Click(object sender, EventArgs e)
{
}
}
}
Janis PeksaPosted Dec 1, 2010, 2:29 AM
Someone please help.
Suthish NairPosted Nov 30, 2010, 10:35 AM
I suggest better read and loop the text file (split with comma, pipe etc) and create an excel file, so you can avoid text to columns method.
Or, wait or more replys.
Janis PeksaPosted Nov 30, 2010, 9:49 AM
Someone please help out.
Suthish NairPosted Nov 30, 2010, 9:38 AM