访问2013进出excel的导入和导出 [英] access 2013 importing and exporting to and from excel

问题描述

我将访问2003数据库转换为访问2013.在从Excel导入时，我收到一条错误消息，表明Excel和Access必须在同一版本中。

我之后将数据库的副本转换为.accdb，我的excel导出不再有效。程序将完成动作，但只导出标题。取回.mdb版本可恢复功能。

我是从头开始创建一个数据库，从旧数据库导入所有数据库，但我现在将其保留为mdb。

编译数据库后，我遇到意外错误。我正在复制这个函数的开头，这不是我的，但可以在支持网站上找到。它在Access 2003中运行良好。帮助，我不得不承认我不太了解VBA而且我迷失了为什么这个
不再有效。

现在它已经停止了在'Excel对象变量

   "Dim appExcel As Excel.Application  -------------"

Public Function ProcessFileImport（ByVal sFile As String，ByVal sTable As String）As String

   On Error GoTo ProcessFileImport_Error

  

   'Excel对象变量

   Dim appExcel As Excel.Application

   Dim wbk As Excel.Workbook

   Dim wks As Excel.Worksheet

  

   '访问对象变量

   Dim dbs As DAO.Database

   Dim rstRead As DAO.Recordset

   Dim rstWrite As DAO.Recordset

   Dim fld As DAO.Field

  

   '声明的变量

   Dim byWW As Byte

   Dim bytMaxPages As Byte

   Dim intStartRow As Integer

   Dim strData As String

   Dim intMaxRow As Integer

   Dim strSQL As String

   Dim strMsg As String

   Dim intLastCol As Integer

   Dim intRow As Integer

   Dim intRec As Integer

   Dim strCurrFld As String

   Dim intCol As Integer

   Dim intLen As Integer

   Dim varValue As Variant

   Dim lngErrs As Long

   Const cPassword As String =" xxx999"

解决方案

< blockquote>

使用以下函数将电子表格传输到Access表

docmd.Transferspreadsheet acImport，acSpreadsheetTypeExcel12，TargetTableName，SourceFilePath，[HasFieldNames]，[Range]

根据Excel版本使用以下常量之一

acSpreadsheetTypeExcel12 = 2007-2010

acSpreadsheetTypeExcel9 = 2003

I converted an access 2003 database to access 2013. On an import from Excel I got an error message that Excel and Access must be in the same version.

After I converted a copy of the database to .accdb my excel export no longer worked. The program would go through the motion, but exported only the headers. Taking back the .mdb version restored the functionality.

I am creating a database from scratch by importing all from the old database, but I am at this point leaving it as an mdb.

Upon compiling the database I get unexpected errors. I am copying below the start of this function, which is not mine but was available on an support site. It worked well in Access 2003. Help, I have to admit I don't know VBA well and I am lost on why this no longer works.

Now it already stops at ' Excel object variables
   "Dim appExcel As Excel.Application  -------------"

Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
   On Error GoTo ProcessFileImport_Error
  
   ' Excel object variables
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
  
   ' Access object variables
   Dim dbs As DAO.Database
   Dim rstRead As DAO.Recordset
   Dim rstWrite As DAO.Recordset
   Dim fld As DAO.Field
  
   ' Declared variables
   Dim bytWks As Byte
   Dim bytMaxPages As Byte
   Dim intStartRow As Integer
   Dim strData As String
   Dim intMaxRow As Integer
   Dim strSQL As String
   Dim strMsg As String
   Dim intLastCol As Integer
   Dim intRow As Integer
   Dim intRec As Integer
   Dim strCurrFld As String
   Dim intCol As Integer
   Dim intLen As Integer
   Dim varValue As Variant
   Dim lngErrs As Long

   Const cPassword As String = "xxx999"

解决方案

Use the following function to transfer a spreadsheet to an Access table

docmd.Transferspreadsheet acImport, acSpreadsheetTypeExcel12, TargetTableName, SourceFilePath, [HasFieldNames], [Range]

use either of the following constants depending on Excel version

acSpreadsheetTypeExcel12 = 2007-2010

acSpreadsheetTypeExcel9 = 2003

这篇关于访问2013进出excel的导入和导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！