找不到可安装的ISAM Excel 2010 [英] Could Not Find Installable ISAM Excel 2010

问题描述

我在Excel 2010中具有此子项，该子项应从Access 2010复制到表中.当我尝试运行代码时，它给我一个找不到可安装的ISAM"的信息.当我调试时，它突出显示objDB.Execute.

I have this sub in Excel 2010 that's supposed to copy in a table from Access 2010. When I try to run the code, it gives me a 'Could not find installable ISAM'. When I debug, it highlights objDB.Execute.

Private Sub btnGetData_Click()

Unload ParameterMenu
formWait.Show

'Save workbook
ActiveWorkbook.Save

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database

strExcelFile = "X:\Form.xlsm"
strWorksheet = "RawDates"
strDB = "X:\Tables.accdb"
strTable = "Dates"

Set objDB = OpenDatabase(strDB)

objDB.Execute _ 'Error occurs here.
  "SELECT * INTO [Excel 14.0;DATABASE=" & strExcelFile & _
   "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"

objDB.Close
Set objDB = Nothing

Unload formWait
FinishDialog.Show

End Sub

我对VBA并没有过多的经验，所以将不胜感激.

I'm not overly experienced with VBA, so any assistance would be greatly appreciated.

推荐答案

正确的格式是"Excel 12.0宏"而不是"Excel 14.0":

The correct format is 'Excel 12.0 Macro' rather than 'Excel 14.0':

objDB.Execute _ 'Error occurs here.
  "SELECT * INTO [Excel 12.0 Macro;DATABASE=" & strExcelFile & _
   "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"

也许尝试使用ADO:

Private Sub btnGetData_Click()

Unload ParameterMenu
formWait.Show

'Save workbook
ActiveWorkbook.Save

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Object
Dim rs As Object

strExcelFile = "X:\Form.xlsm"
strWorksheet = "RawDates"
strDB = "X:\Tables.accdb"
strTable = "Dates"

Set objDB = CreateObject("ADODB.Connection")
With objDB
    .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strDB & ";"
      .Open
   End With

Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM " & "[" & strTable & "]", objDB, 3, 1, 1

If Not rs.EOF Then _
ThisWorkbook.Worksheets(strWorksheet).Cells(Rows.Count, "A").End(xlUp).Offset(1).CopyFromRecordset rs

rs.Close
objDB.Close
Set objDB = Nothing

Unload formWait
FinishDialog.Show

End Sub

这篇关于找不到可安装的ISAM Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！