Use VBA to install Excel Microsoft Scripting Runtime Reference

jbuist

New Member
Joined
Mar 16, 2018
Messages
29
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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"
 
Upvote 0
'Doh! Thank you Domenic. However, now I'm getting the following error: Programmatic access to Visual Basic Project not trusted. Any ideas?
 
Upvote 0
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
   
[B]   'Create a TextStream.[/B]
   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
 
Upvote 0
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
 
Upvote 0
'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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top