Импорт данных из Access в Excel с использованием Access VBA. Vba импорт данных из access в access
vba - Импорт/экспорт отношений в MS Access
Основываясь на ответе @Patrick Cuff, я создал пару скриптов: один экспортирует в xml, другой читает этот xml и анализирует его в базе данных
'supply the Access Application object into this function and path to file to which the output should be written Function ExportRelationships(oApplication, sExportpath) Dim relDoc, myObj Set relDoc = CreateObject("Microsoft.XMLDOM") relDoc.appendChild relDoc.createElement("Relations") 'create root xml element 'loop though all the relations For Each myObj In oApplication.CurrentDb.Relations If Not Left(myObj.Name, 4) = "MSys" Then 'exclude system relations Dim relName, relAttrib, relTable, relFoTable, fld relDoc.childNodes(0).appendChild relDoc.createElement("Relation") Set relName = relDoc.createElement("Name") relName.Text = myObj.Name relDoc.childNodes(0).lastChild.appendChild relName Set relAttrib = relDoc.createElement("Attributes") relAttrib.Text = myObj.Attributes relDoc.childNodes(0).lastChild.appendChild relAttrib Set relTable = relDoc.createElement("Table") relTable.Text = myObj.Table relDoc.childNodes(0).lastChild.appendChild relTable Set relFoTable = relDoc.createElement("ForeignTable") relFoTable.Text = myObj.ForeignTable relDoc.childNodes(0).lastChild.appendChild relFoTable 'in case the relationship works with more fields For Each fld In myObj.Fields Dim lf, ff relDoc.childNodes(0).lastChild.appendChild relDoc.createElement("Field") Set lf = relDoc.createElement("Name") lf.Text = fld.Name relDoc.childNodes(0).lastChild.lastChild.appendChild lf Set ff = relDoc.createElement("ForeignName") ff.Text = fld.ForeignName relDoc.childNodes(0).lastChild.lastChild.appendChild ff Next End If Next relDoc.insertBefore relDoc.createProcessingInstruction("xml","version='1.0'"), relDoc.childNodes(0) relDoc.Save sExportpath End Function 'supply the Access Application object into this function and path to file from which the input should be read Function ImportRelationships(oApplication, sImportpath) Dim relDoc, myObj Set relDoc = CreateObject("Microsoft.XMLDOM") relDoc.Load(sImportpath) Dim xmlRel, xmlField, accessRel, relTable, relName, relFTable, relAttr, i 'loop through every Relation node inside .xml file For Each xmlRel in relDoc.selectNodes("/Relations/Relation") relName = xmlRel.selectSingleNode("Name").Text relTable = xmlRel.selectSingleNode("Table").Text relFTable = xmlRel.selectSingleNode("ForeignTable").Text relAttr = xmlRel.selectSingleNode("Attributes").Text 'remove any possible conflicting relations or indexes On Error Resume next oApplication.CurrentDb.Relations.Delete (relName) oApplication.CurrentDb.TableDefs(relTable).Indexes.Delete(relName) oApplication.CurrentDb.TableDefs(relFTable).Indexes.Delete(relName) On Error Goto 0 'create the relationship object Set accessRel = oApplication.CurrentDb.CreateRelation(relName, relTable, relFTable, relAttr) 'in case the relationship works with more fields For Each xmlField In xmlRel.selectNodes("Field") accessRel.Fields.Append accessRel.CreateField(xmlField.selectSingleNode("Name").Text) accessRel.Fields(xmlField.selectSingleNode("Name").Text).ForeignName = xmlField.selectSingleNode("ForeignName").Text Next 'and finally append the newly created relationship to the database oApplication.CurrentDb.Relations.Append accessRel Next End Function
Примечания
Просто чтобы прояснить, что ожидается, будет передано в параметр oApplication
Set oApplication = CreateObject("Access.Application") oApplication.NewCurrentDatabase path 'new database oApplication.OpenCurrentDatabase path 'existing databaseВ случае, если вы используете это из VBA вместо VBScript, вы можете удалить параметр и только обычный объект приложения всюду в коде, где используется oApplication.
Я начал работать над этим кодом, так как мне нужно было реализовать элемент управления версиями в очень сложном проекте MsAccess. Этот пост заставил меня двигаться, есть также хорошие советы о том, как экспортировать/импортировать другие части проекта MsAccess.
qaru.site
vba - Использование Excel в качестве интерфейса для доступа к базе данных Access (с VBA)
Я уверен, что вы получите тонну ответов "не делай этого", и я должен сказать, что есть веская причина. Это не идеальное решение....
Сказав это, я пошел по этой дороге (и тому подобным) раньше, главным образом потому, что задание указывало на это как на жесткое требование, и я не мог говорить об этом.
Вот несколько вещей, которые следует учитывать при этом:
Насколько легко установить ссылку на Access из Excel с помощью ADO/DAO? Является ли это довольно ограниченным с точки зрения функциональности или я могу стать творческим?
Это довольно просто. Вы более ограничены, чем если бы делали что-то с помощью других инструментов, поскольку формы VBA и Excel немного более ограничены, чем большинство полных языков программирования, но нет ничего, что будет показательным стопором. Он работает - иногда он немного уродлив, но он работает. В моей последней компании мне часто приходилось это делать - и изредка вытаскивали данные из Access и Oracle через VBA в Excel.
Я могу заплатить штраф за производительность (vs. использование форм в Access как пользовательский интерфейс)?
Мой опыт в том, что определенно есть перфекционист. штраф в этом. Я никогда не заботился (в моем случае, дела были достаточно маленькими, чтобы это было разумно), но переход Excel ↔ Access намного медленнее, чем просто работа в Access. Часть этого зависит от того, что вы хотите сделать.
В моем случае то, что казалось абсолютным самым медленным (и самым болезненным), пыталось заполнить электронные таблицы Excel на основе данных Access. Это было не весело, и часто было очень медленно. Если вам нужно спуститься по этой дороге, обязательно сделайте все с помощью скрытого/невидимого Excel, или перерисовка абсолютно вас убьет.
Предполагая, что база данных всегда будет обновляться с использованием команд ADO/DAO из Excel VBA, означает ли это, что я могу иметь несколько пользователей Excel, использующих одну единственную базу данных Access, и не запускать какие-либо проблемы с concurrency и т.д.? p >
Вы в значительной степени используете Excel в качестве клиента - так же, как вы бы использовали приложение WinForms или любой другой инструмент. Клиенты ADO/DAO для Access довольно хороши, поэтому вы, вероятно, не столкнетесь с проблемами concurrency.
Таким образом, Access не масштабируется хорошо. Это отлично работает, если у вас есть 2 или 3 (или даже 10) пользователей. Если у вас будет 100, вы, вероятно, столкнетесь с проблемами. Кроме того, я, как правило, обнаружил, что Access нуждается в регулярном обслуживании, чтобы не иметь проблем с коррупцией. Регулярное резервное копирование БД доступа является обязательным. По моему опыту, уплотнение базы данных доступа на регулярной основе поможет предотвратить повреждение базы данных.
Любые другие вещи, о которых я должен знать?
Ты делаешь это с трудом. Использование Excel для доступа к Access будет намного больше, чем просто использовать Access напрямую.
Я бы порекомендовал посмотреть в API доступа VBA - большая часть из них такая же, как у Excel, поэтому у вас будет небольшая кривая обучения. Части, которые отличаются друг от друга, делают это проще. У вас также будут все преимущества отчетов Access и Forms, которые намного ориентированы на данные, чем те, которые есть в Excel. Отчетность может быть отличной для таких вещей, и наличие макросов и отчетов облегчит жизнь в долгосрочной перспективе. Если пользователь будет использовать формы для управления всем, делать формы в Access будет очень, очень похоже на их выполнение в Excel, и будет выглядеть почти идентично, но сделает все быстрее и плавнее.
qaru.site
Импорт данных из Access в Excel с использованием Access VBA MS Excel онлайн
Я хотел бы попросить вас о помощи с кодом доступа VBA, который будет импортировать все данные из 1 указанной таблицы запросов из базы данных Access (в настоящее время открытой базы данных) в MS Excel (файл, который может быть выбран пользователем ).
У меня в настоящее время есть этот фрагмент кода, но я получаю сообщение об ошибке:
«Ошибка времени выполнения» -2147023170 (800706be):
Ошибка автоматизации Ошибка удаленного вызова процедуры. "
Кто-нибудь из вас знает, как исправить соединение?
Option Explicit Option Compare Database Public Sub CopyRstToExcel_test() 'On Error GoTo CopyRstToExcel_Err Dim sPath As String Dim fd As FileDialog Dim oExcel As Object Dim oExcelWrkBk As Object Dim oExcelWrSht As Object Dim dbs 'Added Dim qdfName As String Dim fRecords As Boolean Dim rst As dao.Recordset Dim iCols As Integer '------------------------------------------------------------------------------------------------------------------------------------------------------------------- ' Select the file and identify the path leading to the file '------------------------------------------------------------------------------------------------------------------------------------------------------------------- 'Define database you want to work with Set dbs = CurrentDb 'Select the Excel file you want to work with Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Define the path If fd.Show = -1 Then sPath = fd.SelectedItems(1) End If MsgBox sPath '------------------------------------------------------------------------------------------------------------------------------------------------------------------- ' Defining names of variables '------------------------------------------------------------------------------------------------------------------------------------------------------------------- 'Defining variables (queries/tables) qdfName = "Query_1" '------------------------------------------------------------------------------------------------ 'Copying the data from Access into the new Excel '------------------------------------------------------------------------------------------------ Set rst = CurrentDb.OpenRecordset(qdfName, dbOpenSnapshot) fRecords = False If rst.EOF = False Then fRecords = True Set oExcel = CreateObject("Excel.Application") Set oExcelWrkBk = GetObject(sPath) oExcel.Visible = True oExcel.ScreenUpdating = False Set oExcelWrSht = oExcelWrkBk.Sheets(1) For iCols = 0 To rst.Fields.Count - 1 oExcelWrSht.Cells(9, iCols + 2).Value = rst.Fields(iCols).Name Next oExcelWrSht.Range(oExcelWrSht.Cells(9, 2), _ oExcelWrSht.Cells(9, rst.Fields.Count)).Font.Bold = True oExcelWrSht.Range("B10").CopyFromRecordset rst oExcelWrSht.Range(oExcelWrSht.Cells(9, 2), _ oExcelWrSht.Cells(rst.RecordCount + 9, rst.Fields.Count)).Columns.AutoFit oExcelWrSht.Range("A1").Select End If '------------------------------------------------------------------------------------------------ CopyRstToExcel_Done: On Error Resume Next If fRecords = True Then oExcel.Visible = True oExcel.ScreenUpdating = True End If Set oExcelWrSht = Nothing Set oExcelWrkBk = Nothing Set oExcel = Nothing Set rst = Nothing ''Error message: 'CopyRstToExcel_Err: ' MsgBox Err & ": " & Error, vbExclamation ' Resume CopyRstToExcel_Done ' Resume '------------------------------------------------------------------------------------------------ End SubСпасибо за помощь!
Solutions Collecting From Web of "Импорт данных из Access в Excel с использованием Access VBA"
Попробуйте заменить
Set oExcelWrkBk = GetObject(sPath)от
Set oExcelWrkBk = oExcel.Workbooks.Open(sPath)Также я бы рекомендовал заменить
Set rst = CurrentDb.OpenRecordset(qdfName, dbOpenSnapshot)от
Set rst = dbs.OpenRecordset(qdfName, dbOpenSnapshot)Открыть заданный рабочий лист:
Set oExcelWrSht = oExcelWrkBk.Sheets("MyWorksheetName")excel.fliplinux.com
