Free Microsoft Excel 2013 Quick Reference

VBA: Automatically referencing the Object Libraries

Hi

Is there any way in which you can determine if an object library is referenced or not. If the library is not referenced then add it using VBA.

TIA

Graeme


Post your answer or comment

comments powered by Disqus
I have been few days searching in the net and this forum trying to solve an "Object library invalid" problem. But I could not find anyting that can fix my problem yet. I'll try to explain the details:

I have a macro for Excel (XLA) written in VBA. It does a connection to a MySQL database. This is the simplified code:

	VB:
	
 ADODB.Connection) 
    Set conn = New ADODB.Connection 
    conn.CursorLocation = adUseClient 
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _ 
    "SERVER=localhost;" & _ 
    "DATABASE=myDatabase;" & _ 
    "UID=myDBuser;PWD=myDBpwd; OPTION=3" 
    conn.Open 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The way I use this function to connect to MySQL is pretty simple:

	VB:
	
 doSomething() 
    Dim conn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
    Dim SQL As String, txtData As String 
     
    Call connectMysql(conn) 
     
    SQL = "SELECT field1 FROM myTable" 
    rs.Open SQL, conn 
    If Not rs.EOF Then 
        txtData = rs("field1") 
    End If 
    rs.Close 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything was fine, but I desired a better security, so the details from the connection string can not be easily read. Among many options, the simplest for me was to create a DLL with VB6 for this purpose (and more code I have been inserting later).

The migration was simple too. I created myDLL.dll with VB6, with a class module (mySQLc) with the code in the first attach. In the VBA part I put a reference to the DLL (that was copied to system32 folder and registered) and added small changes, see below:


	VB:
	
 doSomething() 
    Dim conn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
    Dim SQL As String, txtData As String 
    Dim mySQLConn As myDLL.mySQLc 'reference to a class in myDLL.dll
     
    Set mySQLConn = New myDLL.mySQLc 
    Call mySQLConn.connectMysql(conn) 
     
    SQL = "SELECT field1 FROM myTable" 
    rs.Open SQL, conn 
    If Not rs.EOF Then 
        txtData = rs("field1") 
    End If 
    rs.Close 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have been some months working with it, but suddenly it stopped working this VBA+DLL solution in all computers different from the one I develop and compile the DLL. Apparently nothing was changed in my DLL class for the ADODB connection, but it returns a compilation error in line Call mySQLConn.connectMysql(conn) with the message "Object library invalid...".
I tried everything I found in the net:
- remove the *.exd files from Application DataMicrosoftForms
- remove my class an create a new one with different name
- create a new DLL with a new name and adding one by one all my classes
- all kind of attempts with MS ADO library versions (.tlb)
What other options do I have? Please, send me some help because I am really desperate with a code that was working for months and suddenly stopped last week.

J. Carlos

Is there a way to reference a type library via code? I am working with a situation where I am making a lot of workbooks with sheets that are copied and I need the new workbooks to have the same abilities as the workbook that sheets are copied from, however it would be nice for it to automatically do it rather that having to go into each new workbook open the type library and select the appropriate libraries.
Thanks,

I developed an Excel macro but some people can't use it because they don't
have the object libraries needed in their Excel application. How do you add
object libraries to your VBA that are missing?

Hi everyone,

I need to run VBA codes in Excel that uses the Microsoft Word Object
Library. If the object lbrary is not loaded, the code won't work properly and
causes unfortunate problems. Because the code can be run on any given
machine, there's no way for me to know if the object library has been loaded
on that machine.

Is there a way, using VBA, to load this object library before the code runs,
or at least to know whether or not the library is loaded so that I can advise
the user if it's not?

Thanks for your help.

Hello,
I needed to figure out how to have a spreadsheet perform a lot of data
processing and then send the data to Sybase. It occurred to me to let
Microsot SQL Server DTS bring the data from Excel and then process the data.

I found a link on the web that details how to have VB execute a DTS package.

http://www.sqldts.com/default.aspx?208

I can create an Excel spreadsheet, create a button, attach the button to a
macro and the macro will call DTS. Works great.

Question:

