enable reference library through vba

I have a workbook that uses the Microsoft ActiveX Data Objects 2.8 Library. I need to share this workbook with others.

How could I enable this library through VBA on the workbook_open event?

Thanks!
smokebreak


Hi-

I am working remotely in word vba through excel vba. Basically I have
programed in excel to create a new word document, add a button, and
then add code to the word document for when the button in excel is
clicked. The code I am adding to the word document needs to copy a
table and paste it (right above the button). I figured out how to copy
a table if you give it an index number, but with the other code I am
running the number of tables always changes. Therefore hardcoding an
index number is a bad idea. I will end up copying different tables
each time and not the one I want. Is it possible to add a name or
range to that table, so I can just copy the table name or range?

If this is not possible, I wanted to know if its possible to code
adding an excel reference library in word. If I have the word document
button code remote access excel to get the named table range that way,
I need to figure out how to code an excel reference library into the
button code. I do not know how or if this is possible. Manually
setting it is not an option. I really hope someone can help me! Thank
you so much in advance.

sarah

I am using a reference to the Microsoft Forms 2.0 Object Library in a workbook.

When I copy a page from the workbook (using VBA code), the new copy does not retain the reference to Microsoft Forms 2.0 Object Library.

How can I re-establish that reference in my VBA code so the user will not get an error message caused by the missing reference?

I am writing VBA Code to import data from an Access Database and using in Excel. I need to share this code with others in my office, so I need to Dynamically add the library "Microsoft ActiveX Data Objects 2.8 Library". I am using the following code, and it does not give me an error, but it also does not add the library.

	VB:
	
 AddReference() 
     'Include Reference Library
    On Error Resume Next 
    ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesCommon FilesSystemADOmsado15.dll" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code is located in the same module that calls it, and is called at the beginning of the program. However, as soon as it runs, and the code calls for an ADODB object, it is not recognized, since the library did not add correctly.

I would greatly appreciate any help.
Thanks,
Chris Johnson

Hi,

I have a tool developed in VBA which references the ActiveX Data Obj Library
2.8. Now this code runs fine on some machines but on others it gives a
reference error. So I have to then add the reference to the ADO library on
that machine and run the code. Is it possible to overcome this problem in
some way and run the code on all machines without any error? If I somehow add
the libraries through coding, will it help? And if yes, how do I add the
libraries through code itself rather than going to the Tools menu and
selecting the reference?

Any help is appreciated.

Thanx.

I check the ADO 2.7 box in order to run some macros to update Access tables
but the reference box is unchecked every time I shut down the cumputer.

This problem does not happen to other items that I have checked under the
VBA Reference Library.

What do I need to do?
--
Thanks.

CY

Hi,

I'm currently trying to add the "Solver" reference using VBA code but for some reason my code keeps coming with errors. I have tried using the codes provided in the following thread but I still get the same error even though the code seems to work for others.

Autoload reference library by name

The error I get is:

Run-time error '1004':
Application-defined of object-defined error
The current code I have at the moment that I wish to use is:


	VB:
	
 
Dim k As String 
 
j = Application.Version 
k = "C:Program FilesMicrosoft OfficeOffice" & j & "LibrarySOLVERSOLVER32.Dll" 
Application.ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice14LibrarySOLVERSOLVER32.DLL" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am using Microsoft Excel and the VBA compiler to build this, so my theory is that it doesn't like being referred to as "VBProject".

The reason, I am doing this is because when I transfer my file to a computer with an older version of Solver, my program throws a fit and doesn't wish to work hence I require this code to make sure that the solver reference is always applied.

Many Thanks,

Vincent

I've been trying to use the INDIRECT function in VBA, and it won't take it. I finally came to the thought thought that there might be a Reference Library that I don't have turned on. Can anyone tell me if there is a reference library for that, and which one is it?

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 All,

I was impressed by Ian's auto loading vb reference libraries in the Hey! That's Cool Forum. I was wondering if there is any way to do the same thing (Autoload selected) with Add-ins when the Workbook Loads, rather than having each person who uses the workbook to have to add them in from the Menu.

