VBA-在未安装Access的情况下将数据从Excel上传到Access [英] VBA - Upload data from Excel to Access without Access installed

问题描述

我对VBA还是很陌生，但是我正在尝试将Excel工作簿中的数据从未安装Access的计算机上载到Access数据库表中.我已经在网上搜索了一个解决方案，但还没有找到可以使用我的代码的东西.

I am fairly new to VBA but am trying to upload data from an Excel Workbook to an Access database table from a computer that does not have Access installed. I have searched for a solution online but haven't found anything yet that I can get to work with my code.

我得到的错误代码是... 429无法创建Activex组件

The error code I am getting is...429 cannot create activex component

我在Excel工作簿中设置了一些VBA代码，该代码调用Access中的Sub [可在安装了Access的计算机上运行]，但是我不知道如果计算机没有安装正确的代码，应该是什么Access已安装.

I have some VBA code set up in the Excel workbook which calls a Sub in Access [which works on a machine which has Access installed] but I don't know what the correct code should be if the machine doesn't have Access installed.

Sub Upload_SiteObsData_Excel_To_Access(Database_Path)

Database_Path = "\\Path\db1.mdb"

Dim acApp As Object
Dim db As Object

Set acApp = CreateObject("Access.Application")

acApp.OpenCurrentDatabase ("\\Path\db1.mdb")

Set db = acApp
acApp.Run "Upload_SiteObsData_to_Access"
acApp.Quit
Set acApp = Nothing

End Sub

Access中的过程如下:

The procedure in Access is as follows:

Option Compare Database
Option Explicit

Dim Excel_Path As String
Dim Excel_Range As String
Dim UserNameOffice As String

Dim Excel_File_TechForm As String
Sub SetUp_Variables()

UserNameOffice = CreateObject("wscript.network").UserName

Excel_Path = "C:\Documents and Settings\" & UserNameOffice & "\Desktop\"
Excel_Range = "MyData"

Excel_File_TechForm = "SiteObsForm_v0.2.xls"

End Sub

Sub Upload_SiteObsData_to_Access()

SetUp_Variables
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_SiteObsData",   Excel_Path & Excel_File_TechForm, True

End Sub

我将非常感谢您的帮助.预先感谢

I would be extremely grateful for any help. Thanks in advance

推荐答案

我是第一次通过VBA将Excel连接到Access时执行此操作，并且我确信它可以使用

I've done this the first time I connected Excel to Access via VBA, and I know for sure it works using the code that Gord Thomson provided. Establishing an ADODB connection and executing an append query.

这篇关于VBA-在未安装Access的情况下将数据从Excel上传到Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！