private void btn_Export_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value; //声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application(); //实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]); //获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing); //将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name; //获取工作表名称
excel.DisplayAlerts = false; //设置Excel保存时不显示对话框
workbook.Save(); //保存工作簿
CloseProcess("EXCEL"); //关闭所有Excel进程
object P_obj_Excel = (object)txt_Path.Text; //记录Excel文件路径
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll); //关闭并保存Access数据库文件
CloseProcess("MSACCESS"); //关闭所有Access数据库进程
MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Access数据库中已经存在该表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
static void Main(string[] args)
{
string reportName = "Report1";
string controlName = "Label0";
//
// Note: This project has the following COM reference defined:
//
// Microsoft Access 14.0 Object Library
//
var accessApp = new Microsoft.Office.Interop.Access.Application();
accessApp.OpenCurrentDatabase(@"C:\__tmp\testData.accdb");
accessApp.DoCmd.OpenReport(reportName, Microsoft.Office.Interop.Access.AcView.acViewDesign);
Microsoft.Office.Interop.Access.Report rpt = accessApp.Reports[reportName];
int ctlWidth = rpt.Controls[controlName].Width;
Console.WriteLine("control \"" + controlName + "\" in report \"" + reportName + "\" has a .Width value of " + ctlWidth);
accessApp.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acReport, reportName, Microsoft.Office.Interop.Access.AcCloseSave.acSaveNo);
accessApp.Quit();
// wait for a keypress before terminating
Console.ReadKey();
}
private void ImportDataToAccess(string P_str_Excel, string P_str_SheetName)
{
object missing = System.Reflection.Missing.Value; //声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application(); //实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]); //获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing); //将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name; //获取工作表名称
excel.DisplayAlerts = false; //设置Excel保存时不显示对话框
workbook.Save(); //保存工作簿
CloseProcess("EXCEL"); //关闭所有Excel进程
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_str_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll); //关闭并保存Access数据库文件
CloseProcess("MSACCESS"); //关闭所有Access数据库进程
}
catch
{
MessageBox.Show("Access数据库中已经存在" + P_str_SheetName + "表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
}
private void btn_Export_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]);//获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name;//获取工作表名称
excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
workbook.Save();//保存工作簿
CloseProcess("EXCEL");//关闭所有Excel进程
object P_obj_Excel = (object)txt_Path.Text;//记录Excel文件路径
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
CloseProcess("MSACCESS");//关闭所有Access数据库进程
MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Access数据库中已经存在该表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void AbrirInforme(string informe)
{
Microsoft.Office.Interop.Access.Application app = new Microsoft.Office.Interop.Access.Application();
app.Visible = true;
app.OpenCurrentDatabase(Application.StartupPath + "\\InformesInverReg.mdb", false, "");
app.DoCmd.OpenReport(informe, Microsoft.Office.Interop.Access.AcView.acViewPreview);//, "", "", Microsoft.Office.Interop.Access.AcWindowMode.acWindowNormal, "");
}
static void Main(string[] args)
{
// this code requires the following COM reference in the project:
// Microsoft Access 14.0 Object Library
//
var objAccess = new Microsoft.Office.Interop.Access.Application();
objAccess.Visible = false;
objAccess.OpenCurrentDatabase(@"C:\Users\Public\Database1.accdb");
string formName = "MembersForm";
Console.WriteLine(String.Format("The form [{0}] contains the following controls:", formName));
objAccess.DoCmd.OpenForm(formName, Microsoft.Office.Interop.Access.AcFormView.acDesign);
Microsoft.Office.Interop.Access.Form frm = objAccess.Forms[formName];
foreach (Microsoft.Office.Interop.Access.Control ctl in frm.Controls)
{
Console.WriteLine();
Console.WriteLine(String.Format(" [{0}]", ctl.Name));
Console.WriteLine(String.Format(" {0}", ctl.GetType()));
}
objAccess.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acForm, formName);
objAccess.CloseCurrentDatabase();
objAccess.Quit();
Console.WriteLine();
Console.WriteLine("Done.");
}
public void LookupItemByCKey(string TemplateCKey, string ItemCKey = "")
{
string curPath = "";
if (_oAccess == null)
{
try
{
_oAccess = new Microsoft.Office.Interop.Access.Application();
_oAccess.Visible = false;
curPath = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
curPath = $"{curPath}\\{AccessPath}";
Application.UseWaitCursor = true;
Application.DoEvents();
_oAccess.OpenCurrentDatabase(curPath);
Application.UseWaitCursor = false;
}
catch (Exception ex)
{ //Probably, user has closed Access, the TE is closed, but we are still holding on to a handle to the Access process
Application.UseWaitCursor = false;
//if (!(_oAccess is null)) _oAccess.Visible = true;
MessageBox.Show("LookupItemByCKey: There was a problem (0) loading TE in LookupItemByCKey. Details:\r\n\r\n" + ex);
CloseAccess();
return;
}
}
if (_oAccess != null)
{
try
{
object oMissing = System.Reflection.Missing.Value;
if (_oAccess.hWndAccessApp() > 0)
{
try
{
BringToFront();
RunAccessFunction("InvokeLookup", TemplateCKey, ItemCKey);
if (_oAccess != null)
{
_oAccess.Visible = true;
}
//RunAccessFunction("cboTemplateRequery", TemplateCKey);
}
catch (Exception ex)
{ //Probably, user has closed Access, the TE is closed, but we are still holding on to a handle to the Access process
MessageBox.Show("LookupItemByCKey: There was a problem (1) loading TE in LookupItemByCKey. Details:\r\n\r\n" + ex);
CloseAccess();
}
}
}
catch (Exception ex)
{ //Probably, user has closed Access, the TE is closed, but we are still holding on to a handle to the Access process
MessageBox.Show("There was a problem (2) loading TE in LookupItemByCKey");
CloseAccess();
}
}
}
private bool ExportToExtrato()
{
// https://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
var retorno = true;
using (var ctx = new MoneyBinEntities()) {
ctx.spExportToAccessExtrato(_saveAs);
}
MoneyBinDB.ExportToExtrato(_saveAs);
var tempFile = Path.Combine(Path.GetDirectoryName(_saveAs),
Path.GetRandomFileName() + Path.GetExtension(_saveAs));
var app = new Microsoft.Office.Interop.Access.Application {
Visible = false
};
try {
app.CompactRepair(_saveAs, tempFile, false);
var temp = new FileInfo(tempFile);
temp.CopyTo(_saveAs, true);
temp.Delete();
}
catch (Exception e) {
MessageBox.Show(e.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
retorno = false;
//throw;
}
finally {
app.Quit();
}
return(retorno);
}
public void GetProcessId_Test()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase\MSAccessDatabase.accdb");
var myName = microsoftAccess.Run("GetName");
int id;
GetWindowThreadProcessId(microsoftAccess.hWndAccessApp(), out id);
var result = Process.GetProcessById(id);
Assert.IsNotNull(result);
Console.WriteLine(result.Id);
if (microsoftAccess != null)
{
microsoftAccess.Quit();
Marshal.ReleaseComObject(microsoftAccess);
microsoftAccess = null;
}
}
static void Main(string[] args)
{
var accApp = new Microsoft.Office.Interop.Access.Application();
accApp.OpenCurrentDatabase(new FileInfo("db.accdb").FullName);
Microsoft.Office.Interop.Access.Dao.Database cdb = accApp.CurrentDb();
foreach (TableDef item in cdb.TableDefs)
{
Console.WriteLine(item.Name);
}
}
public form_ExternalDB(string selCategory, string extDB, DataGridView dataGridView)
{
selectedCategory = selCategory;
extDBPath = extDB;
extDataGridView = dataGridView;
oAccess = new Microsoft.Office.Interop.Access.Application();
oAccess.OpenCurrentDatabase(extDBPath);
daoDB = oAccess.CurrentDb();
oAccess.Visible = false;
InitializeComponent();
}
public void GetName()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.Visible = true;
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase.accdb", false);
var myName = microsoftAccess.Run("GetName");
Console.WriteLine($"My Name: {myName}");
Thread.Sleep(3000);
microsoftAccess.CloseCurrentDatabase();
microsoftAccess.Quit();
}
public AccessApplicationModel(Microsoft.Office.Interop.Access.Application application)
{
ADOConnectString = application.ADOConnectString;
Build = application.Build;
Name = application.Name;
ProductCode = application.ProductCode;
try
{
ActiveControl = application.Screen.ActiveControl?.Name;
ActiveDataAccessPage = application.Screen.ActiveDataAccessPage?.Name;
ActiveDatasheet = application.Screen.ActiveDatasheet?.Name;
ActiveForm = application.Screen.ActiveForm?.Name;
ActiveReport = application.Screen.ActiveReport?.Name;
}
catch { }
TempVars = new Lazy <AccessTempVarsCollectionModel>(() => new AccessTempVarsCollectionModel(application.TempVars));
VBE = new Lazy <VbeModel>(() => new VbeModel(application.VBE));
CurrentProject = new Lazy <AccessCurrentProjectModel>(() => new AccessCurrentProjectModel(application.CurrentProject));
}
private static void CloseAccess()
{
if (_oAccess != null)
{
try
{
_oAccess.Application.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
_oAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone); //in case Access is still alive
_oAccess = null;
}
catch
{
try //in case Access is still alive} }
{ _oAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone); }
catch { }
}
finally
{ _oAccess = null; }
}
}
public static void LookupItemByCKey(string TemplateCKey, string ItemCKey)
{
if (_oAccess == null)
{
_oAccess = new Microsoft.Office.Interop.Access.Application();
_oAccess.Visible = false;
string path = System.Windows.Forms.Application.ExecutablePath;
path = System.IO.Path.GetDirectoryName(path);
_oAccess.OpenCurrentDatabase(path + "\\TE\\eCC_TE.adp");
}
_oAccess.Visible = true;
try
{
object oMissing = System.Reflection.Missing.Value;
if (_oAccess.hWndAccessApp() > 0)
{
try
{
RunAccessFunction("InvokeLookup", TemplateCKey, ItemCKey);
}
catch
{
//if exception occurrs here most likely Access instance from this application was closed by the user
//start a new instance and try again
_oAccess = new Microsoft.Office.Interop.Access.Application();
_oAccess.Visible = false;
string path = System.Windows.Forms.Application.ExecutablePath;
path = System.IO.Path.GetDirectoryName(path);
_oAccess.OpenCurrentDatabase(path + "\\TE\\eCC_TE.adp");
_oAccess.Visible = true;
RunAccessFunction("InvokeLookup", TemplateCKey, ItemCKey);
}
}
}
catch (Exception ex)
{
throw new Exception("Error in TEInterop: " + ex.Message);
}
}
private void toolStripDropDownButton1_DropDownOpening(object sender, EventArgs e)
{
toolStripDropDownButton1.DropDownItems.Clear();
Moniker[] keys = ROTUtil.EnumRunning();
foreach (Moniker key in keys)
{
Microsoft.Office.Interop.Access.Application app = (Microsoft.Office.Interop.Access.Application)ROTUtil.GetObjectAs(key, typeof(Microsoft.Office.Interop.Access.Application));
if (app != null)
{
ToolStripMenuItem subItem = new ToolStripMenuItem(key.GetDisplayName());
toolStripDropDownButton1.DropDownItems.Add(subItem);
int cx = app.Forms.Count;
for (int x = 0; x < cx; x++)
{
Microsoft.Office.Interop.Access.Form form = app.Forms[x];
ToolStripItem subSubItem = subItem.DropDownItems.Add(form.Name);
subSubItem.Click += new EventHandler(subSubItem_Click);
subSubItem.Tag = form;
}
}
}
}
/// <summary>
/// Opens the report in Access, fills it, and is previewed or printed
/// </summary>
/// <param name="whereClause">The where clasue for the Access Object OpenReport Method</param>
private void createReport(string whereClause, string recordsource)
{
Microsoft.Office.Interop.Access.Application oAccess = null;
oAccess = new Microsoft.Office.Interop.Access.Application();
try
{
if (chkPreview.Checked == true)
{
oAccess.Visible = true;
}
// Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(
@"C:\ClientcardFB3\ClientcardFB3Reports.accdb", //filepath
false //Exclusive
);
// oAccess.DoCmd.DoMenuItem("Shutter Bar", "Navigation Pane", "Close", Type.Missing);
if (recordsource != "")
{
oAccess.DoCmd.OpenReport(
clsAccessReports.ReportTitle, //ReportName
Microsoft.Office.Interop.Access.AcView.acViewDesign, //acViewPreview, //View
System.Reflection.Missing.Value, //FilterName
System.Reflection.Missing.Value); //WhereCondition
oAccess.Reports[0].RecordSource = recordsource;
oAccess.DoCmd.RunCommand(Microsoft.Office.Interop.Access.AcCommand.acCmdPrintPreview);
}
else
{
if (clsAccessReports.UseFilter == false)
{
// Preview a report
if (clsAccessReports.UseWhere == true)
{
oAccess.DoCmd.OpenReport(
clsAccessReports.ReportTitle, //ReportName
Microsoft.Office.Interop.Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, //FilterName
whereClause); //WhereCondition
}
else
{
oAccess.DoCmd.OpenReport(
clsAccessReports.ReportTitle, //ReportName
Microsoft.Office.Interop.Access.AcView.acViewPreview, //View
System.Reflection.Missing.Value, //FilterName
System.Reflection.Missing.Value); //WhereCondition
}
}
else
{
// Preview a report named Sales:
oAccess.DoCmd.OpenReport(
clsAccessReports.ReportTitle, //ReportName
Microsoft.Office.Interop.Access.AcView.acViewPreview, //View
clsAccessReports.FilterName, //FilterName
whereClause //WhereCondition
);
}
}
if (chkPreview.Checked == true)
{
MessageBox.Show("When Done Viewing Report Click OK", "Report Viewer", MessageBoxButtons.OK);
// Close the report preview window:
oAccess.DoCmd.Close(
Microsoft.Office.Interop.Access.AcObjectType.acReport, //ObjectType
clsAccessReports.ReportTitle, //ObjectName
Microsoft.Office.Interop.Access.AcCloseSave.acSaveNo //Save
);
((Microsoft.Office.Interop.Access._Application)oAccess).
Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
}
else
{
// Print 2 copies of the active object:
oAccess.DoCmd.PrintOut(
Microsoft.Office.Interop.Access.AcPrintRange.acPrintAll, //PrintRange
System.Reflection.Missing.Value, //PageFrom
System.Reflection.Missing.Value, //PageTo
Microsoft.Office.Interop.Access.AcPrintQuality.acHigh, //PrintQuality
2, //Copies
false //CollateCopies
);
MessageBox.Show("Click OK When Report Is Done Printing", "Printing Report", MessageBoxButtons.OK);
((Microsoft.Office.Interop.Access._Application)oAccess).Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
}
}
catch (Exception ex)
{
CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(),
"Access Jet Datase Engine");
((Microsoft.Office.Interop.Access._Application)oAccess).Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
}
}
/// <summary>
/// Opens the report in Access, fills it, and is previewed or printed
/// </summary>
/// <param name="whereClause">The where clasue for the Access Object OpenReport Method</param>
private void openReportForm(string whereClause)
{
Microsoft.Office.Interop.Access.Application oAccess = null;
oAccess = new Microsoft.Office.Interop.Access.Application();
try
{
if (chkPreview.Checked == true)
{
oAccess.Visible = true;
}
// Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(
System.IO.Path.Combine(CCFBGlobal.pathExe, "ClientcardFB3Reports.accdb"), //filepath
false //Exclusive
);
// oAccess.DoCmd.DoMenuItem("Shutter Bar", "Navigation Pane", "Close", Type.Missing);
if (clsAccessReports.UseFilter == false)
{
oAccess.DoCmd.OpenForm
(clsAccessReports.ReportTitle //ReportName
, Microsoft.Office.Interop.Access.AcFormView.acPreview //View
, System.Reflection.Missing.Value //FilterName
, whereClause //WhereCondition
);
}
else
{
// Preview a report named Sales:
oAccess.DoCmd.OpenForm(clsAccessReports.ReportTitle //ReportName
, Microsoft.Office.Interop.Access.AcFormView.acPreview //View
, clsAccessReports.FilterName //FilterName
, whereClause //WhereCondition
);
}
if (chkPreview.Checked == true)
{
MessageBox.Show("When Done Viewing Report Click OK", "Report Viewer", MessageBoxButtons.OK);
// Close the report preview window:
oAccess.DoCmd.Close(
Microsoft.Office.Interop.Access.AcObjectType.acReport, //ObjectType
clsAccessReports.ReportTitle, //ObjectName
Microsoft.Office.Interop.Access.AcCloseSave.acSaveNo //Save
);
}
else
{
// Print 1 copies of the active object:
oAccess.DoCmd.PrintOut(
Microsoft.Office.Interop.Access.AcPrintRange.acPrintAll, //PrintRange
System.Reflection.Missing.Value, //PageFrom
System.Reflection.Missing.Value, //PageTo
Microsoft.Office.Interop.Access.AcPrintQuality.acHigh, //PrintQuality
numericUpDown1.Value, //Copies
false //CollateCopies
);
MessageBox.Show("Click OK When Report Is Done Printing", "Printing Report", MessageBoxButtons.OK);
}
}
catch (Exception ex)
{
CCFBGlobal.appendErrorToErrorReport("openReportForm: " + whereClause, ex.GetBaseException().ToString());
}
((Microsoft.Office.Interop.Access._Application)oAccess).Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
}
private void Work_Macro_Click(object sender, EventArgs e)
{
//Проверяем существование базы данных в текущем каталоге
String db_filename = Directory.GetCurrentDirectory() + @"\db.accdb";
if (File.Exists(Directory.GetCurrentDirectory() + @"\db.accdb") == false)
{
//Создание каталога ADOX
ADOX.Catalog adoxCat = new ADOX.Catalog();
//Создание базы данных
adoxCat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Directory.GetCurrentDirectory() + @"\db.accdb" + ";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password="******"\db.accdb", false);
//Создаем ссылку класса VBProject на текущий объект БД
Microsoft.Vbe.Interop.VBProject project = oAccess.VBE.VBProjects.Item(1);
//Создаем ссылку класса VBComponent на созданный модуль БД
Microsoft.Vbe.Interop.VBComponent module = project.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
//Вставляем текст в модуль
module.CodeModule.AddFromString(Properties.Resources.Macro);
//Сохранение макроса
oAccess.DoCmd.Save(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString());
//Присваеваем список схем и выбранных таблиц
string[] Schemas = new string[ListBox_Schemes.Items.Count];
string[] Tables = new string[CheckedListBox_Tables.CheckedItems.Count];
//Цикл по схемам
int i = 0;
foreach (Object Схема in ListBox_Schemes.Items)
{
Schemas[i] = (string)Схема;
i++;
}
i = 0;
//Цикл по таблицам
foreach (Object Таблица in CheckedListBox_Tables.CheckedItems)
{
Tables[i] = (string)Таблица;
i++;
}
//Запуск функции "export" в созданном модуле VBA
oAccess.Run("export", Schemas, Tables, Server.Text, Port.Text, Uid.Text, Pwd.Text);
//Закрытие модуля VBA
oAccess.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString(), Microsoft.Office.Interop.Access.AcCloseSave.acSaveYes);
//Удаление модуля VBA
oAccess.DoCmd.DeleteObject(Microsoft.Office.Interop.Access.AcObjectType.acModule, module.Name.ToString());
//Выход из приложения ACCESS без сохранения изменений
oAccess.DoCmd.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
//Освободить объект oAccess
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
oAccess = null;
}
public void CopyDataTable(string target,string newName)
{
Microsoft.Office.Interop.Access.Application app =
new Microsoft.Office.Interop.Access.Application();
string path = AppDomain.CurrentDomain.BaseDirectory;
app.OpenCurrentDatabase(path+"optimization_db.accdb",false,"");
app.DoCmd.CopyObject(
Type.Missing,
target +newName,
Microsoft.Office.Interop.Access.AcObjectType.acTable,
target+"data");
app.CloseCurrentDatabase();
app.Quit();
}
private void ImportDataToAccess(string P_str_Excel, string P_str_SheetName)
{
object missing = System.Reflection.Missing.Value;//声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]);//获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name;//获取工作表名称
excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
workbook.Save();//保存工作簿
CloseProcess("EXCEL");//关闭所有Excel进程
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_str_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
catch
{
MessageBox.Show("Access数据库中已经存在" + P_str_SheetName + "表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
}
public static extern int AccessibleObjectFromWindow(
IntPtr hwnd,
uint dwObjectID,
ref Guid riid,
out Microsoft.Office.Interop.Access.Application applicationObject
);
public void runApp(string databaseName, string function)
{
VBA.VBComponent f = null;
VBA.VBComponent f2 = null;
Microsoft.Office.Interop.Access.Application app = null;
object Missing = System.Reflection.Missing.Value;
Object tempObject = null;
try
{
app = new Microsoft.Office.Interop.Access.Application();
app.Visible = true;
app.OpenCurrentDatabase(databaseName, false, "");
//Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database
//Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
//the temp objects won't interfere with other objects each other (if there are multiples).
string tempGuid = Guid.NewGuid().ToString("N");
f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);
//We must set the Instancing to 2-PublicNotCreatable
f.Properties.Item("Instancing").Value = 2;
f.Name = "TEMP_CLASS_" + tempGuid;
f.CodeModule.AddFromString(
"Public Sub TempClassCall()\r\n" +
" Call " + function + "\r\n" +
"End Sub\r\n");
//Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
f2.Name = "TEMP_MODULE";
f2.CodeModule.AddFromString(string.Format(
"Public Function instantiateTempClass_{0}() As Object\r\n" +
" Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
"End Function"
, tempGuid));
//Step 3: Get a reference to a new TEMP_CLASS_* object
tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);
//Step 4: Call the method on the TEMP_CLASS_* object.
Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
}
catch (COMException e)
{
MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
}
catch (Exception e)
{
MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
}
finally
{
//Clean up
if (f != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f);
Marshal.FinalReleaseComObject(f);
}
if (f2 != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f2);
Marshal.FinalReleaseComObject(f2);
}
if (tempObject != null)
{
Marshal.FinalReleaseComObject(tempObject);
}
if (app != null)
{
//Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
Marshal.FinalReleaseComObject(app);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