I am thinking mainly of the Analysis ToolPak and the Analysis ToolPak-VBA, both of which are required in many of the Workbooks that I have used.

Regards,

Bill

I have used the following code before...but now, on a new pc it is not working...I have a feeling that the reference libraries I had incorporated in the past are not activated in the new version of excel I am running...is this true, or do you see some other issue with this code?

Sub Adjust_Zoom()
'
' Adjust_Zoom Macro

vidWidth = GetSystemMetrics32(SM_CXSCREEN)
vidHeight = GetSystemMetrics32(SM_CYSCREEN)

ScreenResolution = vidWidth & " X " & vidHeight

Application.ScreenUpdating = False

Select Case ScreenResolution

Case "1024 X 768"

For Each item In ThisWorkbook.Worksheets
item.Select
ActiveWindow.Zoom = 75
Next item

and so on.....

Thanks in advance...

I made a project in Excel 2002 that works great in that version of Excel and in Excel 2003 but when I try it on a computer that has Excel 2000 I get a runtime error that an object library is missing. The references that I used were Microsoft Office 10.0 Object Library, Microsoft Word 10.0 Object Library, and Microsoft Forms 2.0 Object Library. When I didn't see them listed in the references list on the older computer I tried copying the files for these 3 references to the older computer. I then opened the VB editor and clicked Tools and References and clicked the browse button. I can locate the references that I copied to the older computer but when I click on it to try to add it nothing happens.

I also tried to select the Microsoft Office 9.0 Object Library but the code won't run using it.

I am very new with VBA and am in a bind because I need to make the program work in Excel 2000. Is there an easy fix to getting these 3 reference libraries onto the older computer?

Hi, Does anyone know how to install the Small Business Financials (SBF)
reference library in the VBA window to automate tasks in MS Excel using a
macro?
--
Regino Torres

I'm having problem in adding constaints to a mean variance optimizer model. The constraint range for asset weights (between 0% and 100%) are specified through 2nd and 3rd column of range "wts_matrix_mthly". All other constraints are added, except those where the weight is 100%, i.e., 1. I don't know why it doesn't add them. Manually I can add them, but through VBA, it just won't add them. I tried even macro recording, which generates the same code, but when added to procedure, it won't add the constraint where relation is 2, and formulatext is 1. If I set maximum weight to 80%, or 0.8, it will add the constraint, but not 100%. What can be the possible reason?

Any help in this regard is appreciated. Thanks!

Note: The cell reference "E275" is the sum of asset weights, which should be equal to 100%. Even that was not added; only when I manipulated it by multiplying the sum with 100 and then adding the constraint of 100 instead of 100% or 1, it worked.

Sub OptimizeMthly()
    
    SolvReset
    
    SolvAdd CellRef:=Range("E275"), Relation:=2, FormulaText:=100
    SolvAdd CellRef:=Range("optimal_wts_mthly"), Relation:=3, FormulaText:=0
    
    For i = 1 To 20
        
        If Range("wts_matrix_mthly").Cells(i, 1).Value = "1" Then
            
            If Range("wts_matrix_mthly").Cells(i, 2).Value > 0 Then
                SolvAdd CellRef:=Range("wts_matrix_mthly").Cells(i, 4), Relation:=3,
FormulaText:=Range("wts_matrix_mthly").Cells(i, 2)
                SolvAdd CellRef:=Range("wts_matrix_mthly").Cells(i, 4), Relation:=1,
FormulaText:=Range("wts_matrix_mthly").Cells(i, 3)
            Else
                SolvAdd CellRef:=Range("wts_matrix_mthly").Cells(i, 4), Relation:=1,
