Free Microsoft Excel 2013 Quick Reference

Application.VBE.ActiveVBProject.References.AddFromFile


	VB:
	
Application.VBE.ActiveVBProject.References.AddFromFile= "C:Program FilesMicrosoft OfficeOFFICE11MSOUTL.OLB" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This command is tripping me up with an error message. Worked perfectly every time, and then my company had to switch me to a terminal server 2003. It worked on my local desktop, but I'm not allowed to use that anymore.

Error message reads: "Programmic access to Visual Basic is not trusted". The path exists on the Terminal server. Other VB programs are working for me just fine.

IS is stumped. What might be causing this error?


Post your answer or comment

comments powered by Disqus
Hello all!

I have the following module loading the script environment.


	VB:
	
 AddRuntimeLibrary() 
    On Error Resume Next 
    Application.VBE.ActiveVBProject.References.AddFromFile "C:WINDOWSsystem32scrrun.dll " 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Should I check to see if it is loaded before I call this sub? Will it reload and take more memory?

Everything works, just looking for ideas to best practice.
How would I test to see if loaded? or should I put it in the open ws funtion and assign a flag if it loads OK?

Thanks
Bruce

I need some help. A program that I use every day just got glitch-y now that I have just got Office 2003.
One of my programs had these commands.

On error resume next
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice10MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice11MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice10OUTLCTL.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice11OUTLCTL.DLL"

Now my Office 11 doesn't have these files in it, but has another folder called 1033, and these files aren't in that folder either! Now, I can't remember why I put these commands in. It will take me quite a long time to figure out why I actually put those in there, so before I go back and do that, I wanted to ask if there is a quick-fix to the problem?

I use Office 2003 but most of the end-users for my application have office XP.

The application uses the outlook object library. the problem is that since I
have office 2003, it references version 11 of the library. I have the
following code to try to avoid this problem.

Private Sub ReferenciaXP()
Dim str As String
Dim v As Integer
Dim i As Integer
Set Refs = Application.VBE.VBProjects(1).References
v = Application.Version
If v < 10 Then
MsgBox "Esta aplicación solo esta hecha para Office XP o superior",
vbInformation + vbOKOnly, "xMedios"
ActiveWorkbook.Close
Else
If v = 10 Then
For i = 1 To Refs.Count
If Refs(i).IsBroken And Refs(i).name = "Outlook" Then
Application.VBE.VBProjects(1).References.Remove Refs(i)
End If
Next i
str = Application.Path & "msoutl.olb"
Application.VBE.ActiveVBProject.References.AddFromFile (str)
End If
End If

End Sub

But it does not work on all machines.
Sometimes the user is missing more references that are needed. I'd like it
if there was a way that the user could add the missing references without
having access to the proyect.

Any ideas??

--
Mariano

Code 1 adds the ADO 2.6 Library fine. Can someone tell me how code 2 should
read to remove the ADO 2.6 Library? I found CODE 2 as example on web, but
wasn't sure how it could be changed.

CODE 1
Application.VBE.ActiveVBProject.References.AddFromFile _
"C:Program FilesCommon FilesSystemadomsado26.tlb"

CODE 2
Set x = Application.VBE.ActiveVBProject _
.References.Item("Project2")

Application.VBE.ActiveVBProject.References.Remove x

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.

One of the problems I am having since my upgrade to Excel 2003 is my programs that are supposed to send e-mails. The first bugs I get is here (as follows)- I have a suspicion that this is where the whole problem lies.

These references are in the folders where they should be, but my program doesn't seem to be recognizing the command to add them.

Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice11MSOUTL.OLB"
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice11OUTLCTL.DLL"
Application.VBE.ActiveVBProject.References.AddFromFile "C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB"

It used to work with my prior version, but now there must be missing some prior step, or a different avenue to making the e-mails work. Any suggestions?

Hi there,

do you know how I can specify the Windows User App directory in an Excel VBA
macro?
What is the variable to use? (VBA API or Windows environment variables?)

In my case I want to code something like that:

Application.VBE.ActiveVBProject.References.AddFromFile
'CurrentUserAppsDirectoryVariableMicrosoftExcelXlStartmydll.dll'

Thanks very much

Hello, New to the Board but really need some help.

I work for a large organisation (6000 + PCs). I work on a team where we capture info on a excel spreadhsheet that has embedded macros. These workbooks get sent to different users who add their contribution to the workbook and save the changes to the workbook before returning to me.

The problem is that different users are using differnt versions of MS office, which means that if someone opens the document in office 2003 and saves the changes, the office 2000 reference is replaced by the office 2003 library, so when i get the file back it crashes and i have to change the reference manually.

I flagged this up weeks ago as an issue in the hope that i could amend the excel workbook we send out with something like :- (which would run on workbook open)

