0

I am attempting to export a worksheet from Excel 2007 to Access 2007 on Windows 7 including creating a new table. I have tried the following VBA in Excel but it states "Compile Error User-defined types not defined" and then highlights the first variable declaration for the Access object. I have the Microsoft ActiveX Data Objects 6.1 Library referenced.

What is wrong?

Dim acc As New Access.Application
Dim ws As Worksheet
Dim ThisWorkbook As Workbook
Dim a As Long
Dim b As Long

Set ThisWorkbook = ActiveWorkbook
Set ws = ThisWorkbook.Sheets("CompleteNoFormat3")
a = ws.UsedRange.Rows.Count
b = ws.UsedRange.Columns.Count

acc.OpenCurrentDatabase "CustDB.mdb"
  acc.DoCmd.TransferSpreadsheet _
   TransferType:=acImport, _
   SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
   TableName:="CustExportTable", _
   Filename:=Application.ActiveWorkbook.FullName, _
   HasFieldNames:=True, _
   Range:="BAPCompletNoFormat$A1:" & a & B 
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
pnuts
  • 58,317
  • 11
  • 87
  • 139
mjhenry
  • 53
  • 1
  • 14
  • You need to add a reference to the Access Application object library. ADO and Access aren't the same thing. – Tim Williams Sep 11 '15 at 18:59
  • @TimWilliams Thanks for your response. Late reply because I had to take some annual leave. This resolved the issue but now it is not recognizing the "CompleteNoFormat3" worksheet and instead transferring the first worksheet – mjhenry Sep 15 '15 at 14:16

1 Answers1

2

Consider using late binding which is even sometimes recommended if distributing applications to multiple users who may have different library version types installed.

Late binding avoids needs to specifically choose references as CPU searches for appropriate type and for applications that do not support the "New" keyword:

Dim acc As Object

Set acc = CreateObject("Access.Application")
Parfait
  • 104,375
  • 17
  • 94
  • 125