FormulaText:=Range("wts_matrix_mthly").Cells(i, 3)
            End If
        
        Else
            SolvAdd CellRef:=Range("wts_matrix_mthly").Cells(i, 4), Relation:=2, FormulaText:=0
        
        End If
    
    Next i
    
    Range("results_mthly").ClearContents
    gp = 0.0048 / 25
    
    For counter = 1 To 25
    
        Range("constant_mthly") = counter * gp
        SolveKarMthly
        Application.SendKeys ("{Enter}")
        Range("results_mthly").Cells(counter, 1) = Range("meu_prt_mthly")
        Range("results_mthly").Cells(counter, 2) = Range("sigma_prt_mthly")
        Range("results_mthly").Cells(counter, 3) = Range("theta_mthly")
        
        For j = 1 To Range("optimal_wts_mthly").Count
            Range("results_mthly").Cells(counter, j + 3) = Range("optimal_wts_mthly").Cells(j, 1)
        Next j
    
    Next counter
       
End Sub


Sub SolveKarMthly()

    SolvOK SetCell:=Range("theta_mthly"), MaxMinVal:=1, ByChange:=Range("optimal_wts_mthly")
    SolvSolve UserFinish:=True

End Sub


Aim/ Object — I've made this Time Tracker workbook and it's working fine. I want to keep protected the cells of columns A, C, D and E, so I just added the codes in the VBA. Therefore one can run the time but can not delete the value.

Problem — I want to allow formatting in these protected cells through VBA codes.

If I put below code in the last steps of my macro:

    Worksheets(1).Activate
    Worksheets(1).Protect (123)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True

...then the required thing (allowing formatting) is enable on the protected cells, but If I want to manually unlock the protection then it doesn't asks the password. The protection of the worksheet is just removed by clicking on the Unprotect Sheet option(in the Tool Menu).

In this case anyone can misuse the workbook, easily can delete the data since there is no requirment of the password.

OR

If I put the above code as follows (in the last steps of my macro):

    Worksheets(1).Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    Worksheets(1).Protect (123)

...then no one can unlock/ break the protection without the password. It will ask the password if somebody wants to manually unlock the worksheet, but the other side this code doesn't allow formatting on the protected cells.

How can I enable formatting options on the protected cells, and if somebody wants to unprotect the worksheet then he must need the password too?

Note - I am testing with password: 123

Kindly help me out!

Thanks in Advance!

Hello,

I have written a function in C that calculates a double through an
iterative process (I found VBA too slow for it).

By adding the declaration to my function in VBA, the function becomes
available as a User Defined Function :

Public Declare Function afschrijving _
Lib "C:Program FilesMicrosoft OfficeOfficeair.dll" _
Alias "_afschrijving@52" _
(ByVal aankoopwaarde As Double, _
ByVal levensduur As Long, _
...
) as Double

Is this possible by just moving the dll in the right directory and
registering the function somewhere somehow when Excel starts ? The
calculation is still slow, even when I simplify the function to an
addition of 2 variables. I suspect that by going through VBA and a
User Defined Function, it slows down the calculation.

