Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vba reference library Results

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

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

Does anyone know if the VBA references are saved with the workbook?

For example, if I select some Microsoft ADO Library references in my workbook, save it, and give it to someone else who has the same Excel installation (same libraries available), will the ADO references still be there?

Thanks again.

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 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 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?

I've got a user who recently upgraded from Office 2002 to Office 2003 due to migration of some of our excel templates to 2003. A excel spreadsheet developed in Excel 2002 then modified and saved, including updating VBA code, with Excel 2003 will not allow this user to add the the Office11 version of regedit.dll to his VBA reference list. Upon opening the worksheet, the VBA code fails to compile, after stopping/reseting the debugger and checking references, it shows as missing the regedit.dll. Following the normal procedure to uncheck the missing reference and browsing to the file to select and "open" it from the browser dialog to add it from the Office11 library into the references list, the reference list comes back with the item rechecked and still shown as a missing reference. Anyone got any idea why Excel refuses to recognize the .dll file and add it to the VBA reference list?

I have a workbook that runs code when the "print" macro is activated to save a backup copy of the sheet to the user's c-drive for archiving purposes. This form also sends an email based upon conditions in the form, so I've had to reference the Outlook 11.0 Object Library.

Everything works great in the standard form, however, if you open the backup copy on the c-drive I seem to have a problem with this Library not being selected.

Is there anyway I can automate the selection of this Library, or write some other kind of workaround for this. The error I get when activating the "print" macro on the backup copy is "Compile error: User-Defined type not defined", it highlights the following code line:
Code:
and the Outlook 11.0 Object Library is not selected in the list of references in the vbe.

Thanks in advance for any help you can offer... =)

Ben Poese

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

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 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?

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


Hi,

I am a very new member of this forum and this is my first post.

I have prepared an excel macro which prepares n numbers of word files from "Data" sheet and saves it with a name driven by a range of cells. Then emails each attachment to a specific recipient (driven by a range of cells) with a subject, body (driven by a range of cells) etc from outlook. But, I am stuck while trying to do the same thing from Lotus Notes 8.5 instead Outlook. Your expert openion highly appretiated.

Any advise? Codes I am using for Outlook are as follows:


	VB:
	
 Test() 
     
    Dim i As Long 
    Sheets("Data").Select 
     'Script should run upto 999 rows of data
     'Since we come across 150 NSW Disclaimer Letters a day 9999 rows should be more than sufficient
    FinalRow = range("A999").End(xlUp).Row 
     
    For i = 2 To FinalRow 
         
        Sheets("Data").Select 
         'To copy data from A2 cell of "Data" Sheet and to paste it to C4
         'Cell of "Template" sheet
        range("A" & i).Copy Destination:=Sheets("Template").range("C4") 
        Sheets("Template").Select 
         'To copy Disclaimer template from "Template" sheet
        range("C32:I71").Copy 
         
         
         'Microsoft Word 8/9/10/11/12 Object Library added from reference library
         'Tools ----> References ----> Microsoft Word 12.0 selected
         'To create a new instance of Word Application
        Dim appWD As Word.Application 
        Set appWD = CreateObject("Word.Application") 
        appWD.Visible = True 
         'To open a pre existing formatted** word file from desktop
         '**For this script go to Home --> Paragraph --> Spacing Before & Aftershould be 0 pt
         '**and "Don't add space between paragraphs of the same style" should be ticked.
         '**select it as Default settings
        appWD.Documents.Open ("C:UserssomnathDesktopDoc1.docx") 
         'To maximize to word file
        Application.WindowState = xlMaximized 
         'To paste Disclaimer template to opened word file
        appWD.Selection.Paste 
         'To save the word file with name derived from "H11" cell of "Template" sheet
        Sheets("Template").Select 
        ThisFile = range("H11").Value 
        appWD.ActiveDocument.SaveAs Filename:=ThisFile 
         'To close active word document
        appWD.ActiveDocument.Close 
         'To close word application
        appWD.Quit 
         
        Dim ToContact As Outlook.Recipient 
        Dim olMailItem As MailItem 
        Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream 
        Dim rngeSend As range, strHTMLBody As String 
         
        On Error Resume Next 
        Set rngeSend = Sheets("Data").range("M1:AE15") '.SpecialCells(xlCellTypeVisible)
         
        If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
        On Error Goto 0 
         
        ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:UserssomnathDesktopTemp.htm", rngeSend.Parent.Name,
