Excel.Application User-define Error

  • Thread starter Thread starter Michael Kintner
  • Start date Start date
M

Michael Kintner

In trying to solve a Access to Excel data linking problem from Microsoft's
site http://support.microsoft.com/kb/904953

I am trying to run the example and it dies on
Dim xlApp As Excel.ApplicationThe error message I get is "Compile error:
User-defined type not defined." Is there something I need to add like a
reference in order to get this to work?Mike
 
You need to add a reference to Excel (through Tools | References while in
the VB Editor).

Personally, I prefer using Late Binding, since setting a reference then
limits your application to only those users with the same version of Excel:

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")
 
In trying to solve a Access to Excel data linking problem from Microsoft's
sitehttp://support.microsoft.com/kb/904953

I am trying to run the example and it dies on
Dim xlApp As Excel.ApplicationThe error message I get is "Compile error:
User-defined type not defined." Is there something I need to add like a
reference in order to get this to work?Mike
Click to expand...

As Douglas said, your guess about adding a reference is correct. The
conversion software within Access -- when run on the target machine --
will take a reference to an earlier version of Excel and convert it
into a reference to the current version of Excel automatically. So if
you need to use Early Binding you can continue to do so without too
much inconvenience. As Douglas said, Late Binding does adapt to
differing versions of Excel, but it does so at the expense of
Intellisense. Some developers code with Early Binding early in the
development process and then switch to Late Binding later :-). As you
have discovered, Automation is one way to get around the linking
problem. In:

http://groups.google.com/group/microsoft.public.access/msg/212d13bc35be7d3c

Jamie Collins posted an interesting way to get around the linking
problem.

James A. Fortune
(e-mail address removed)
 
I've always done my own automation, so Microsoft removing the capability
didn't impact me, so I've never really looked at that code.

Looking at it just now, it looks reasonable. What problems have you been
having? I assume you remembered to replace <AccessPath>, <ExcelPath>,
<MyQuery> and <Worksheets> with the approriate values.
 
When I run Microsoft's example I get an error Undefined Object on the Dim
xlApp As Excel.Application

So I then tried putting in your references and running into all sorts of
issues. Below is what I tried.

Any help would be greatly appreciated.

Thank you in advance,
Michael Kintner


Public Sub WorkArounds()
'On Error GoTo Leave

Dim strSQL As String
Dim SQL As String
Dim XLSFile As String
Dim XLSSheet As String
Dim Db As ADODB.Connection

Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source=H:\MySystem\Desktop\Projects\Access-Excel\AccessExcel.mdb"
'*Note: In Office Access 2007, use the following line of code:
'Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=<AccessPath>"
XLSFile = "H:\MySystem\Desktop\Projects\Access-Excel\BuildXLSData.xls"
XLSSheet = "Prep_Viscosity"
SQL = "Select * from TestUpdate"
Call CopyRecordSetToXL(XLSFile, XLSSheet, SQL, Db)
Db.Close
MsgBox "Access has successfully exported the data to excel file.",
vbInformation, "Export Successful."
Exit Sub
Leave:
MsgBox Err.Description, vbCritical, "Error"
Exit Sub
End Sub

Private Sub CopyRecordSetToXL(stFile As String, stSheet As String, SQL As
String, con As ADODB.Connection)
Dim rs As New ADODB.Recordset
Dim x
Dim i As Integer, y As Integer
Dim xlApp As Object
'Set xlApp = CreateObject("Excel.Application")
Dim xlwbBook As Object
'Set xlwbBook = CreateObject("Excel.Workbook")
Dim xlwbAddin As Object
'Set xlwbAddin = CreateObject("Excel.Workbook")
Dim xlwsSheet As Object
'Set xlwsSheet = CreateObject("Excel.Worksheet")
Dim rnData As Object
'Set rnData = CreateObject("Excel.Range")

'Dim xlApp As Excel.Application
'Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
'Dim xlwsSheet As Excel.Worksheet

'Dim rnData As Excel.Range
Dim stAddin As String
'Dim rng As Range
Dim rng As Object
'Set rng = CreateObject("Range")

'Instantiate a new session with the COM-Object Excel.exe.
'Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(stFile)
Set xlwsSheet = xlwbBook.Worksheets(stSheet)
xlwsSheet.Activate
'Getting the first cell to input the data.
xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(1, -y).Select
x = xlwsSheet.Application.ActiveCell.Cells.Address
'Opening the recordset based on the SQL query and saving the data in the
Excel worksheet.
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, con
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet.Range(x)
xlwsSheet.Range(x).CopyFromRecordset rs
End If
xlwbBook.Close True
xlApp.Quit
Set xlwsSheet = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing

End Sub
 
Issues are below. Everything works until Dim rng as Object and Set rng =
CreateObject("Range"), object not found. It looks like the on the fly
declare of Excel.Application does not pass thru. Is there anywhere I can go
to see such an example working?
 
Michael Kintner said:
Issues are below. Everything works until Dim rng as Object and Set
rng = CreateObject("Range"), object not found. It looks like the on
the fly declare of Excel.Application does not pass thru. Is there
anywhere I can go to see such an example working?
Click to expand...

It should probably be

Set rng = CreateObject("Excel.Range")

not

Set rng = CreateObject("Range")

But....

Why do you instantiate (or try to instantiate) them more than once?
There is no need to do all these instantiations "at the top", when
you're doing them again later on, i e, remove all those createobject
thingies at the top, only keep the declarations.

Dim xlApp As Object
Dim xlwbBook As Object
Dim xlwsSheet As Object
Dim rng As Object

Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(stFile)
Set xlwsSheet = xlwbBook.Worksheets(stSheet)
....
Set rng = xlwsSheet.Range(x)

(I don't see you using the range object, though, so you could probably
just skip it, too)
 
Back
Top