asked on

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

kaufmed

Are you learning Visual Basic for Applications (VBA), or are you learning VB.NET?

cmed

ASKER

vb.net

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmed

ASKER

@käµfm³d

Do you have any suggestion for the second function?

SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nasir Razzaq

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

cmed

ASKER

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