Need to add library

[pbassett]

I have a Report that will create lots of tabs in Excel with names taken from a query. I start my Macro with the statement "Dim xl As Excel.Application" as I've done before, but I get the message "Compile Error: user-defined type not defined".

I remember having to add 2-3 libraries to get the previous macro to work, and then making sure these were at the top of a list, but I have completely forgotten where I located these libraries from within Access

Any help would be appreciated - I think one library had "ADO" in it and another was rev 10.0 or something.

Pete

 

[bat17]

Open any module, then Tools>References... then select the refernces you need.
probably the Microsoft Excel Object Libary and either the Microsoft DAO Object Libary or Microsoft ADO Object Libary.

Version numbers will vary depending on your instalation.

Peter

 

[Norie]

Pete

Peter is right.

The library you need for your specific proplem appears to be Microsoft Excel Object Library , V8.0 on my machine, Access 97.

 

[pbassett]

Thanks, but in my Access 2002 Form I don't have Tools->References. maybe Tools->ActiveX Controls or Tools->Add-Ins, but these don't show the list of Libraries you're listing.

 

[bat17]

you need to be in the code pane not form design. Just open any module.

peter

 

[pbassett]

Thanks! I need to write down those steps for future reference.

While I have the chance, I have one more little problem. Every other time I run the Form, I get the error "Method 'Sheets' of object '_Global' failed. It's as if I didn't correctly close the module the previous time, since it goes away if I click on the VB "Reset" blue square button.

If you don't mind checking my code and letting me know what I need to do - (this app queries a table of numbers and creates sheets with that number ("12488", "10032", etc.)

====
Dim xl As Excel.Application

Set xl = New Excel.Application
xl.Visible = True
xl.Workbooks.Add

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("qryMyQuery")

rst.MoveFirst
Do
Sheets.Add.Name = Str(rst(0))
If Not rst.EOF Then rst.MoveNext
Loop Until rst.EOF

rst.Close
Set rst = Nothing
Set x1 = Nothing
====

 

[Norie]

You need to explicitly refer to the Excel object.

e.g.

Sheets.Add.Name

should be something like this

xl.Workbooks(1).Sheets.Add.Name

It might be a good idea to introduce another object for the workbook.

e.g.

Set wb = xl.Workbooks.Add

I'm not sure what type it would be though.

 

[bat17]

If you copied you code to here then you have an error.
Set x1 = Nothing should be Set xl = Nothing

if you use Option Explicit at the top of your code page it will prevent this sort of error from happening.

to explicty refer you can do something like

Dim xl As Excel.Application
Dim wbk As Workbook
Dim rst As Recordset

Set xl = New Excel.Application
Set wbk = xl.Workbooks.Add
xl.Visible = True

Set rst = CurrentDb.OpenRecordset("qryMyQuery")
rst.MoveFirst
Do
   wbk.Sheets.Add.Name = str(rst(0))
   If Not rst.EOF Then rst.MoveNext
Loop Until rst.EOF

rst.Close
Set rst = Nothing
Set wbk = Nothing
Set xl = Nothing
End Sub

HTH

Peter

 

[pbassett]

Thanks! My poor coding had worked in a different routine, but tripped me up when I modified it to this app.

Pete