Since I am a developer with SQL Server loaded as well as Microsoft Excel, I
only had to select References and check the "Microsoft DTSPackage Object
Library".

When I deploy this spreadsheet to the clients, they won't have SQL Server
loaded. They might have just the Client connectivity loaded. How do I
check for the instance of the object library? If it is not present, how do
I load it (from somewhere) to insure that Excel can call DTS? How do you
check for the existence and/or load it via code.

I apologize if I am in the wrong forum for this question.

Thanks.

Tony

I have a worksheet that uses MS Outlook Object Library to send one sheet of the workbook via Outlook email. I have code written to load MS Outlook 14.0 Object Library (MS Excel 2010) and it works great. I distribute this workbook to other users, some of which run MS Excel 2007. MS Excel 2007 uses MS Outlook 12.0 Object Library. I want to add code to my macro that will load the proper Object Library based on the version being used. I need to add an 'on error then' statement to my existing code. In other words if Object Library 14.0 is not found, then load Object Library 12.0. This will cover all my users versions. I will need to know where the Object Library 12.0 is located in the registry. See my current code that loads Object Library 14.0 below. Thanks in advance.

'  Load Microsoft Outlook 14.0 Object Library
     OLB = "{00062FFF-0000-0000-C000-000000000046}"
     Vmajor = 9
     Vminor = 4
     
       On Error Resume Next
         ThisWorkbook.VBProject.References.AddFromGuid OLB, Vmajor, Vminor
       If Err <> 0 And Err <> 32813 Then
          MsgBox "VBE Object library not loaded." & vbCrLf _
                & Err.Description
       End If


Hello
Can I install Microsoft Outlook 10.00 or 11.00 Object Library using VBA as a part of my code?

I have working code to send automatic email using MS Outlook which I need to distribute to my coworkers. But if they do not have the correct object library installed, they will not be able to use it.
Any help would be great.

John

I need to use the PrivateProfileString() function contained within the
'System' object to read/write an .ini file. I have referenced the relevant
Word object library to Excel but the function only works whilst Word is open.
I get the following (common) error:

Run-time Error 429; ActiveX component can't create object.

Can I actually read/write an .ini file using VBA in Excel? Would be greatful
for any help.

Hi,
i have a problem with succesfully starting my COM Addin, which i have builded with VB6.
I´ve referenced the Excel 11.0 Object Library but no objects can be used.
As long as i do nothing with an Excel Object, everything is fine; my form is being loaded, my menubutton is generated by the dll, but when i´m starting to let my Addin Insert formulas, copy sheets and so forth, my App raises an error telling me "Object required"
I definately have no idea how to fix this.
Is there anybody who can help me?
Kind regards,
Newmoon