Set ref = Application.VBE.ActiveVBProject.References("Outlook")
Application.VBE.ActiveVBProject.References.Remove ref
Application.VBE.ActiveVBProject.References.AddFromFile_ "D:ApplicationsOffice2000OFFICE9msoutl.olb"

Sadly this option was not embraced, more PCs have been upgraded to Office 2003 and this is a time consuming issue

What i want to do is have a 'Master tool' that opens each template and sucks the new data out of them, fixing teh invalid reference as it goes. The reference MUST be fixed becuase i might forward the document onto another user that doesnt have Office 2003, and they will experience the same issue

Can i do something similar to the above code, but not in the active project.

something like :-

Dim wbsource as workbook

set ref = wbsource.application.vbe.activevbproject.references ("Outlook")

Blah blah.

So im affecting the VBE of the damaged excel workbook rather than the active project?

I tried it but get an error saying it conflicts with an existing library

Am i on the right lines?

The Master tool will user Microsfot Outlook object library 9.0 and so will the excel workbooks i send out. But when i receive the excel workbook back it will contain "Microsfot Outlook object library 10.0" which wont be on my PC, hence i want the code to automaticall open, replace the invalid reference with a valid one and close it down again.

Hope ive not over empasised. Any thoughts greatfully received

Well, I got something that did work. I changed

Set ref = Application.VBE.ActiveVBProject.References
to
Set ref = Application.VBE.VBProjects("MyProj").References

Where I renamed the default project name of VBAProject to
MyProj.

Now I can see my dll class, and I was able to remove it.
Whatever works, heh? I am still open for suggestions if
anyone has a better (or more correct) way to do this.

A J

>-----Original Message-----
>Hi All,
>
>I have an Excel project where I make a reference to an
>ActiveX dll class I wrote in VB6 on my workstation, but
>that file is not installed on other workstations which
>will use this Excel file. So I need to programmatically
>remove the reference before shipping the Excel file off.
>The purpose of the dll class is to collect data from an
MS
>Access Application and pass it to this Excel file. This
>works fine. But I need to remove the reference to my dll
>class in the Excle File. Even though the dll class
passes
>the data correctly to the Excel file, I can't detect it
>with the following loop, and thus cannot remove it.
>
>Dim v As Variant
>For Each V in Application.VBE.ActiveVBProject.References
>Debug.print v.Description
>Next
>
>This will correctly print out all the standard references
>and even the reference to VBA Extensibility, but it won't
>detect my ActiveX dll. In Access I can remove the
>reference successfully as follows:
>
>Dim ref As Reference
>Set ref = References!MyRef
>References.Remove MyRef
>
>But this does not work in Excel. If anyone knows how I
>can do this in Excel, I would be grateful if you could
>share.
>
>Thansk,
>A J
>
>.
>

Thought I'd chuck this on as it's something I use regularly and something I see a lot of other people mentioning in posts....

If you use code reliant on non-standard vb libraries then you can use the GUID for that library to reference it on the workbook being opened - then use the name of the library to remove it again when closing the file. Saves going in and ticking the references everytime!!

The below is what I use when working with ADO - I can not take credit for this as I found it on another forum but thought i'd post it up for others to use.

You can add these to your "ThisWorkbook" Sheet ... will install ADO (2.5 or above) and remove it on open and close of the workbook.


	VB:
	
 Workbook_Open() 
     
    On Error Resume Next 
     'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References 
    ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 
     
End Sub 

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

	VB:
	
) 
     
     'remove ADO reference
    Dim x As Object 
    n = Application.VBE.ActiveVBProject.References.Count 
     
    Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0 
        On Error Resume Next 
        Set x = Application.VBE.ActiveVBProject.References.Item(n) 
        y = x.Name 
        If y = "ADODB" Then 
            Application.VBE.ActiveVBProject.References.Remove x 
        End If 
        n = n - 1 
    Loop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If you don't know the GUID then reference it (by ticking it) and then run this to list all the active libraries and their GUIDs - adapted from NateO post on Mr E


	VB:
	
 Grab_References() 
     
    Dim n As Integer 
     
    Sheets.Add 
    ActiveSheet.Name = "GUIDS" 
     
    On Error Resume Next 
    For n = 1 To ActiveWorkbook.VBProject.References.Count 
        Cells(n,1) = ActiveWorkbook.VBProject.References.Item(n).Name 
        Cells(n,2) = ActiveWorkbook.VBProject.References.Item(n).Description 
        Cells(n,3) = ActiveWorkbook.VBProject.References.Item(n).GUID 
        Cells(n,4) = ActiveWorkbook.VBProject.References.Item(n).Major 
        Cells(n,5) = ActiveWorkbook.VBProject.References.Item(n).Minor 
        Cells(n,6) = ActiveWorkbook.VBProject.References.Item(n).fullpath 
    Next n 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope that proves as useful for others as it has for me.

