KeithRoberts
12-08-2008, 08:24 AM
I found some great code that allows you to list all the references selected in an Excel module and adapted it to MS Project. I know that I have to have a certain number of references selected (added) in order to have the VBA to run.
The problem that I am running into is that I can only add one reference in the code. If I use a For-Next loop or sequential coding, I cannot add a second reference if it exists.
The code that I am using is as follows:
sReference = "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB"
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
sReference = "C:\WINDOWS\system32\scrrun.dll"
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
I also tried
For lcntr = 1 to 2
Select Case lcntr
Case 1
sReference = "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB"
Case 2
sReference = "C:\WINDOWS\system32\scrrun.dll"
End Select
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
Next lcntr
I cannot use GUID as I use the MS Excel 11 Library reference. If I use the GUID, it selects the MS Excel 5 Object LIbrary instead of MS Excel 11 Library. :banghead: :help
The problem that I am running into is that I can only add one reference in the code. If I use a For-Next loop or sequential coding, I cannot add a second reference if it exists.
The code that I am using is as follows:
sReference = "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB"
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
sReference = "C:\WINDOWS\system32\scrrun.dll"
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
I also tried
For lcntr = 1 to 2
Select Case lcntr
Case 1
sReference = "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB"
Case 2
sReference = "C:\WINDOWS\system32\scrrun.dll"
End Select
Err.Clear
ActiveProject.VBProject.References.AddFromFile sReference
'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 add or remove a reference in this file" & vbCrLf & _
"Please check the references in your VBA project!", _
vbCritical + vbOKOnly, _
"Error!"
End Select
On Error GoTo 0
Next lcntr
I cannot use GUID as I use the MS Excel 11 Library reference. If I use the GUID, it selects the MS Excel 5 Object LIbrary instead of MS Excel 11 Library. :banghead: :help