Is there a VBA code to automatically change the Y Axis Title each time a cell value changes. This is for a chart with IS a worksheet (it is not a chart IN a worksheet...) (I don't know the object name of worksheet chart)...

Help greatly appreciated !

Thanks!

Is there a Microsoft Outlook Express Object Library? I have Outlook Express
6 installed but am unable to find the OLB file so that I can reference it
from my Excel VBA project. I've searched my drive for this file,
msoutl9.olb, or something that might look like it, and there's nothing here.
Any ideas ?

Hi,

How can i add a reference to the Word-library by VBA program. Because on forehand i don't no which office version is in use on the target computer.
When i send an update of my VBA script i find it user unfrendly to let de user selcet the right library item.

Thank you in advance

Hi,

I'm fairly new to vba, and am creating a general work log spreadsheet. I need to use the workday function and am doing so thought vba using a the following line of code:

	VB:
	
ActiveCell.Value = [atpvbaen.xls].Workday(c1, c2, BankHols) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with c1 being a reference to a date filed and c2 being a integer field, bankshols is a list of the dates to be ignored.

This code works fine on my machine as I've referenced atpvbaen.xla in the reference library. The problem I'm having is this will be used by multiple people on different machine and when used on a machine that doesn't reference atpvbaen.xla the code doesn't work.

Can somebody advise if there is either a way to reference atpvbaen.xla through vba.

Thanks

i'm using a user form and i have the microsoft forms 2.0 Object Library checked

i have sent to another user, and i can't compile, when i took at this vba tools reference, microsoft forms 2.0 Object Library checked is missing (and not available in the drop down)...?

???

thank you.

In order for one of my Excel macros to work, the user must have a reference set for a particular Object Library. I can programatically verify whether they have the reference set. However, I learned they must manually go to Macro Security and set it to "Trust access to Visual Basic Project" first, so it seems that I can't set it for them without some human intervention.

I wanted to keep the user from having to open the Visual Basic Editor in order to locate and set the reference. Right now, if they invoke the macro and don't have the reference set, they are automatically brought to a worksheet with directions to set the Macro Security to "Trust access to Visual Basic Project", and are then told to press a button which will programatically set the reference for them.

My questions are

#1) whether the user actually does have to open the VBE to manually set a reference -- or if there is another way

#2) if anyone has found a less 'clunky' way to address this issue than the method I am using.

Thanks,

Randy

Hello

I have two PCs:
1. Win2K + Office 2000
2. Win XP Pro + Office 2003

When opening a workbook on my XP machine, save any changes and re-open it in Office 2000, I find the following missing reference:
Microsoft Outlook 11.0 Object Library

The original refernce to Microsoft Outlook 9.0 Object Library is still apparant.

Can anybody please help me with the code to automatically remove this missing or any future references. Also does anybody know how to add refernces automatically to avoid manually to this?

Thanks a mill.

Otto

How can I programmatically reference an external object library, e.g Lotus Domino objects or VBScript Regex from within my vba program? I want to avoid Tools/References and ticking the boxes if possible.

I am getting a compile Error in Access.
I am guess I need a reference??

Currently I have checked.
"Visual Basic for applications"
"Microsoft Access 11.0 Object Library"

The Editor is highlighted section Code:
Any suggestions?

Code:
Public Function SKPIUPDATE()
Dim QPR
Dim lnk As Integer
Dim frm
Dim dwn
Dim Start
Dim fin
Dim drp1
Dim drp2
Dim drp3
Dim src1

' This macro will automatically open and download the TMMK-VEH daily scrap
'and store the file in the same directory

Set QPR = CreateObject("InternetExplorer.application")

    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
    
  TimeOut = Now + TimeValue("00:00:10")  '-- wait maximum of 10 seconds
   Do While QPR.Busy Or QPR.readyState  4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Time Out before Login"
         Exit Function
      End If
   Loop

   With QPR.Document.Forms("Login")
      .User.Value = "*****"
      .Password.Value = "*****"
      .submit
   End With

   TimeOut = Now + TimeValue("00:00:10")  '-- wait maximum of 10 seconds
   Do While QPR.Busy Or QPR.readyState  4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Time Out after Login"
         Exit Function
      End If
   Loop

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
    
    TimeOut = Now + TimeValue("00:00:05")  '-- wait maximum of 5 seconds
   Do While QPR.Busy Or QPR.readyState  4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Time Out after Login"
         Exit Function
      End If
   Loop
   
   Set lnk = QPR.Document.Links(3) ' 3=TMMK-VEH,4=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
    
   TimeOut = Now + TimeValue("00:00:05")  '-- wait maximum of 5 seconds
   Do While QPR.Busy Or QPR.readyState  4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Time Out after Login"
         Exit Function
      End If
   Loop
   
   lnk.Click
      
End Function


Hi, if I write VBA code to modify cell B3. Later on I inserted a new row above it, so now B3 became B4. The problem is my VBA code will still modify cell B3 every time I run it, not the new location B4.

Is there a way to make VBA code automatically update the cell location? Just like if you use = assignment in a cell, the referred cell will be automatically updated by excel every time you insert or delete rows/columns.

Thanks for your help.

Jianhua Huang

Hello

Please can someone help?

I have the task to write a program in VBA which will automatically select payments which have been made by collectors on the previous day.

Without breaking the DPA I will use google as an example of what I need.

Imagine i have been asked to write a program in VBA to search the internet for "data Protection".

Thsi is what i have so far

Sub google()

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.navigate "www.google.com"
IE.Visible = True

While IE.busy
DoEvents
Wend

End Sub

Can someone please advise how I ask VBA to imput "data Protection" into thew search bar in google?

Thanks you in advance.

FP

Hi,

I have just started to use the Excel Object Library. Here is a code that opens two Excel files, TEST and BLOTTER. Let us say both have some data in sheets called SheetFile1(of TEST) and SheetFile2(of BLOTTER).

How would I copy all the rows from SheetFile2 and append them to SheetFile1 which already has a few rows in it? (No need to check if total > 65000, etc.)

Here is the code I use to open both files. Please feel free to remove any redundancy.

Sub OpenFINAL()

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Dim o2Book As Excel.Workbook
Dim o2Sheet As Excel.Worksheet

sfilename = "Q:ZOOMTEST.xls"
s2filename = "Q:ZOOMBLOTTER.xls"
' Create a new instance of Excel
Set oExcel = New Excel.Application

Set oBook = oExcel.Workbooks.Open(sfilename)
Set o2Book = oExcel.Workbooks.Open(s2filename)

' Get a reference to the default/active sheet in the workbook (typically "Sheet1")
Set oSheet = oBook.ActiveSheet
Set o2Sheet = o2Book.ActiveSheet

' Assign a value to the first column of the first row
oSheet.Cells(2, 2).Value = o2Sheet.Cells(2, 2).Value
'----> So this part needs to change, all rows from BLOTTER have to be appended to TEST with formatting.

oBook.Save

' Close the workbook
oBook.Close
o2Book.Close
' Close Excel
oExcel.Quit

' Release the object references
Set oSheet = Nothing
Set oBook = Nothing

Set o2Sheet = Nothing
Set o2Book = Nothing

Set oExcel = Nothing

End Sub

Oops!
I happen to find that

SOLVER
Microsoft Forms 2.0 Object Library

in VBA References in Excel can't be checked permanently,that's once I
check them in a new blank excel file,and then close that blank file(of
course I don't save it) and open another new excel file,those two
component are back in the unchecked state.

but this weird phenomenon doesn't occuered for others,I don't know how
to solve it...

--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=470167

I have a VBA application written in Excel 2000.
The appl contains several forms and custom menus.

I tried running the appl on Excel 2003, but got errors.

(I can't remember exactly what the errors read,
but Excel 2003 gave me warning messages saying
it didn't recognize some components or code.
And, the code would not compile.)

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I believe the problem is the Excel 2003 object libraries
are different from the Excel 2000 object libraries.
And, I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look, and
the PC is at a different location. I'll be trying to get the
appl working on Wed, Aug 9. But, I'll be at that other
location tomorrow, Tue, Aug 8th, and will have some
brief time available on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:

Visual Basic For Applications (VBE6.DLL)
Microsoft Excel 9.0 Object Library (Excel9.OLB)
OLE Automation (stdole2.tlb)
Microsoft Forms 2.0 Object Library (FM20.DLL)
Microsoft Office 9.0 Object Library (MSO9.DLL

--
ZimF
-----------------------------------------------------------------------
ZimFL's Profile: http://www.excelforum.com/member.php...fo&userid=3721
View this thread: http://www.excelforum.com/showthread.php?threadid=56928

I have a VBA application written in Excel 2000.

Running the appl on Excel 2003 generated errors,
and the appl would not run on the Excel 2003 PC.

(I can't remember exactly what the errors read,
but Excel 2003 didn't recognize some components
or code. And the code would not compile.)

The appl contains about 10 forms, custom menus,
macros, & custom functions. The forms have
standard, native controls that include simple text
boxes, command buttons, spin buttons, combo
boxes, & labels. All controls are on forms -- no
controls are imbedded in worksheets.

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I suspect the forms are causing the errors, and
the problem is the Excel 2003 object libraries are
different from the Excel 2000 object libraries. And,
I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look,
and the PC is at a different location. I'll be trying
to get the appl working on Wed, Aug 9. But, I'll be
at that other location tonight, Tue, Aug 8th, & will
have some brief time on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:
- Visual Basic For Applications (VBE6.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)
- OLE Automation (stdole2.tlb)
- Microsoft Forms 2.0 Object Library (FM20.DLL)
- Microsoft Office 9.0 Object Library (MSO9.DLL)


No luck finding an answer? You could always try Google.