I developed an excel project in 2000 and am now trying to deploy the project
in 2003 version of MS Office. Below is the code that has been developed. An
error occurs at line 4 and jumps to the error handler. Errormessage #1004 is
then displayed. As a note, I tried dimming ref as an object and also a
reference and an error occured with both. What is wrong with this code. Thank
you.

Public Sub ProjAddRef()
On Error GoTo Handle
Dim ref As Variant 'variant for ref only type that works with ref
collection
For Each ref In Application.VBE.ActiveVBProject.References 'iterate through
collection
With ref 'this will fix any broken references in project
If
Application.VBE.ActiveVBProject.References.Item("MSForms").IsBroken = True
Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{0D452EE1-E08F-101A-852E-02608C4D0BB4}",
2, 0) = True 'ms forms reference C:WINNTsystem32FM20.DLL full path
End If
If
Application.VBE.ActiveVBProject.References.Item("Access").IsBroken = True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}",
9, 0) = True 'access ref C:Program FilesMicrosoft
OfficeOfficeMSACC9.OLB full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADOR").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000300-0000-0010-8000-00AA006D2EA4}",
2, 6) = True 'ador ref C:Program FilesCommon
FilesSystemADOmsador15.dll full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADOX").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000600-0000-0010-8000-00AA006D2EA4}",
2, 6) = True 'adox ref C:Program FilesCommon FilesSystemADOmsadox.dll
full path
End If
If Application.VBE.ActiveVBProject.References.Item("ADODB").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{00000205-0000-0010-8000-00AA006D2EA4}",
2, 5) = True 'adodb ref C:Program FilesCommon
FilesSystemADOmsado25.tlb full path
End If
If Application.VBE.ActiveVBProject.References.Item("JRO").IsBroken =
True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{AC3B8B4C-B6CA-11D1-9F31-00C04FC29D52}",
2, 6) = True 'jro ref C:Program FilesCommon FilesSystemADOmsjro.dll
full path
End If
If
Application.VBE.ActiveVBProject.References.Item("Shell32").IsBroken = True
Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{50A7E9B0-70EF-11D1-B75A-00A0C90564FE}",
1, 0) = True 'shell32 ref C:WINNTsystem32shell32.dll full path
End If
If Application.VBE.ActiveVBProject.References.Item("VBIDE").IsBroken
= True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{0002E157-0000-0000-C000-000000000046}",
5, 3) = True 'vbide ref C:Program FilesCommon FilesMicrosoft
SharedVBAVBA6VBE6EXT.OLB full path
End If
If
Application.VBE.ActiveVBProject.References.Item("SHAPPMGRLib").IsBroken =
True Then
Application.VBE.ActiveVBProject.References.Remove ref

