Variable not defined

[irade92]

Hello
I am sending query to Excel and everything is ok if in References is Excel 15.0 Object Library. To avoid Excel version I made it late binding..and everything is ok until the code reach this line:

.Range("A4:F4").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous

I got this message: Variable not defined for "XlLineStyle"
Where and how to define this variable?
Thanks

 

[sonic8]

To avoid Excel version I made it late binding..
[...]
I got this message: Variable not defined for "XlLineStyle"

If you only need this particular one constant, you can copy and paste the following to any module in your application.

Public Enum XlLineStyle
    xlContinuous = 1
End Enum

You can lookup the enum values in the Object Browser, while the reference to the Excel object library is set.

If you need a lot of those enums/constants in your code, you can download a ready made module with all of those constants from my website.

 

[Solo712]

Hello
I am sending query to Excel and everything is ok if in References is Excel 15.0 Object Library. To avoid Excel version I made it late binding..and everything is ok until the code reach this line:

.Range("A4:F4").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous

I got this message: Variable not defined for "XlLineStyle"
Where and how to define this variable?
Thanks

You still need to have Microsoft Excel 15.0 Object Library into your project References. This reference will cover all other (lesser) versions, and likely also the 2016 version.

Best,
Jiri

 

[isladogs]

You still need to have Microsoft Excel 15.0 Object Library into your project References. This reference will cover all other (lesser) versions, and likely also the 2016 version.

Best,
Jiri

I believe that irade92 is trying to avoid issues with users having different versions of Excel. If so, he is right to be wary.
References WILL be upgraded to newer versions so users of Access 16.0 (2016) would be OK.
However downgrading doesn't work - Access 14.0(2010) has no idea what the later reference is so will flag it as MISSING.
I know this from bitter experience!

So using late binding, irade92 needs to define any objects that are from the Excel library.

In this case I suspect this will be correct (but not tested)

Dim XlLineStyle As Object

It should be placed in the declarations section of the relevant code module

EDIT - just read sonic's earlier reply which I'd somehow missed. That will definitely work

 

[irade92]

If you only need this particular one constant, you can copy and paste the following to any module in your application.

Public Enum XlLineStyle
    xlContinuous = 1
End Enum

You can lookup the enum values in the Object Browser, while the reference to the Excel object library is set.

If you need a lot of those enums/constants in your code, you can download a ready made module with all of those constants from my website.

Great Thanks to Sonic8...