rngeSend.Address, xlHtmlStatic).Publish True 
         
        Set FSObj = New Scripting.FileSystemObject 
        Set TStream = FSObj.OpenTextFile("C:UserssomnathDesktopTemp.htm", ForReading) 
         
        strHTMLBody = TStream.ReadAll 
         
        Set OLF = GetObject("", _ 
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) 
        Set olMailItem = OLF.Items.Add 'creates a new e-mail message
        Sheets("Data").Select 
        ThisFile2 = range("L1") 'Recipient
         
        With olMailItem 
            .Subject = ThisFile '"Subject for the new e-mail message" ' message subject
            Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'ToContact.Type = olCC ' set latest recipient as CC
             'Set ToContact = .Recipients.Add(ThisFile2) ' add a recipient
             'ToContact.Type = olBCC ' set latest recipient as BCC
            .HTMLBody = strHTMLBody '"This is the message text"
             ' the message text with a line break
            .Attachments.Add ("C:UserssomnathDocuments" & ThisFile & ".docx") 
            .OriginatorDeliveryReportRequested = False ' delivery confirmation
            .ReadReceiptRequested = False ' read confirmation
            .Save ' saves the message for later editing
            .Send ' sends the e-mail message (puts it in the Outbox)
             
        End With 
        Set ToContact = Nothing 
        Set olMailItem = Nothing 
        Set OLF = Nothing 
        Set FSObj = Nothing 
        Set TStream = Nothing 
        Set rngeSend = Nothing 
         
    Next i 
     
    MsgBox ("NSW Disclaimer Letter Attachment Generation Completed") 
    Application.Visible = True 
     
End Sub 

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


Greetings everybody.

Not an experienced programmer so maybe I'm missing something, but here's what I'm trying to do. I am inserting an add-in as *.xlam workbook that will contain a module with my code. One of the macros in this add-in when called via a button on a ribbon should remove all modules and code from any workbook opened and copy module from the add-in workbook to the open workbook. I have found a standard removal code that I'm using which works fine, however issues arise when using 'copy module' code, which I found somewhere as well:


	VB:
	
 CopyAllModules() 
     
    Dim FName As String 
    Dim VBComp As VBIDE.VBComponent 
     
    With Workbooks("Book2") 
        FName = .Path & "code.txt" 
        If Dir(FName)  "" Then 
            Kill FName 
        End If 
        For Each VBComp In .VBProject.VBComponents 
            If VBComp.Type  vbext_ct_Document Then 
                VBComp.Export FName 
                Workbooks("book1").VBProject.VBComponents.Import FName 
                Kill FName 
            End If 
        Next VBComp 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know that I need to Reference Extension Library here which I've tried doing different ways. However, the problem is that after I have referenced the library I need to call the macro and that's when I get messages "code cannot be executed in break mode" and "object variable or with block variable is not set". My guess is that a sub that links the Library must be completed before the next should be run, but I don't know how to achieve that. I've tried using functions as below (where DPMacro is the macro that removes original/adds my modules), but that does not help.


	VB:
	
 
Sub reference() 
     
    refvar = 0 
    Call checkreference(refvar) 
    If refvar = 0 Then 
        Call addreference 
    End If 
    Call DPMacro 
     
End Sub 
 
 
Function checkreference(refvar As Variant) 
    For i = 1 To Application.VBE.ActiveVBProject.References.Count 
        If Application.VBE.ActiveVBProject.References.Item(i).Description = "Microsoft Visual Basic for Applications
Extensibility 5.3" Then 
            refvar = 1 
        End If 
    Next 
End Function 
 
Function addreference() 
    Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB" 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas on this one. If my explanation of what I trying to achieve is not clear - please let me know.

I'm trying to get my spreadsheet to communicate with an outside program.

I've added the appropriate reference in VBA, and used the object browser to find the information I need. However when I try to run a test it pops up with "method or data member not found" error.

The reference library is called "FCCSSOINTf"
The Class I want is called "IAuthenticator"
And the member is "GetUserId"

So I feel like I want the code to read:


	VB:
	
 
sUserID = FCCSSOIntf.IAuthenticator.GetUserId 
msgbox(sUserID) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But it gives me the error. always highlighting the Class portion (in this case IAuthenticator. It does this across tests with other references also, so am I just building the line of code incorrectly?

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

Hello everybody!

I'm having a problem with a missing reference when moving an xlsm file into another computer.
After a little research I've found that the problem is the minor number.
For some reason, in my PC the reference is added with minor = 2 and the target PC needs it with minor=0 (I've checked the minor number in the target PC by fixing the reference by hand and listing the references again).

First of all, why's that difference with minor numbers? The workbook use 8 different references and only one fails to load because of that (I didn't check the other minor and major numbers because didn't fail, but I guess they're the same). The path to the reference (REFEDIT.DLL) is okay, the only difference is the minor number.

Anyway, I've tried to fix this with some code I found on the net and I can't remove the reference!


	VB:
	
 AddReference() 
     'Macro purpose:  To add a reference to the project using the GUID for the
     'reference library
     
    Dim strGUID As String, theRef As Variant, i As Long 
     
     'Update the GUID you need below.
    strGUID = "{00024517-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 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've run this in debug mode and stopped it after the Remove line was executed, went to see if the reference was actually removed but was still there so I guess that's why it isn't added again later (because it's already there).

What am I missing?

I have successfully written/borrowed some simple code to create a basic e-mail and attached a pdf from some spreadsheet data.

However, there are about three form fields in the pdf that I need to populate from the spreadsheet also.

Is this possible? and if so I'd be very grateful for some pointers. Have looked at the various acrobat reference libraries.

I use Excel 2007, XP and my users will have reader rather than Pro.

Many thanks


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