Late Binding and Excel cell formats 2

[cwadams1955]

I have an Access Data Project module that reads an Excel spreadsheet and imports the spreadsheet data into a table. Which column of data is needed is determined by a cell in that column with a diagonal border (representing a slash across the cell), so I need to look at the cell's border linestyle property to determine if I should import that column. Problem is, I have to use late binding because there are at least two or three versions of Excel in use in the company, and I can't find anything that tells how to reference cell properties like borders, etc, when using late binding - the only properties I've been able to reference are Value, Address, and NumberFormat. Anything else I try to use gives an "object does not support this property or method." error. Can anyone tell me how to access these properties, or know of an on-line reference? Thanks.

 

[SkipVought]

xlDiagonalDown = 5
xlDiagonalUp = 6

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, to find this info in your VB Editor, temporarily set a reference for ANY Excel object library. In the Object Browser, find xlDiagonal.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Have you tried:

<YourWorkSheetObject>.Cells(1, 1).Borders(xlDiagonalUp).LineStyle = xlContinuous

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

I generally do the same as Skip for most things, sometimes however if I'm in a bit of a rush and I'm using Excel (mainly Excel but mostly works with other Office products) I'll record a macro of myself doing the relevant action, cut out any of the needless guff Excel has put it and apply what's left over to the objects I'm using in my application.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[cwadams1955]

Cool, thanks to both of you, this helps. I did try using "xlDiagonal", but it didn't like that, although it may have been the specific syntax. Since I need to check to see what the style is, I had tried "If .Cells(x, y).Borders(xlDiagonalDown).LineStyle = Solid then..."

Probably should have been xlSolid there? I'll try the xlContinuous just to see if it will work in this app, otherwise I'll use the numeric constants. Thanks again.

 

[SkipVought]

"I did try using "xlDiagonal", but it didn't like that..."

I assume that the "it" is the VB Compiler. I did not suggest using xlDiagonal for anything except the Object Browser. Doing a FIND on xlDiagonal in the Object Browser, gets you xlDiagonalUp & xlDiagonalDown et al.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cwadams1955]

Sorry, that was in response to HarleyQuinn's code snippet. I had used something like that line previously, and the compiler balked.

 

[HarleyQuinn]

Yeah, it shouldn't have liked Solid on it's own like that.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

As you want late binding you should use the numeric values instead of the named constants.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RoyVidar]

Yes, it balked because you're using late binding, and those constants need a reference to Excel (early binding), either that, or you declare all of them in your project.

Just try it out in Excel, find the correct constants etc. Then when you move it to Access, change all the constants to it's literal value - say in the immediate window (ctrl+g), do

? xlDiagonalDown --> 5
? xlDiagonalUp --> 6
? xlSolid --> 1
...

Roy-Vidar

 

[HarleyQuinn]

Personally, I'd go with declaring the constants in your app, makes for (IMO) much better readability of code.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Roy, a faster way to get the values is the object browser, don't you think ?

 

[SkipVought]

I guess that I was not specific enough to state that you ought to use the numeric values rather than the Excel Object Library constants, since you are not referencing an Excel Object Library in your code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[RoyVidar]

> Roy, a faster way to get the values is the object browser, don't you think ?

Object browser was already mentioned (second reply), using immediate window is an alternative method. I usually work faster with immediate window than object browser, but it's more a matter of preference and how you work, me thinks.

Roy-Vidar

 

[cwadams1955]

SkipVought - No, I got that, and that's probably what I'll do, I was just curious about whether the other format would work if I got the syntax right.

PHV & RoyVidar - I tend to use the immediate window mostly, too, but I wasn't actually all that familiar with using the object browser to pull values like that, so I learned something else new, anyway!