Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: reference Microsoft Scripting Runtime via code?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default reference Microsoft Scripting Runtime via code?

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

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

  2. #2
    Board Regular pcc's Avatar
    Join Date
    Jan 2003
    Location
    Derby, UK
    Posts
    1,332
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

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

    Code:
    Sub test()
    Application.VBE.ActiveVBProject.References.AddFromFile ("C:\WINNT\system32\scrrun.dll")
    
    End Sub
    Using Excel 2002

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,557
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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.
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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.


    Code:
    Option Explicit 
    
    Dim lRow As Long

    Code:
    Sub GetBasicFolder()  
    
    
    'Microsoft Scripting Runtime references!
    'Tools/References/Microsoft Scriping Runtime
    Dim wbkNew As Workbook Dim wksSource As Worksheet
    Dim sFolderPath As String Set wbkNew = Application.Workbooks.Add(Template:=xlWorksheet) Set wksSource = wbkNew.Worksheets(1)
    lRow = 3 With Application.FileDialog(msoFileDialogFolderPicker)
    .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
    With wksSource.Range("A1") .Value = "The files and subfolders list for " & sFolderPath
    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
    End Sub
    Code:
    Sub CreateDocList(ByRef sFolderFullPath As String, _
    
    
    ByVal wksTemp As Excel.Worksheet)
    '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.
    ' Dim FSO As FileSystemObject Dim objFolder As Folder Dim objSubFolder As Folder Dim objFile As File With Application .ScreenUpdating = False .EnableEvents = False End With
    Set FSO = New FileSystemObject Set objFolder = FSO.GetFolder(sFolderFullPath)
    For Each objFile In objFolder.Files If InStr(1, objFile.Type, "Microsoft") Or _
    InStr(1, objFile.Type, "Document") Or _ InStr(1, objFile.Type, "Text") Then
    With wksTemp.Range("A1").Offset(lRow, 0) .Value = objFile.Name
    .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
    End With lRow = lRow + 1
    End If Next objFile For Each objSubFolder In objFolder.SubFolders CreateDocList objSubFolder.Path, wksTemp Next objSubFolder
    With wksTemp .Columns("B:E").AutoFit
    .Range("A:A").ColumnWidth = 47 End With
    With Application .ScreenUpdating = True
    .EnableEvents = True End With
    Set FSO = Nothing Set objFolder = Nothing
    Set objSubFolder = Nothing Set objFile = Nothing End Sub

    Last edited by Mister H; Feb 5th, 2010 at 01:43 PM.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,557
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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.
    Code:
    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.
    If posting code please use code tags.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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



  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,557
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: reference Microsoft Scripting Runtime via code?

    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.
    If posting code please use code tags.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com