Application.VBE.ActiveVBProject.References.AddFromGuid("{3964D990-AC96-11D1-9851-00C04FD91972}",
1, 0) = True 'shapmgrlib ref C:WINNTSystem32appwiz.cpl full path
End If
Debug.Print .Name & " " & "Name" '''these print to
debug window
Debug.Print .Major & " " & "Major"
Debug.Print .Minor & " " & "Minor"
Debug.Print .BuiltIn & " " & "built in"
Debug.Print .Guid & " " & "Guid"
Debug.Print .FullPath & " " & "full path"
Debug.Print .IsBroken & " " & "is broken"
Debug.Print "***********************************************"
End With
Next
Exit Sub
Handle:
Select Case Err.Number
Case 32813
Resume Next 'if reference already exists
Case Else
MsgBox Err.Number & vbNewLine & Err.Description
End Select
End Sub

Hello,

I have an excel xlsm file which includes Microsoft outlook reference.
The file is opened in excel 2007 and excel 2010.

When the file is opened in Excel 2007 after it is opened in Excel 2010
I get a reference error message and a missing reference.

I wish to dynamic update the reference - remove the missing outlook reference and add the correct one.

I use the following code. If the reference is missing it adds it correctly.
My problem: when there is a missing reference the code can not remove it and I get an error.

Can you help me ?

Thank you for your help

Sub LoadOutlookReferences()

'load Outlook object library
On Error Resume Next
'When using AddFromGUID, you can use zero for the major
'and minor versions to pick the latest.

'adding VBE object library:
ActiveWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 0, 0
'Adding outlook object library:
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 0, 0
On Error GoTo 0

End Sub

Sub References_RemoveMissing()
     'Macro purpose:  To remove missing references from the VBE
     
    Dim theRef As Variant, i As Long
     
    On Error Resume Next
     
    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
     
    If Err <> 0 Then
        MsgBox "A missing reference has been encountered!" _
        & "You will need to remove the reference manually.", _
        vbCritical, "Unable To Remove Missing Reference"
    End If
     
    On Error GoTo 0
End Sub


Hi folks

Im having real difficulty with these macros.

I have a spreadsheet with 2 macros operated by one button on a sheet.

On opening the book code counts how many rows there are and places that calculation in cell g1. When the button is pressed the macro calculates the number of rows again, if a new row or rows have been added then the new row(s) are copied into a new sheet and attached to an email (Module 31). If there have been no new rows added then an email is created with text saying NIL return for today (Module 4). On both occasions todays date is inserted into cell D1 to show that a return has been made (either a NIL Return or the attached sheet with new rows). both these macros work fine from the button.

I run into problems with my Private Sub Workbook_BeforeClose(Cancel As Boolean) code:

I dont want the user to be able to close out of the workbook without warning them to send a daily return if cell g1 shows there are new rows or the date in cell D1 is not today.

If there are new rows I call Module 31, but in this instance the whole workbook is attached to an email rather than just the new rows and if the only difference is that cell D1 is not todays date the NIL return email is created ok but cell D1 is not updated with todays date unless i click Cancel when prompted to save when the workbook is about to close.

Can anyone give me any suggestions? I know there is a lot of code and its very cheeky of me to expect someone to sit and go through it but im desperate and i would be very grateful if someone could help me.

Thanks as much

Phil

Here's the code:

	VB:
	
 Workbook_open() 
    On Error Resume Next 
     'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References 
    ID.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9, 0 
     'count the number of rows and put the result in cell g1'
    Dim MyData As Range 
    Dim cntRecords 
    Dim s As Integer 
     
    With Worksheets("NAME") 
        Set MyData = ActiveSheet.UsedRange 
        cntRecords = MyData.Rows.Count - 2 
        Range("g1").Value = cntRecords 
         
        Call Module2.Overdue 
        Call Module2.DueToday 
        Call Module2.Due_less_than_5_days 
        Call Module2.Clarification_Overdue 
        Call Module2.Fees_Overdue 
    End With 
End Sub 

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

	VB:
	
 Daily_Return() 
     
    Dim MyData As Range 
    Dim cntRecords 
    Dim OL As Object 
    Dim EmailItem As Object 
    Dim FileName As String 
    Dim y As Long 
    Dim TempChar As String 
    Dim SaveName As String 
    Dim currentbook, newbook As Workbook 
    Dim MyNewSheet As Worksheet 
    Dim n As Integer 
     
    Application.Calculation = xlCalculationManual 
    Application.ScreenUpdating = False 
     
    Set MyData = ActiveSheet.UsedRange 
     'get the number of new rows'
    cntRecords = MyData.Rows.Count - 2 
    If cntRecords = Range("g1") Then Call Module4.email_NIL_Return 'if no new rows go to module 4'
    If cntRecords = Range("g1") Then Exit Sub 'on return from module 4 exit sub'
     
    n = cntRecords - Range("g1") 
     
    Application.ScreenUpdating = False 
    Set OL = CreateObject("Outlook.Application") 
    Set EmailItem = OL.CreateItem(olMailItem) 
    FileName = ActiveSheet.Name & ".xls" 
    For y = 1 To Len(FileName) 
        TempChar = Mid(FileName, y, 1) 
        Select Case TempChar 
        Case Is = "/", "", "*", "?", """", "", "|" 
        Case Else 
            SaveName = SaveName & TempChar 
        End Select 
    Next y 
     
    Set currentbook = Workbooks("NAME.xls") 
    ActiveSheet.Range("A3:H" & 3 + n - 1).Select 
    Selection.Copy 
    Workbooks.Add 
    Selection.PasteSpecial Paste:=xlPasteValues 
    Selection.PasteSpecial Paste:=xlPasteFormats 
     
    Set newbook = ActiveWorkbook 
     
    Application.CutCopyMode = False 
    ActiveSheet.Name = "NAME" 
     
    newbook.SaveAs "Daily Return for NAME" 
    newbook.ChangeFileAccess xlReadOnly 'Leave this line out to allow user to write and save
    With EmailItem 
        .Subject = "Daily Return for " & ActiveSheet.Name & " " & Int(Now) 
        .To = "1 NAME" 
        .CC = "4 NAMES" 
        .Attachments.Add newbook.FullName 
        EmailItem.Display 
    End With 
    Kill newbook.FullName 
    newbook.Close False 
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    Range("D1").Value = Int(Now) 
    Range("g1").Value = cntRecords 
    Set currentbook = Nothing 
    Set newbook = Nothing 
    Set OL = Nothing 
    Set EmailItem = Nothing 
     
End Sub 

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

	VB:
	
 email_NIL_Return() 
    Dim OL As Object 
    Dim EmailItem As Object 
    Dim Wb As Workbook 
    Dim FileName As String 
    Dim y As Long 
    Dim TempChar As String 
    Dim SaveName As String 
     
    Set OL = CreateObject("Outlook.Application") 
    Set EmailItem = OL.CreateItem(olMailItem) 
    With EmailItem 
        .Subject = "Daily Return for " & ActiveSheet.Name & " " & Int(Now) 
        .Body = "Please record a NIL Return for " & ActiveSheet.Name & " today" 
        .To = "1 NAME" 
        .CC = "4 NAMES" 
        EmailItem.Display 
    End With 
     
    Set Wb = Nothing 
    Set OL = Nothing 
    Set EmailItem = Nothing 
    Range("d1").Value = Int(Now) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And finally - where the problems materialise:

	VB:
	
) 
     'remove ADO reference
    Dim x As Object 
    n = Application.VBE.ActiveVBProject.References.Count 
     
    Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0 
        On Error Resume Next 
        Set x = Application.VBE.ActiveVBProject.References.Item(n) 
        y = x.Name 
        If y = "Outlook" Then 
            Application.VBE.ActiveVBProject.References.Remove x 
        End If 
        n = n - 1 
    Loop 
     
    ActiveWorkbook.Close 
     
    Dim MyData As Range 
    Dim cntRecords 
    Dim s As Integer 
    With Worksheets("NAME") 
        Set MyData = ActiveSheet.UsedRange 
        cntRecords = MyData.Rows.Count - 2 
        If cntRecords = Range("g1") And Range("d1") = Int(Now) Then Exit Sub 
        If Range("d1") = Int(Now) Then Exit Sub 
        If MsgBox("You have added " & cntRecords - Range("g1") & " records and haven't sent a Daily Return yet today. Do you
want to send one now?", vbYesNo) = vbYes Then Call Module31.Daily_Return 
    End With 
End Sub 

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


My VBA macro calls Solver.Solve, which requires the code
to reference solver.xla. Unfortunately, the location of
solver.xla is different for Office 2000 (C:Program
FilesMicrosoft OfficeOfficeLibrarySolver) than Office
XP (C:Program FilesMicrosoft OfficeOffice10
LibrarySolver).

Thus, if I set the reference (using the Visual Studio 6
IDE menuitem "Tools" > "References...") for Office XP,
then the macro fails on Office 2000, and visa versa.

How can I create a single version of my macro that
automatically references the proper location of
solver.xla so it can call SolverSolve?

I have tried the following approaches so far (without
success)...

1. Application.VBE.ActiveVBProject.References.AddFrom File
Application.LibraryPath & "SolverSolver.xla"

This approach ALMOST works, but it requires the user to
lower their security settings for macros (allowing self-
referential code), which is a major downside in today's
world of macro viruses. Also, it sometimes fails with a
namespace conflict, even though solver.xla is not active.

2. status = Application.Run("SolverSolve", True)
instead of:
status = SolverSolve(UserFinish:=True)

Using Application.Run() doesn't solve the problem... the
call still fails unless the reference is set.

3. Set obj = CreateObject("Excel.Solver")
or: Set obj = CreateObject("Excel.Solve")
or: Set obj = GetObject(,"Excel.Solver")
or: Set obj = GetObject(Application.LibraryPath
& "SolverSolver.xla")
etc. etc. etc.

I have not been able to find a proper incantation of
CreateObject() or GetObject() parameters that return an
object in which I can invoke the SolverSolve
function/method.

I even tried a few zanier things to no avail.

Please help! How can I write portable code that calls
SolverSolve and works with BOTH Excel XP and Excel 2000
(without requiring my macro code file to be copied into
the same directory as solver.xla)?

Any assistance is greatly appreciated.
Chris Russell

I'm using an external .dll, and like the side-effect that if I try to use
it on an excel which doesn't have the .dll installed, the
spreadsheet won't run, but goes into a compile error cycle, and has
to be killed by the task manager. however, it would be better to be a bit
less brutal, and detect the absence of a reference to the .dll.
so I put a search of Application.VBE.ActiveVBProject.References into
auto-open(), with an immediate End if the name of the .dll isn't found.
unfortunately I still get the compile error cycle, even if excel has
all its tool->options->general compile options unticked. is this
something for which there is a workaround?

thanks,
Eric

I am using Access to Excel automation. The first time I run the code below, Excel hangs up on "Microsoft Visual Basic - VBAProject". To continue, I have to go to File and Select "return to excel". From then on the code works fine. I have stripped all the code that is not relevent like move data to cells.

I would appreciate any help that is provided.

DoCmd.Maximize
On Error Resume Next
Set dbs = CurrentDb()
Dim Ref As Reference
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
On Error Resume Next
'When using AddFromGUID, you can use zero for the major and minor versions to pick the latest.
ActiveWorkbook.VBProject.References.AddFromGuid "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 0, 0
Application.VBE.ActiveVBProject.References.AddFromGuid "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 0, 0
On Error GoTo 0
'xlApp.Visible = True
xlapp.Workbooks.Add
xlapp.Visible = False
Set sht = xlapp.ActiveWorkbook.Sheets(1)
Dim TQOrd As Double
Dim TQShp As Double
Dim TExt As Double
Set rst = dbs.OpenRecordset("tblShopScheduleRevExcelInput", dbOpenDynaset)
' Loop through the a table and copy them to worksheet.
GoTo CloseWS
CloseWS:
'GoTo MonitorEnd
AddReference
Dim cm As VBIDE.CodeModule
Dim x As Long
Set cm = sht.Parent.VBProject.VBComponents("ThisWorkBook").CodeModule
x = cm.CreateEventProc("Open", "Workbook")
I = 1
Set rst = Nothing
Set rst = dbs.OpenRecordset("tblAddExcelRefCode", dbOpenDynaset)
' Loop through the a table and copy them to worksheet.
Do Until rst.EOF
cm.InsertLines x + I, Trim(rst!codeline)
I = I + 1
rst.MoveNext
Loop
'Add Code
On Error Resume Next
Kill "c:tempExcel.xls"
On Error GoTo 0
sht.Parent.SaveAs "c:tempExcel.xls"
sht.Application.ActiveWorkbook.Close
xlapp.Quit
Set sht = Nothing
Set cm = Nothing
Set rst = Nothing
Dim objActiveWkbk As Object
Dim objActiveWksh As Object
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
objXL.Application.Workbooks.Open "c:tempExcel.xls"
Set cm = Nothing
Set sht = objXL.ActiveWorkbook.Sheets(1)
Set cm = sht.Parent.VBProject.VBComponents("ThisWorkBook").CodeModule
x = cm.CreateEventProc("WindowDeactivate", "Workbook")
I = 1
Set rst = Nothing
Set rst = dbs.OpenRecordset("tblAutoBackupCode", dbOpenDynaset)
' Loop through the a table and copy them to worksheet.
Do Until rst.EOF
cm.InsertLines x + I, Trim(rst!codeline)
I = I + 1
rst.MoveNext
Loop
''Add Code
Set cm = Nothing
''Now This workbook
If Len(Trim(mpDoingCompany)) = 0 Then
mpDoingCompany = "EIM"
End If
On Error Resume Next
Kill "c:" & mpDoingCompany & "ShopScheduleFormated.xls"
Kill "c:" & mpDoingCompany & "ShopSchedule.xls"
On Error GoTo 0
sht.Parent.SaveAs "c:" & mpDoingCompany & "ShopScheduleFormated.xls "
sht.Application.ActiveWorkbook.Close
objXL.Quit
xlapp.Quit
MonitorEnd:
Set sht = Nothing
Set objXL = Nothing
Set cm = Nothing
DoCmd.TransferSpreadsheet acExport, 8, "tblShopScheduleRevExcelInput", "c:" & mpDoingCompany & "ShopSchedule"
DoCmd.Close acForm, "frmEmersonScheduleWS"
DoCmd.OpenForm "frmReportCenter"
DoCmd.Maximize
End Sub

Hello,
I am working on trying to manage code module updates for a distributed excel project. I am trying to code the following flow:
1. User clicks button to install updates from master workbook
2. Master workbook modules are export to local filesystem
3. Loop through the exported modules deleting the local and importing the updated

I want to be able to do this without having to open the master workbook. The path to the master workbook is accessible to the users on a network filesystem. I am getting a subscript error with the following. Is there another way to reference the VBProject? Thank you.

Code:
Private Sub getUpdates(m_type As String)
    Dim N As Long
    
    mpTempFilepath = VBA.Environ$("temp") & Application.PathSeparator & "PSTSTModules" & Application.PathSeparator
    If Dir(mpTempFilepath, vbDirectory) = vbNullString Then
        On Error GoTo CreateDirectoryError:
        VBA.MkDir (mpTempFilepath)
    End If

    On Error GoTo RefError
    With 
Application.VBE.VBProjects("H:MASTER_v2.xls") 'Getting error here
        For N = 1 To .VBComponents.count
            'Debug.Print .VBComponents(N).Name & " " & .VBComponents(N).Type
            If (m_type = "mod") Then
                If .VBComponents(N).Type = vbext_ct_StdModule Then
                    On Error GoTo ExportModuleError:
                    Application.VBE.ActiveVBProject.VBComponents(N).Export (mpTempFilepath & .VBComponents(N).Name & ".bas")
                End If
            ElseIf (m_type = "frm") Then
                 If .VBComponents(N).Type = vbext_ct_MSForm Then
                    On Error GoTo ExportModuleError:
                    Application.VBE.ActiveVBProject.VBComponents(N).Export (mpTempFilepath & .VBComponents(N).Name & ".bas")
                End If
            End If
        Next N
    End With
    
CreateDirectoryError:
    If (Err  0) Then
        Globals.raiseException ("Error creating local module directory!" & " : " & mpTempFilepath)
    End If
    
ExportModuleError:
    If (Err  0) Then
        Globals.raiseException ("Error exporting module!")
    End If
    
RefError:
    MsgBox Err.Description

End Sub


John,

This should solve it...

If it cannot find a valid reference:
It queries the registry for the installed version of outlook.
then looksup the typelibrary and sets a reference.

(you cant use excel's version e.g. i have excel 11 but use
outlook 10)

It doesnot require a reference to VBIDE.
but user MUST allow access to project in macro security

You may have to add some errorhandling...

Option Explicit

'ADVAPI32
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
Alias "RegOpenKeyExA" ( _
ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, _
phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
Alias "RegQueryValueExA" ( _
ByVal hKey As Long, ByVal lpValueName As String, _
ByVal lpReserved As Long, lpType As Long, lpData As Any, _
lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" ( _
ByVal hKey As Long) As Long

Function getRegistry(sName$) As String
Dim lKey&, lBuf&, sBuf$, lRes&
Const HKEY_CLASSES_ROOT = &H80000000
Const RegAccessRead = &H20019

lRes = RegOpenKeyEx(HKEY_CLASSES_ROOT, sName, 0&, RegAccessRead, lKey)
If lRes = 0 Then
lBuf = 255
sBuf = Space(lBuf)
lRes = RegQueryValueEx(lKey, "", 0&, &H1, ByVal sBuf, lBuf)
lRes = RegCloseKey(lKey)
getRegistry = Left(sBuf, lBuf - 1)
End If
End Function

Sub CheckAndSetReference()
Dim ref As Object
Dim bOK As Boolean
Dim sGuid As String

With Application.VBE.ActiveVBProject
On Error Resume Next
Set ref = .References("Outlook")
On Error GoTo 0
If Not ref Is Nothing Then
If ref.IsBroken = False Then
bOK = True
Else
.References.Remove ref
End If
End If

If Not bOK Then
sGuid = getRegistry("Outlook.ApplicationCLSID")
sGuid = getRegistry("CLSID" & sGuid & "Typelib")
If sGuid = "" Then
MsgBox "Cant add reference to Outlook object Library"
Else
.References.AddFromGuid sGuid, 0, 0
End If
End If
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"JohnV" > wrote:

> I have a spreadsheet that will be used by different
> Admins. As they have varying levels of technical
> knowledge I would like to be able to check the references
> when the workbook is opened and see if the references
> to "Microsoft Outlook X.X Library" is checked. As the
> users can be using Office 97, 2000 or XP and I am not sure
> exactly what reference I am looking for; I probably need
> to check for all 3. Additionally, I am not sure where the
> reference library would be kept, so I would like to refer
> to the Library name rather than the location.
>
> Could anybody help me with this?
>
> Thanks,
> JohnV

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


	VB:
	
Application.VBE.ActiveVBProject.VBComponents.Import ("DOMAIN2PrivateUTPCandidate TrackingVBComponentsUserForm3.frm") 
 
Application.VBE.ActiveVBProject.VBComponents.Import ("DOMAIN2PrivateUTPCandidate TrackingVBComponentsUserForm4.frm") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
These commands for importing user forms used to work fine. The path really does have those forms in it, however IS switched me to Terminal Server 2003. My security is set to "Trust access to Visual Basic Project", so that is not the problem in this case. The error message tells me to go to the log to see the problem, and it says the form is in use so it can't be imported. However I don't have the form open, and the import used to work with this exact code.

Perhaps my library references got re-set when they changed me to Terminal Server 2003. These are the ones that are checked off:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Outlook 11.0 Object Library
Microsoft Office Outlook View Control
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Word 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.0 Library

Hi,

I would like to distribute an XP Excel/VBA application I have written. To do this I need the application to be able to access the Application.VBE.ActiveVBProject object to do some configuration checking/setting.

However, if I run the code shown below, this indicates that when I open the spreadsheet, the ActiveVBProject is Solver. (My application does use Solver from a VBA macro.) As Solver is the active VBA project my checks and sets don't work properly. If I then open my project using the VB Editor from Excel the active project changes to my project.

What I really need is to have the default project set to my project when the Spreadsheet is opened. Does anyone know if this can be done?

On one of the search engines I found a reference to a post along these lines at:

http://www.microsoftexceltraining.co...d.php?tid=4637

but I can't find this reference now this stuff has moved to OzGrid.

Any suggestions?

Thanks in advance

David

Private Sub Workbook_Open()

MsgBox Prompt:="Active project name is " + Application.VBE.ActiveVBProject.Name, Buttons:=vbCritical

'Check and set configurations

End Sub

Hello, before I terminate the formating of my file, I want to change the
name of the vbaProject. using the following code:

'name the vbaProject
Application.VBE.ActiveVBProject.Name = "Consolidated_Monthly_FB"

I am not adding any module or reference to the file. I am getting Run-time
error 50289, can't perform operation since the project is protected.
Please, be assure that the project is not protected at this point. If this
can help, when I open the vba editor, I have no error, it terminates as it
should be.
Further I also added on the template (initial unformatted file) the VBA
Extensibility 5.3, just in case, but to no avail.
Is there a solution?

Daniel

I posted this to the Word programming newsgroup, too, in hopes of getting
someone who knows both applications well enough to answer.

I wrote a set of macros that modify a text file and import the result into
Excel. Once in Excel, more formatting changes are made, and finally I want
to copy three modules into the target's VBE. These modules provide for a
custom outline expansion, and the code for these works once in Excel.

I am having trouble with the copying. I took my method from Chip Pearson's
"Programming to the VBE" website, but I've noticed that Word seems to use the
VBE differently than Excel. I either get a message that says "Subscript out
of Range" or it fails to copy the modules completely.

Here is the code:

Sub addCollapse(xlApp)

Dim fname As String
Dim fso
Dim VBCodeMod As CodeModule
Dim VBComp As VBComponent
Dim LineNum As Long

Application.Activate

' Search for "collapse" modules and transfer code to target
For Each Module In Application.VBE.ActiveVBProject.VBComponents
If Module.Type = 1 And (Module.Name = "copy_collapse_functions" Or _
Module.Name = "copy_collapse_main" Or _
Module.Name = "copy_collapse_hide") Then
' Export module to text file
fname = ActiveDocument.Path & "" & Module.Name & ".txt"
Module.Export pthVisual & Module.Name & ".txt"
Module.Export fname

' Import module into spreadsheet
xlApp.ActiveWorkbook.VBProject.VBComponents.import fname

' Delete text file
Set fso = CreateObject("scripting.filesystemobject")
fso.deletefile fname
End If
Next

' Rename modules
xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_main").Name =
"collapse_main"

xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_functions").Name =
"collapse_functions"
xlApp.ActiveWorkbook.VBProject.VBComponents("copy_collapse_hide").Name
"collapse_hide"

xlApp.Application.Run "collapse"

End Function

In the code, xlApp refers to the Excel Application. I have also found that
if I step through the code line by line, it works correctly. I can't figure
out why it would work using F8 and not when just run by itself. I have that
error on other parts of the macro, too, but I've usually found a workaround.

Is there a reason for this?

Pflugs

I found this code that uses sendkeys for unlocking/locking your VBE Project :

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
Dim vbProj As Object

Set vbProj = WB.VBProject

'can't do it if already unlocked!
If vbProj.Protection <> 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

' now use lovely SendKeys to quote the project password
SendKeys Password & "~~"

Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Sub ProtectVBProject(WB As Workbook, ByVal Password As String)
Dim vbProj As Object

Set vbProj = WB.VBProject

'can't do it if already locked!
If vbProj.Protection = 1 Then Exit Sub

Set Application.VBE.ActiveVBProject = vbProj

' now use lovely SendKeys to set the project password

SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password & "~"

Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

WB.Save
End Sub

Also modifying Chip's information on removing/adding Subs from Projects, I
created this Sub:

Sub DeleteCodeLine(WB As Workbook, ModuleName, StartLine)
Dim VBCodeMod As CodeModule

Set VBCodeMod = WB.VBProject.VBComponents(ModuleName).CodeModule
With VBCodeMod
.DeleteLines StartLine, 1
End With

End Sub

I then have a commandbutton that Does this then: (where sheetname is the
other workbook I want to unprotect / reprotect

UnprotectVBProject Workbooks(sheetname), "abc"
DeleteCodeLine Workbooks(sheetname), "sheet28", 2
ProtectVBProject Workbooks(sheetname), "abc"

When I click the commandbutton it gives me an error:

Run-time error '50289':

Can't perform operation since the project is protected.

I know I'm doing something stupid wrong, but, I can't figure out what...
anyone care to take a stab at it?

Thanks!

-Kaisies


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