When my function makes a division by zero, I would like to communicate
an error to Excel (now I return zero as answer, which is wrong, I want
to have #DIV/0 in my worksheet).
I can put the answer in an XLOPER-structure. When there is no answer
but an error-message, I can put it in the XLOPER-structure as well.
But how do I declare such a function in VBA ?

Public Declare Function afschrijving _
Lib "C:Program FilesMicrosoft OfficeOfficeair.dll" _
Alias "_afschrijving@52" _
(ByVal aankoopwaarde As Double, _
ByVal levensduur As Long, _
... , _
ByRef answer As XLOPER _
) as Long

The Long that is returned becomes useless, the answer is now in the
XLOPER-structure (though I don't believe that this works : how do I
create a reference to an XLOPER-structure in VBA), but how do I declare
a function 'VOID' in VBA ?

A lot of questions, is there someone who can point me to a book or a
webpage that explains these things ?

With kind regards
Joris Adriaenssens

Hi all ,

I just started using add-ins in Excel 2003 and it is driving me nuts !!

To remove an add-in through vba code from inside its own .xla file I am using the following code:

That will trigger the event of Workbook_AddinUninstall() from the ThisWorkbook of the .xla file that have the code:

Or:

The AddMenuOff module just have the code to create a menu when the add-in is uninstalled.

In fact it is working but I receive this error message when the code has finished run:

The macro “Open Order Add-in.xla’!Auto_Remove’ cannot be found.

Note that I have no Auto_Remove function. And that the error does not come right after the line:

I think that like I am removing the add-in from inside its own running code this internal Auto_Remove function is failing.
But it's a Auto_Remove so it should not be working ? 
.... What I am missing here...

Please help

If somebody has experience on add-in will be able to answer reading till here. Bellow is better explanation about what I am doing and why….

I added a menu in Excel 2003 using an add-in I just created. In fact I have 2 menus.
The first menu appears when the add-in is installed being the full menu with all the options including the option to auto remove itself and the add-in then making appear the second menu. The second menu is just to turn on the add-in and enable the full menu again.

To generate both menus I have 2 modules the MenuOn ( ) and MenuOff( ).

My idea is to reduce as much possible the code from the .xls file. The MenuOff( ) code stays inside the .xls because I will need that code make the menu appear and disappear when I have the correct workbooks activated. The MenuOn( ) code is inside the .xla file obviously because it will only appears when the add-in is installed and once it is installed I have the code for the menu and all its options inside the .xla file.

i just got a new computer and need to set up vba how i had it on my old
computer. when i go in a check the reference libraries that i need then close
xl the libraries don’t stay checked.

what do i need to do to rectify this?

I have a Reference that I load into VB6 and it adds an event in the Visuals Basic code. The sub is executed automaically when a USB device receives data.

Sub RecvAction(ByVal Action1 As Variant, ByVal Action2 As Variant)

end sub

When I load the same Reference in VBA, it does not show up in code but looks like the in Object Browser

Event RecvAction(Action1, Action2)

How do I access this in VBA?

Hi All

I'm trying to programmatically add and remove the Outlook 11.0 Reference Library in Excel 2003.

So far, I have found the following code which successfuly adds the reference:

    Dim strGUID As String,
theRef As Variant, i As Long
     
     'Update the GUID you need below.
    strGUID = "{00062FFF-0000-0000-C000-000000000046}"
     
     'Set to continue in case of error
    On Error Resume Next
     
     'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.isbroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i
     
     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear
     
     'Add the reference
    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:=strGUID, Major:=1, Minor:=0
     
     'If an error was encountered, inform the user
    Select Case Err.Number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0
But when I try to remove it using the following code, it doesn't work.

     'Update the GUID you need below.
    strGUID = "{00062FFF-0000-0000-C000-000000000046}"
    
     'Set to continue in case of error
    On Error Resume Next
     
    'Remove the reference
    ThisWorkbook.VBProject.References.Remove strGUID
Does anybody have any suggestions of what I'm doing wrong?

Dion

FORGET THIS POST: The code below DOES WORK after all!!!
I just had a jump-around, and it wasn't executing; didn't realize it until doing a better job of debugging the code. Sorry for the stupidity!!!!!

NOTE: This is for WORD 2003!!!
1) I am trying to programmically add the Microsoft Visual Basic for Applications Extensibility 5.3 reference library to a Word Document. I put the following code in the Document_Open Event procedure, but it does NOT add the reference. It doesn't error; but doesn't add the reference.
2) I want to do this programmically because I want to develop an automated (VBA) means to copy multiple modules from a SOURCE document to many TARGET documents (to be used by future users when modules are revised). And, want these VBProject objects to be automatically available without requiring others to manually add this reference.
3) I got this code from multiple internet sites, but it was for Excel, not Word. The only difference is that instead of "ActiveDocument", this text was "ThisWorkbook". Other than that, if it works for Excel, then with this object name change, it ought to work for Word. Any ideas why this code doesn't work in Word?
 ActiveDocument.VBProject.References.AddFromGuid GUID:= _
  "{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3


