[edit]: Answered a small part of my own question, but the biggest issue remains...how to get it to properly activate/import the reference when it isn't activated.
The GUID string I'd need for the method of ThisWorkbook.VBProject.References.AddFromGuid
is: "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}", 1, 0
However, using the method & example format given by the microsoft help file, I keep getting an error that it expects an = sign
[end edit]
I've done quite a bit of searching on the internet to accomplish this, and the stuff I've seen has mostly just confused me. I looked at using the method of adding a reference by GUID, adding it by filename, etc.
Basically, I want to be sure that the VBA project will automatically select to use the given Reference (that otherwise, the user would have to Alt+F11, then go to Tools-->References. Then they'd have to find the reference in the list, and add it. I'm trying to simplify things for the user, and the step of having to add the given reference requires a lot more from the user than I want.
I want all my code to be highly automated for people who may not be all that computer literate---basically I keep trying to make everything more automated.
The reason filelist.blahexe had to have that extension is to allow for the email server to allow the sending/receiving of the zip file (the server blocks .exe extensions). What needs to end up happening is that filelist.exe ends up needing to be placed in C:\temp\
So...either the user will have to rename filelist.blahexe to filelist.exe, or my program will do this for them.
So the catch is that the user would have to ADD THE reference (mentioned earlier)...before my code would run. Otherwise they're just going to get an error...which is something I want to avoid at all costs--otherwise the person might be like "OMG an error!"
So I need to figure out how to add the reference automatically.
See my code below, please.
CODE
Public Sub Run_Input_Formv3_experimental()
'NEED TO ADD CODE TO AUTOMATICALLY ADD Reference to Windows Script Host Object Model!!!!!!!!!!
'Location is C:\Windows\system32\wshom.ocx
' one way to maybe have it find windows system32 directory: \windows\system32
'Dim Reference As Object
'With ThisWorkbook.VBProject
' For Each Reference In .References
' If Reference.Description Like "Windows Script Host Object Model*" Then
' Exit Sub
' End If
' Next
' .References.AddFromFile "C:\Windows\System32\wshom.ocx"
'End With
'On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile "c:/windows/system32/wshom.ocx"
Dim EXE_Filelist As String, BlahEXE_Filelist As String
Dim FolderNotExistMsg, FolderPath As String, FileNotExistMsg
Dim SourcePath, RenamePath
Dim FileSysObj As FileSystemObject 'NEED THIS for use in ___ .FolderExists(FolderPath)
'and __________.FileExists(<generic file path>)
' Note: the help file says FileSystemObject.FolderExists(folderpath)
' But FileSystemObject must be replaced with a variable name
' initialized in a dim statement as a FileSystemObject
' This is what the above line of code accomplishes
CheckFilelistExt:
Set FileSysObj = New FileSystemObject
FolderPath = "C:/Temp/"
EXE_Filelist = "C:\Temp\filelist.exe"
BlahEXE_Filelist = "C:\Temp\filelist.blahexe"
If FileSysObj.FolderExists(FolderPath) = False Then 'NOTE THE NEED FOR THE DIM STATEMENT
'ABOVE (dim FileSysObj As FileSystemObject
'I could have named it anything (FSO is
'a common name)
FolderNotExistMsg = MsgBox("The directory ""C:\Temp\"" does not exist!" _
& " Please ensure you created this directory & extracted the necessary" _
& " files! These files are located in the .zip file and should include: " _
& vbNewLine & "filelist.blahexe and" & vbNewLine & "Disc project Readme.txt" _
& vbNewLine & vbNewLine & "After extracting filelist to C:\Temp\, " _
& "please rename the file from filelist.blahexe to filelist.exe" & vbNewLine _
& "My program SHOULD rename it for you--if you have the filelist.blahexe IN C:\Temp\" _
& "Click OK to quit. After you fix the problem, run this code again", vbOKOnly)
If FolderNotExistMsg = vbOK Then
End
Else
End
End If
Else
If FileSysObj.FileExists(EXE_Filelist) = True Then
GoTo LoadForm
Else
If FileSysObj.FileExists(BlahEXE_Filelist) = False Then
FileNotExistMsg = MsgBox("Neither filelist.exe or filelist.blahexe " _
& "exist in the folder C:\Temp\" & vbNewLine & "Please ensure that " _
& "either filelist.exe or filelist.blahexe are copied to C:\Temp" _
& vbNewLine & vbNewLine & "Please fix the problem after clicking OK." _
& vbNewLine & "After you fix the problem, simply run this code again.", vbOKOnly)
If FileNotExistMsg = vbOK Then
End
Else
End
End If
Else
Name BlahEXE_Filelist As EXE_Filelist
GoTo CheckFilelistExt
End If
End If
End If
LoadForm:
Load InputExperiment
InputExperiment.Show
Unload InputExperiment
This post has been edited by cmount: 20 Aug, 2008 - 12:46 PM
| 