Results 1 to 10 of 10

Thread: Option Strict On disallows late binding

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Option Strict On disallows late binding

    Hi, everybody

    I am not sure this is the correct forum to ask my question. But any help can orient me towards the solution.

    I am developing a VB.NET Sub which can get data from SQL Server to my Excel Sheet at lightning speed (using ADODB connection).

    My Sub is written in VB.NET, not VBA. But I verified that it also works in VBA.

    Here is my code:

    Code:
    Option Strict On
    Imports System.Runtime.InteropServices
    Imports System.Text
    Imports System.ComponentModel
    Imports AddinExpress.MSO
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Data.DataSet
    Code:
    Private Sub AdxRibbonButton1_*******(ByVal sender As System.Object, ByVal control As AddinExpress.MSO.IRibbonControl, ByVal pressed As System.Boolean) Handles AdxRibbonButton1.*******
    
    
            Dim Conn As New ADODB.Connection
            Dim recset As New ADODB.Recordset
            Dim sqlQry As String, sConnect As String
            Dim xlWb As Excel._Workbook
            Dim xlWsht As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
    
            xlWsht.Cells.ClearContents()
    
            sqlQry = "EXECUTE[dbo].[MyStoredProcedure]"
    
            sConnect = "Driver=SQL Server;Server=MyServer; Database=MyDatabase; User Id = sa; Password= 12345"
    
            Conn.Open(sConnect)
            recset = New ADODB.Recordset
    
            recset.Open(sqlQry, Conn)
    
            Dim icols As Integer
            For iCols = 0 To recset.Fields.Count - 1
    
     '//------------ MY PROBLEM IS HERE --------------------------------
                xlWsht.Cells(1, icols + 1).value = recset.Fields(icols).Name
    
    '// -----------------------------------------------------------------------
            Next
                
           
            xlWsht.Range("A2").CopyFromRecordset(recset)
    
            recset.Close()
    
            Conn.Close()
            recset = Nothing
    
        End Sub


    MY PROBLEM

    My code works perfectly if I set Option Strict OFF.

    But if I set Option Strict On, there is an error at this line:
    Code:
    xlWsht.Cells(1, icols + 1).value = recset.Fields(icols).Name
    Info: This line of code adds the headers which are missing because "CopyFromRecordset" does not copy headers.

    The error Message is:
    Option Strict On disallows late binding

    Can anybody help me write the above line correctly?

    Thanks
    Leon
    Last edited by leonlai; May 15th, 2019 at 04:26 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Strict On disallows late binding

    Hi, everybody

    I am working hard on my problem.

    I found that the following codes work perfectly, and produce the expected results:


    Code:
    xlWsht.Range("A1").Value = recset.Fields(1).Name
    xlWsht.Range("B1").Value = recset.Fields(2).Name
    xlWsht.Range("C1").Value = recset.Fields(3).Name
    xlWsht.Range("D1").Value = recset.Fields(4).Name
    xlWsht.Range("E1").Value = recset.Fields(5).Name
    xlWsht.Range("F1").Value = recset.Fields(6).Name
    xlWsht.Range("G1").Value = recset.Fields(7).Name
    But I want to parametrize the codes.
    As I mentioned, I tried:

    Code:
    For iCols = 0 To recset.Fields.Count -1
         xlWsht.Cells(1, iCols +1).Value = recset.Fields(iCols).Name
    Next
    It is this code which does not work and that I need to correct.

    Now that I have simplified my problem, I hope someone will reply.

    Thanks
    Leon
    Last edited by leonlai; May 15th, 2019 at 09:04 AM.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,977
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Option Strict On disallows late binding

    Leon

    Which part of the code isn't working?


    Is it xlWsht.Cells(1, iCols +1).Value ?

    Is it recset.Fields(iCols).Name ?
    If posting code please use code tags.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,338
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Option Strict On disallows late binding

    It is possible that using the Excel model in VB.NET does not give you access to ranges through a Cells() reference. Also in keeping with the general syntax of VB.NET, it is also likely that you should be using brackets, not parenthesis.

    So you can try:
    xlWsht.Cells[1, iCols +1].Value = recset.Fields[iCols].Name

    Or otherwise keep trying other experiments. Do not assume that VBA and VB.NET are the same - they are quite different. You have to search for examples of programming Excel with VB.NET, not with VBA. For what it's worth, I prefer to use a more lightweight way of interacting with Excel spreadsheets in .NET. So for, instance, I would use something like EPPlus (https://github.com/JanKallman/EPPlus). Basically, you don't need full-blown excel to create simple spreadsheets. But anyway, that's my two pennies.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Strict On disallows late binding

    Hi, Norie

    Thanks for your reply.

    It is xlWsht.Cells(1, iCols +1).Value which is highlighted as error if I set Option Strict on:
    " Option Strict on disallows late binding"

    Please note that if I set Option Strict Off, the program works perfectly.
    However, I do not want to Set Option Strict Off. I will go crazy if I do.

    Best Regards,
    Leon
    Last edited by leonlai; May 16th, 2019 at 02:17 AM.

  6. #6
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Strict On disallows late binding

    Hi, xenou

    Thanks for your reply.

    Do not assume that VBA and VB.NET are the same.
    Yes, they are not exactly the same. But often, VBA can be converted to VB.NET with only some slight changes.
    I use this approach, because it is easier to develop solutions in, and get help on VBA.
    For example, the code which I have posted was originally written in VBA, and I found it works well with VB.NET.
    The only problem is the one I raised.

    I never heard about EPPPlus. Thanks for sharing the tip. I'll see if I can use it.

    Best Regards,
    Leon
    Last edited by leonlai; May 16th, 2019 at 02:29 AM.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,338
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Option Strict On disallows late binding

    Okay. Well, in this case this is one of the differences. You will have to use an explicit type (no late binding) or go with option strict off.
    https://docs.microsoft.com/en-us/dot...c/misc/bc30574

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Strict On disallows late binding

    Hi, everybody

    Thanks to everybody who replied.

    I am closing this thread.

    Leon

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,977
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Option Strict On disallows late binding

    Leon

    Did you find a solution?

    If you did can you share it?
    If posting code please use code tags.

  10. #10
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Option Strict On disallows late binding

    Hi, Nori

    The lines which gave me a headache for 3 days and nights should be written like this:

    Code:
     '// COPY HEADERS FROM RECORDSET
    For icols = 0 To recset.Fields.Count - 1
         TryCast(ActivShtCels(1, icols + 1), Excel.Range).Value = recset.Fields.Item(icols).Name
    Next
    With this correction, the code works perfectly, and I think I've got the fastest possible way to retrieve data from SQL Server. It retrieved 5000 rows from my Stored Procedure, just 2 seconds after pressing my button!

    And the nice thing is it is a disconnected model (does not block traffic) and the recordset can be deleted asap (releases memory)!

    The code is not mine, but copied from the foll. sites and amended to suit my requirements:

    To retrieve the data (without headers) from SQL Server:
    http://excelerator.solutions/2017/08...cel-sql-query/

    To copy the (missing) headers:
    https://docs.microsoft.com/en-us/off...yfromrecordset

    However, please note that I am not using VBA, but VB .NET in Visual Studio.
    I often ask questions on VBA Forums as a first step, and then modify the solutions slightly to suit VB. NET.

    However, the solutions presented in the 2 above sites work perfectly with VBA. Same lightning speed! I am amazed! No looping! Just CopyfromRecordset (unfortunately, it doesn't copy the headers).

    Keep posting!

    Best Regards,
    Leon
    Last edited by leonlai; May 20th, 2019 at 02:28 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •