reference Microsoft Scripting Runtime via code?

[Mister H]

Hi All:

I have a macro that was given to me by a Board Member but in order to run it you need to reference Microsoft Scripting Runtime. Is there anyway to set this reference AUTOMATICALLY in the code itself?

Right now I have the macro in my Personal Module but would like to send it to others without having them go into VBA to set reference to: Microsoft Scripting Runtime. If there is a line of code that would turn on this feature I can place it at the beginning the code and then remove the reference at the end of the code.I did see this line of code in another Post but I am not certain it is what I am looking for???

Set fso = CreateObject("Scripting.FileSystemObject")

If you need to see the code for some reason let me know and I will post it.

THANSK to anyone that can assist,
Take Care,
Mark

 

[pcc]

this ashould do it, although you need to know where the ref is stored and alter path accordingly.

Sub test()
Application.VBE.ActiveVBProject.References.AddFromFile ("C:\WINNT\system32\scrrun.dll")

End Sub

 

[Mister H]

Hi PCC:

THANKS I just saw your reply. Do I need to shut that down after the Macro is run or will it turn itself off after the spreadsheet is closed? I noticed when I go into VBA\TOOLS\REFERENCES and turn it on it seems to be only temporary. Sorry if it is a stupid questions but I do not even know what this reference does other then makes the macro I am using run without error. If I need to turn it off then how would I accomplish that?

Lastly my file is in a different location. It is in C:\WINDOWS\system32

Is there a way of stating:

IF C:\WINNT\system32\scrrun.dll does not exist then check to see if
C:\WINDOWS\system32 exist? I only ask in case this spreadsheet winds up being used in a different location I am uncertain as to what operating system they use.

THANKS Again. I am will try out your code shortly

Take Care,
Mark

 

[Legacy 98055]

If you use the VBE Library (Visual Basic for Applications Extensibility), the user will be required to set permissions for accessing the VB Project. This is not the default setting so in most cases it will be a required action which is not a good idea to begin with. It is safe to set an early bound reference to the scripting runtime. It is a standard component with Windows 2000 +, Internet Eplorer 5.5 +, and Office XP +. There is no benefit to late binding because if the library is there, rather you late bind or early bind, it will work. Visa versa.

 

[Norie]

Mark

Are you sure you need the reference?

As far as I can see the line of code you've posted wouldn't require it.

Mind you perhaps the rest of the code does.

 

[Mister H]

THANKS to ALL that replied. ​

I am going to post the code that I have. Maybe when you see it you can tell me if there is a workaround. As I mentioned I am clueless as to what the unction does I just know that I was told to Activate it and prior to activating it I was getting an error when the 2nd code ran. Here are the codes I am using to allow the user to:​

Select A Folder and the gives links to all files within that folder and all its sub folders. The results returned are:​

Columns A) File Name (as a link)
Columns B) Folder
Columns C) File Path
Columns D) File Type
Columns E) Date of File​

Basically I want to be able to place this into a spreadsheet so I can send it out to others.​

Option Explicit 
[LEFT]Dim lRow As Long[/LEFT]

Sub GetBasicFolder()  

[LEFT]'Microsoft Scripting Runtime references!
 
[LEFT]'Tools/References/Microsoft Scriping Runtime[/LEFT]
 
 
 
[LEFT] Dim wbkNew                As Workbook

 Dim wksSource             As Worksheet
[LEFT] Dim sFolderPath           As String
 Set wbkNew = Application.Workbooks.Add(Template:=xlWorksheet)
 Set wksSource = wbkNew.Worksheets(1)[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] lRow = 3

 With Application.FileDialog(msoFileDialogFolderPicker)
[LEFT]     .AllowMultiSelect = False
     .Title = "SELECT the FOLDER that you require a File Listing from and   then      Click OK:"
     .Show
     If .SelectedItems.Count = 0 Then
         Exit Sub
     Else
         sFolderPath = .SelectedItems(1)
     End If
 End With[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] With wksSource.Range("A1")

     .Value = "The files and subfolders list for " & sFolderPath
[LEFT]         With .Font
             .Bold = True
             .Size = 14
             .Underline = True
         End With
             With .Offset(2, 0).Resize(1, 5)
                 .Value = Array("FILENAME", "FOLDER", "FILE PATH", "FILE TYPE", "FILE DATE")
                 .Font.Bold = True
             End With
 End With
 CreateDocList sFolderPath, wksSource[/LEFT]
