option strict on disallows late binding

I am still learning visual basic.  I have two functions in an application that i am running without the option strict on.  When I turned option strict on, I get the same error (disallows late binding).  I typically am able to use option strict on and be fine, but this function will not work with it on.  Any suggestion?
Function GetBoiler(ByVal sFile As String) As String
        Dim fso As Object
        Dim ts As Object
        fso = CreateObject("Scripting.FileSystemObject")
        ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close()
    End Function

    Function RangetoHTML(rng As Excel.Range) As String
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Excel.Workbook
        'Dim xlApp As New Excel.Application
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

        'Copy the range and create a new workbook to past the data in
        rng.Copy()

        TempWB = xlApp.Workbooks.Add(1)

        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial(Paste:=8)
            .Cells(1).PasteSpecial(-4163, , False, False)
            .Cells(1).PasteSpecial(-4122, , False, False)
            .Cells(1).Select()
            xlApp.CutCopyMode = CType(False, Excel.XlCutCopyMode)
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete()
            On Error GoTo 0
        End With

        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=4, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=0)
            .Publish(True)
        End With

        'Read all data from the htm file into RangetoHTML
        fso = CreateObject("Scripting.FileSystemObject")
        ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close()
        RangetoHTML = String.Format("{0}", Replace(CStr(RangetoHTML), "align=center x:publishsource=", _
                              "align=left x:publishsource="))

        'Close TempWB
        TempWB.Close(SaveChanges:=False)

        'Delete the htm file we used in this function
        Kill(TempFile)

        ts = Nothing
        fso = Nothing
        TempWB = Nothing
    End Function

Open in new window

Calvin-code-error.PNGCalvin-code-error1.PNG
cmedTechnical AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Are you learning Visual Basic for Applications (VBA), or are you learning VB.NET?
0
cmedTechnical AnalystAuthor Commented:
vb.net
0
käµfm³d 👽Commented:
If this is VB.NET, then there's no need to be using FileSystemObject and CreateObject. Use the built-in classes for interacting with the file system. These classes are found under the System.IO namespace. From what I see above, you'll want either the StreamReader class, or the Shared method File.ReadAllText.

e.g.

Function GetBoiler(ByVal sFile As String) As String
    Return System.IO.File.ReadAllText(sFile)
End Function

Open in new window


That should take care of your late-binding issues since this all becomes compile-time stuff.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

cmedTechnical AnalystAuthor Commented:
@käµfm³d

 Do you have any suggestion for the second function?
0
käµfm³d 👽Commented:
Those I think you might be stuck with. The reason being that .NET inter-operates with Excel (and all other Office products) through what is known as a COM layer. Pretty much anything COM is going to be late bound. As far as I can see in the documentation, you cannot use late-binding and Option Strict together. You would have to turn Strict off.

There was rumor a year or two back that Office could be switching to .NET behind the scenes, but that has yet to happen.
0
CodeCruiserCommented:
You may be able to fix that issue by changing

        TempWB = xlApp.Workbooks.Add(1)

        With TempWB.Sheets(1)

to

        TempWB = xlApp.Workbooks.Add(1)
        Dim TempWS As Worksheet = TempWB.Sheets(1)
        With TempWS
0
cmedTechnical AnalystAuthor Commented:
It did not work, but it gave me an idea to use this in the code  
TempWB = xlApp.Workbooks.Add(1)
        Dim tempsheet As Excel.Worksheet
        tempsheet = CType(TempWB.ActiveSheet, Excel.Worksheet)
        Dim temprange As Excel.Range
        temprange = CType(tempsheet.Cells(1), Excel.Range)

        With TempWB.Sheets(1)
            temprange.PasteSpecial(Paste:=CType(8, Excel.XlPasteType))
            temprange.PasteSpecial(CType(-4163, Excel.XlPasteType), , False, False)
            temprange.PasteSpecial(CType(-4122, Excel.XlPasteType), , False, False)
            temprange.Select()
            xlApp.CutCopyMode = CType(False, Excel.XlCutCopyMode)
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete()
            On Error GoTo 0
        End With

        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=CType(4, Excel.XlSourceType), _
             Filename:=TempFile, _
             Sheet:=tempsheet.Name, _
             Source:=tempsheet.UsedRange.Address, _
             HtmlType:=0)
            .Publish(True)
        End With

        'Read all data from the htm file into RangetoHTML
        fso = CreateObject("Scripting.FileSystemObject")
        ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close()

        RangetoHTML = String.Format("{0}", Replace(CStr(RangetoHTML), "align=center x:publishsource=", _
                              "align=left x:publishsource="))

        'Close TempWB
        TempWB.Close(SaveChanges:=False)

        'Delete the htm file we used in this function
        Kill(TempFile)

        ts = Nothing
        fso = Nothing
        TempWB = Nothing
    End Function

Open in new window


I am still running into problem with the option strict with this part of the code
 .DrawingObjects.Visible = True
            .DrawingObjects.Delete()

Open in new window

Dim ts As Object = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close()

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.