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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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
19,092
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
19,092
'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
19,092
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,089,492
Messages
5,408,596
Members
403,217
Latest member
dmcmaste

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top