I have an activex checkbox control that I have tied to a cell ( by checking "move and size with cell" option ). If I copy this cell in design mode and paste it somewhere else the checkbox also gets pasted along with the cell value. But if I do this in run mode then checkbox is not copied.

The problem is, I need to do this operation through VBA, but copying pasting a cell through VBA always seem to be occuring in Run mode, since the checkbox isn't getting copied.
How can I copy this cell along with the checkbox through VBA.??

Hi all,

I want to add a reference library to my file using code. The Excel file is being sent to somebody with a different version of Excel. The code uses the add-in Solver, and whenever the recipient tries to use a button which triggers Solver, because of the different version, the VB can't find the reference library.

I'm hoping it's possible to remove the SOLVER ref library and then reinstate it by name using code on the open event of the workbook.

Any ideas greatly appreciated,
A similar thread is present in the Forum called:
"auto loading vb reference libraries"

Regards,
B

Hello Wiz,

I would like to get some help on choosing the right files through vba codes. I have the dates in cell a3 and b3 and all my files ends with the date. The user inputs the date and the code should get the data within that date.

I would like to get some thoughts on how to get the value of cell a3 and b3 and open only those files from the folder.

I have attached a sample in case am confusing with my question.

Thanks a lot for looking at my post and I appreciate your time to look at my post.

Hi,

I am trying to automatically create pivot tables through VBA. My data sheet is called MASTER.
I tried to create a report sheet REPORTSH and then create pivot tables in that using the data in the master.

I tried to record the macro and got the code as follows :


	VB:
	
 
 
Sub Macro11() 
    Sheets("Master").Select 
    Sheets.Add.Name = "Reportsh" 
    Range("A1").Select 
    Sheets("Master").Select 
     
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    "Master!R1C1:R12900C8").CreatePivotTable TableDestination:= _ 
    "[Perfomancemgmt_AMZN.xls]Reportsh!R1C1", TableName:="Perftable1", _ 
    DefaultVersion:=xlPivotTableVersion10 
     
     ' I get error in the above line
     
     
    ActiveSheet.PivotTables("Perftable1").AddFields RowFields:=Array("Name", _ 
    "startdate", "Function Name", "Total Paid Hours"), ColumnFields:="Date", _ 
    PageFields:=Array("Employee Type", "Manager") 
    ActiveSheet.PivotTables("Perftable1").PivotFields("EachStowed").Orientation = _ 
    xlDataField 
    Range("A1").Select 
    ActiveWorkbook.ShowPivotTableFieldList = False 
    Application.CommandBars("PivotTable").Visible = False 
    With ActiveSheet.PivotTables("Perftable1").PivotFields("Employee Type") 
        .PivotItems("(blank)").Visible = False 
    End With 
    Range("A2").Select 
    With ActiveSheet.PivotTables("Perftable1").PivotFields("Manager") 
        .PivotItems("(blank)").Visible = False 
    End With 
    Columns("B:B").EntireColumn.AutoFit 
    Range("A4").Select 
    ActiveSheet.PivotTables("Perftable1").PivotFields("Count of EachStowed"). _ 
    Function = xlSum 
        Range("A5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Name").Subtotals = Array( _ 
        False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("B5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("startdate").Subtotals = _ 
        Array(False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("C5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Function Name").Subtotals = _ 
        Array(False, False, False, False, False, False, False, False, False, False, False, False) 
        Range("D5").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Total Paid Hours"). _ 
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _ 
        False, False) 
        Range("E4").Select 
        ActiveSheet.PivotTables("Perftable1").PivotFields("Date").Subtotals = Array( _ 
        False, False, False, False, False, False, False, False, False, False, False, False) 
        Columns("A:A").EntireColumn.AutoFit 
        Columns("B:B").EntireColumn.AutoFit 
        Columns("C:C").EntireColumn.AutoFit 
        Cells.Select 
        With Selection 
            .HorizontalAlignment = xlCenter 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 
         
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The problem is it is not creating the pivot and throwing up some error . Is there any other way to automate this.

Thanks in advance