Use VBA to install Excel Microsoft Scripting Runtime Reference

jbuist

New Member
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
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
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
'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
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
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
'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
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
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
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

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top