Re: How to get an ADO control in my Excel 2002?

Works with me even on Excel 2000.
Did you run it in a normal workbook in a normal code module?

RBS

"NiceGuy" <NiceGuy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BD7DA1B0-E152-4E7B-937C-4072CFF37CCB@xxxxxxxxxxxxxxxx 
I ran this sub, it gave me this message:
      run time error '1004'
      Method 'VBProject' of object '_workbook' failed

What does it mean?

Thanks



"RB Smissaert" wrote:

I don't think there is an ADO control but you will need a reference to the
Microsoft ActiveX Data Objects Library and this code will set the reference
to that library:

Sub AddADO()

    Dim r

For Each r In ThisWorkbook.VBProject.References
If r.GUID = "{00000205-0000-0010-8000-00AA006D2EA4}" And r.Major = 2
Then
Exit Sub
End If
Next

    On Error GoTo NOTFOUND

    'although usually the ADO version will be higher, doing Minor:=0 will
install
    'the higher version if available. On the other hand when you specify
Minor:=5
    'and only a lower version is available, this can't be installed

'----------------------------------------------------------------------------
    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:="{00000205-0000-0010-8000-00AA006D2EA4}", _
                                                  Major:=2, Minor:=0
    Exit Sub

NOTFOUND:
    On Error GoTo 0

End Sub


Once you have this reference you can set your connection and work with the
database.
Plenty of examples on the net how to do this.


RBS


"NiceGuy" <NiceGuy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D66FF1C1-93E1-4662-8AF5-54EB19D57D07@xxxxxxxxxxxxxxxx
> I'm developing a small program using Excel 2002 and VBA with a > connection
> to
> Access. However there is no ADO control in my excel. Where do I get one
> and
> how to register it?



.

Relevant Pages

  • RE: Loop through Excel Files and Tables
    ... I'm having some issues using the ForEach container to process multiple excel ... and trying to reuse the connection strings they built up ... The connection string format is not valid. ... Warning: 0x80019002 at Foreach Loop Container: The Execution method ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL incorporation with Excel
    ... Excel 2002 VBA by Bullen, Green, Bovey and Rosenberg has a fairly good ... Set a reference to ADO 2.7 in your vb project. ... connection string to find the correct way to connect. ... Our accounting system uses an SQL ...
    (microsoft.public.excel.programming)
  • RE: connect to Access 2007
    ... In Excel, start recording a macro. ... Make a manual connection to the database (I use Excel 2003, ...
    (microsoft.public.excel.programming)
  • How to connect Xel with oracle
    ... > You can connect to Access from Excel using ADO - it works very nicely ... I leave the connection open until the Excel ... > Private cn As ADODB.Connection ... > 'Check we're connected to the database ...
    (microsoft.public.excel.programming)
  • Re: Some of my merged data is missing
    ... Perhaps using a different connection method will resolve the issue. ... You didn't indicate which version of Word and Excel you are using but ... that some of the reports cut off mid way through the report ... I have tried changing the format to text and back again, ...
    (microsoft.public.office.misc)