[/LEFT]

 
 
 
 
[LEFT]End Sub[/LEFT]
[/LEFT]

Sub CreateDocList(ByRef sFolderFullPath As String, _

[LEFT]               ByVal wksTemp As Excel.Worksheet)
 
 
[LEFT]'1. Have you checked (marked), a library Microsoft Scripting Runtime

'before starting? (in VBA goto)Tools/References/Microsoft Scripting Runtime - this library must be turn on - otherwise macro will show some errors.
[LEFT]'
 Dim FSO                   As FileSystemObject
 Dim objFolder             As Folder
 Dim objSubFolder          As Folder
 Dim objFile               As File
 With Application
     .ScreenUpdating = False
     .EnableEvents = False
 End With[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] Set FSO = New FileSystemObject

 Set objFolder = FSO.GetFolder(sFolderFullPath)[/LEFT]

 
 
 
 
[LEFT] For Each objFile In objFolder.Files

     If InStr(1, objFile.Type, "Microsoft") Or _
[LEFT]         InStr(1, objFile.Type, "Document") Or _
         InStr(1, objFile.Type, "Text") Then[/LEFT]
[/LEFT]

 
 
 
 
[LEFT]         With wksTemp.Range("A1").Offset(lRow, 0)

             .Value = objFile.Name
[LEFT]             .Hyperlinks.Add Anchor:=.Offset(0, 0), _
                             Address:=objFile.Path, _
                             TextToDisplay:=.Text
              .Offset(0, 1) = objFolder.Name
              .Offset(0, 2) = objFile.Path
              .Offset(0, 3) = objFile.Type
              .Offset(0, 4) = objFile.DateCreated[/LEFT]
[/LEFT]

 
 
 
 
[LEFT]         End With

                 lRow = lRow + 1
[LEFT]     End If
 Next objFile
 For Each objSubFolder In objFolder.SubFolders
     CreateDocList objSubFolder.Path, wksTemp
 Next objSubFolder[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] With wksTemp

     .Columns("B:E").AutoFit
[LEFT]     .Range("A:A").ColumnWidth = 47
 End With[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] With Application

     .ScreenUpdating = True
[LEFT]     .EnableEvents = True
 End With[/LEFT]
[/LEFT]

 
 
 
 
[LEFT] Set FSO = Nothing

 Set objFolder = Nothing
[LEFT] Set objSubFolder = Nothing
 Set objFile = Nothing
End Sub[/LEFT]
[/LEFT]

[/LEFT]

 

[Norie]

Mark

The code you've posted in your latest post doesn't seem to contain the line of code you posted in your first post.

In your latest code I would suggest you declare things as Object and replace this line with the line from your first post.

Set FSO = NewSystemFileObject

If you do that then you might not need to set the reference, either programatically or manually.

You can set references programatically but it can be tricky, especially if you are working with different setups.

By the way what is the purpose of the code - there might be some other approach.

 

[Mister H]

Hi Norie:

SORRY for the confusion...

In my first post I was just asking if there was a way to automatically reference Microsoft Scripting Runtime. The line of code I posted at first
Set fso = CreateObject("Scripting.FileSystemObject") was just a line of code that I found when searching "Scripting Runtime" on Mr Excel. I was just asking if that little snippet of code was what I needed to turn on the Scripting Runtime...

The other codes I posted are the actual codes I am using in my personal macros. When I run them it open a box to allow me to select a Folder. The code then runs and creates a list of ALL Files (with Hyperlinks) within the folder that I just selected. So beginning in cell A4 the results would be:

Cell A4 will contain the File Name (as a Hyperlink)
Cell B4 will contain the Folder name the file is in
Cell C4 will contain the Complete File Path (as text)
Cell D4 will contain the File Type
Cell E4 will contain the Date of the File

The only way I could get the codes to run without error was to reference Microsoft Scripting Runtime otherwise I got an error message when the CreateDocsList macro started to run. I think it had something to do with the code:
Dim FSO As FileSystemObject

I will try out your solution (suggestion) as soon as I get a chance.

THANKS Again,
Mark ​

 

[Norie]

Mark

No problem, I sort of understand what you asked in your first post.

You can programmatically set references but there are other methods you could use so you wouldn't need to.

Have a search for 'late-binding' - that might explain what I mean better than I can.