Option Strict On disallows late binding

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Leon

Which part of the code isn't working?


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

Is it recset.Fields(iCols).Name ?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
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.
 

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77

ADVERTISEMENT

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:

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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:

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
Hi, everybody

Thanks to everybody who replied.

I am closing this thread.

Leon
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Leon

Did you find a solution?

If you did can you share it?:)
 

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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/07/3-ways-perform-excel-sql-query/

To copy the (missing) headers:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset

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:

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,061
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top