Can someone help me on how to transfer data from a folder with lots excel file to access.
here's my code I dont know why does it not working.

namespace import
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            fbdDialog = new FolderBrowserDialog();
            fbdDialog.SelectedPath = @"C:\WINDOWS\allparts";
        }
        System.IO.DirectoryInfo diDirectoryInfo;
        System.IO.FileInfo[] fiFileInfo;
        FolderBrowserDialog fbdDialog;

        string strPartCode;
        string strFilePath;
        string strPartNumber;
        string strLocationCode;
        string strPartName;

        bool bolIsDirectoryFound = false;
        bool bolIsThreadStarted = false;        
      
        private void invoiceTableBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.invoiceTableBindingSource.EndEdit();
            this.invoiceTableTableAdapter.Update(this.supplierdbDataSet.InvoiceTable);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'supplierdbDataSet.SupplierTable' table. You can move, or remove it, as needed.
            this.supplierTableTableAdapter.Fill(this.supplierdbDataSet.SupplierTable);
            // TODO: This line of code loads data into the 'supplierdbDataSet.InvoiceTable' table. You can move, or remove it, as needed.
            this.invoiceTableTableAdapter.Fill(this.supplierdbDataSet.InvoiceTable);

        }

        void ReadExcelPartFileData()
        {
            // *** Initialize the Excel Application class
            Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
            //Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(strFilePath,
            //    0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
            //    false, false, 0, true, 1, 0);
            //// *** Get the active worksheet using sheet name or active sheet
            // *** Declare and Initialize Variable for Reading Sheet 3

            Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(strFilePath,
                0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
                false, false, 0, false, true, false);

            Excel.Worksheet workSheetPartFile = (Excel.Worksheet)excelWorkBook.Sheets[1];

            String myPartCode;

            try
            {
                // *** Get the needed information from Excel file
                Excel.Range rangeCode = (Excel.Range)workSheetPartFile.get_Range("B1", "B1");
                if (rangeCode.Value2 != null)
                    myPartCode = rangeCode.Value2.ToString();
                else
                    myPartCode = "";

                strPartNumber = myPartCode.Substring(0, 1);

                Excel.Range rangeLocalName = (Excel.Range)workSheetPartFile.get_Range("B2", "B2");
                if (rangeLocalName.Value2 != null)
                    strLocationCode = rangeLocalName.Value2.ToString();
                else
                    strLocationCode = "";


                Excel.Range rangeSciName = (Excel.Range)workSheetPartFile.get_Range("F1", "F1");
                if (rangeSciName.Value2 != null)
                    strPartName = rangeSciName.Value2.ToString();
                else
                    strPartName = "";                

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error message: " + ex.Message);
            }
            finally
            {
                // *** Code below will garage collect the memory of excel objects
                Marshal.ReleaseComObject(workSheetPartFile);
                excelWorkBook.Close(false, "", false);
                Marshal.ReleaseComObject(excelWorkBook);
                excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            diDirectoryInfo = new System.IO.DirectoryInfo(fbdDialog.SelectedPath);
            fiFileInfo = diDirectoryInfo.GetFiles();

            foreach (System.IO.FileInfo fi in fiFileInfo)
            {
                string delimStr = ".";
                char[] delimiter = delimStr.ToCharArray();
                string[] strSplitString = null;
                strSplitString = fi.Name.Split(delimiter, 2);


                strPartCode = strSplitString[0];
                // *** Check and Ensure that the file being read is an Excel File
                if (strSplitString.Length > 1 && strSplitString[1] == "xls")
                {
                    //OpenAndReadPartFile(fi.FullName)
                    strFilePath = fi.FullName;
                    strPartCode = strSplitString[0];
                    //dr["PartNumber"] = strSplitString[0];
                    //dr["FilePath"] = strFilePath;

                    ReadExcelPartFileData();//code to read the file and insert to Access DB
                }
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (fbdDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                bolIsDirectoryFound = true;
        }
    }
}

Please guys!

kvprajapati commented: Please put suitable titles for your questions. -2

Recommended Answers

All 5 Replies

Please stop! naming threads help, urgent help, need help etc
exactly where the exception arises?

check this thread it is also about copying files from one folder to another

i need to copy data from excel to access and not folder to folder.

oh sorry for the previous post i didn't understand your question

Can you please explain the exception you are facing and where in the code?

I have folder with a contents of excel files and i want to get some data in the excel and save to my access.

The code that i posted previously was running but i have trouble in saving to my access I think it only read my file but not totally saving to my access.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.