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
 

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,919
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"
 

jbuist

New Member
Joined
Mar 16, 2018
Messages
29
'Doh! Thank you Domenic. However, now I'm getting the following error: Programmatic access to Visual Basic Project not trusted. Any ideas?
 

jbuist

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

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,919
'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:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,919
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:

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
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
 

jbuist

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

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top