Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Use VBA to install Excel Microsoft Scripting Runtime Reference
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi all! I'm trying to activate the Microsoft Scripting Runtime Reference within my code as I'm using FSOs in other parts of the code. I don't get any errors, but it's not activating the reference either. The path below is my actual path. Any help is appreciated.

    Thanks in advance!

    Code:
    Sub Activate_Scripting_Runtime()
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\SysWOW64\scrrun\dll"
     
    End Sub

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi,
    Do you really need to install the microsoft sctipting runtime reference? You can use it in two ways:
    1. One way it's called early binding - here you need to turn on the reference and than in code you declare it as follows:
    Dim FSO as scripting.filesystemobject
    Set FSO = new scripting.filesystemobject
    2. Thw other way calles late binding where you do not need to turn on the reference and than you declare it as follows
    Dim FSO as object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    However, laye binding has two disadvantages:
    -> according to Microsoft late binding is a bit slower than early binding
    -> because of the dact that using late binding you declare it as object so intellisence will not work in such case so when you place a dot you won't see available methods so you have to be very accurate while programming.

    Personally, I often use late binding
    Regards,
    Sebastian

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    It looks like you have a typo. The last backslash (\) should be a dot (.)...

    Code:
    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\SysWOW64\scrrun.dll"

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    'Doh! Thank you Domenic. However, now I'm getting the following error: Programmatic access to Visual Basic Project not trusted. Any ideas?

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi Sebastian!

    I'm not familiar with binding. Below is a snippet of my code. Not sure where I would place it. Thanks!!

    Code:
    Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
    Range("A1").Select
       Dim FilePath As String
       Dim CellData As String
       Dim LastCol As Long
       Dim LastRow As Long
     
       Dim fso As FileSystemObject
       Set fso = New FileSystemObject
       Dim stream As TextStream
     
       LastCol = ActiveSheet.UsedRange.Columns.Count
       LastRow = ActiveSheet.UsedRange.Rows.Count
       
       'Create a TextStream.
       Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
     
       CellData = ""
     
       For i = 1 To LastRow
          For j = 1 To LastCol
             CellData = (ActiveCell(i, j).Text)
             stream.WriteLine CellData
          Next j
       Next i
     
       stream.Close
     
    End Sub

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi,
    Here I changed your code from early binding to late one. Now all fso methods will work without turning on the microsoft scripting runtime dll.
    Check it out now.

    Code:
    Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
    Range("A1").Select
       Dim FilePath As String
       Dim CellData As String
       Dim LastCol As Long
       Dim LastRow As Long
     
       Dim fso As Object
       Set fso = CreateObject("Scripting.FileSystemObject")
       Dim stream As TextStream
     
       LastCol = ActiveSheet.UsedRange.Columns.Count
       LastRow = ActiveSheet.UsedRange.Rows.Count
       
       'Create a TextStream.
       Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
     
       CellData = ""
     
       For i = 1 To LastRow
          For j = 1 To LastCol
             CellData = (ActiveCell(i, j).Text)
             stream.WriteLine CellData
          Next j
       Next i
     
       stream.Close
     
    End Sub

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Quote Originally Posted by jbuist View Post
    'Doh! Thank you Domenic. However, now I'm getting the following error: Programmatic access to Visual Basic Project not trusted. Any ideas?
    You'll need to allow access to the VBA project object model...

    Code:
    File >> Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Developer Macro Settings >> select/check 'Trust access to the VBA project object model'
    However, as Sebastian has already suggestion, I would use late binding instead.
    Last edited by Domenic; Aug 15th, 2019 at 04:29 PM.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi Sebastian, I just thought I would point out that you forgot to declare stream as Object...

    Code:
    Dim stream As Object
    Cheers!
    Last edited by Domenic; Aug 15th, 2019 at 04:44 PM.

  9. #9
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hi,
    Here's a fixed code, according to what Domenic mentioned.
    @Domenic-you're right, I forgot to chsnge declaration of stream to object

    Code:
    Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
    Range("A1").Select
       Dim FilePath As String
       Dim CellData As String
       Dim LastCol As Long
       Dim LastRow As Long
     
       Dim fso As Object
       Set fso = CreateObject("Scripting.FileSystemObject")
       Dim stream As Object
     
       LastCol = ActiveSheet.UsedRange.Columns.Count
       LastRow = ActiveSheet.UsedRange.Rows.Count
       
       'Create a TextStream.
       Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
     
       CellData = ""
     
       For i = 1 To LastRow
          For j = 1 To LastCol
             CellData = (ActiveCell(i, j).Text)
             stream.WriteLine CellData
          Next j
       Next i
     
       stream.Close
     
    End Sub

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    Hmmm....now I'm getting a Run-time error '5': Invalid procedure call or argument.

    Code:
    Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
    Range("A1").Select
       Dim FilePath As String
       Dim CellData As String
       Dim LastCol As Long
       Dim LastRow As Long
     
       Dim fso As Object
       Set fso = CreateObject("Scripting.FileSystemObject")
       Dim stream As Object
     
       LastCol = ActiveSheet.UsedRange.Columns.Count
       LastRow = ActiveSheet.UsedRange.Rows.Count
       
       'Create a TextStream.
       Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
     
       CellData = ""
     
       For i = 1 To LastRow
          For j = 1 To LastCol
             CellData = (ActiveCell(i, j).Text)
             stream.WriteLine CellData
          Next j
       Next i
     
       stream.Close
     
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

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
  •