CODEWITHSUNDEEP
excelvba
Sebastian Braun
Sebastian Braun

Reputation: 3

VBA Early Binding to Late Binding

I use this code to convert PDF Files to Excel.

Private Sub CommandButton2_Click()
    
    Dim setting_sh As Worksheet
    Set setting_sh = ThisWorkbook.Sheets("Tabelle1")
    
    Dim pdf_path As String
    Dim excel_path As String
    
    pdf_path = setting_sh.Range("E11").Value
    excel_path = setting_sh.Range("E12").Value
    
    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File
    
    Set fo = fso.GetFolder(pdf_path)
    
    Dim wa As Object
    Dim doc As Object
    Dim wr As Object
    
    Set wa = CreateObject("word.application")
    
    'Dim wa As New Word.Application
    wa.Visible = True
    'Dim doc As Word.Document
    
    Dim nwb As Workbook
    Dim nsh As Worksheet
    'Dim wr As Word.Range
    
    For Each f In fo.Files
        Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
        Set wr = doc.Paragraphs(1).Range
        wr.WholeStory
    
    
        Set nwb = Workbooks.Add
        Set nsh = nwb.Sheets(1)
        wr.Copy
    
        nsh.Paste
        nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))
    
        doc.Close False
        nwb.Close False
    Next
    
    wa.Quit
    
    MsgBox "Done"
    
End Sub

The problem is, other people also want to use this function, but they don't understand to how to add the Microsoft Scripting Runtime Reference in your VBA project. I tried to convert it to late binding, but it fails all the time. Can someone help me?

Thanks.

Upvotes: 0

Views: 589

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

  1. The next code will automatically add the necessary reference:
Sub addScrRunTimeRef()
  'Add a reference to 'Microsoft Scripting Runtime':
  'In case of error ('Programmatic access to Visual Basic Project not trusted'):
  'Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings->
  '         check "Trust access to the VBA project object model"
  Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\SysWOW64\scrrun.dll"
End Sub

The above code may fail if Trust access to the VBA project object model check box is gray out, being disabled by restricted administrative security policies. In such a case, the following solution should be recommended:

  1. To declare and set variables in Late binding way, declare and assign the objects in the next way:
Dim fso As Object, fo as Object, f as Object
set fso =CreateObject("Scripting.FileSystemObject")

It is also good to be known that Early binding is operationally faster than Late during run-time, and the programmer may benefit of intellisense suggestions, automatic capitalization of method names etc.

Upvotes: 0

Rory
Rory

Reputation: 34075

Change this:

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

to this:

Dim fso As Object
Dim fo As Object
Dim f As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Upvotes: 1

Related Questions