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!