Excel constants in Access code

I'm developing a tool that creates a spreadsheet, writes data from
several Access queries to ranges in the spreadsheet.

That part works.

What I need to do now is format the ranges, so that I can set
borders, shading, etc.

I've generated code in Excel that does the right thing, but when I
move the code into an Access module, the compiler complains
"variable not defined" highlighting Excel Intrinsic constants.
I've changed some of the constants for literal values, which works,
but there are lots that need to be done, and the code would be much
more readable with the constants.

So my question: Is there a way to allow an Access modukle to
reverence the constants in Excel, by setting a reference (I tried,
but maybe I did it wrong, it didn't work) or some other method... I
know I can define the ones I need in my module, but that takes more
time than I can afford.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Excel constants in Access code

On 20 Dec 2007 00:48:39 GMT, Bob Quintal
wrote:

You probably did it wrong. Set a reference. No need to create an Excel
object. Just use the constants like this:
Debug.Print Excel.Constants.xlLow

-Tom.


>I'm developing a tool that creates a spreadsheet, writes data from
>several Access queries to ranges in the spreadsheet.
>
>That part works.
>
>What I need to do now is format the ranges, so that I can set
>borders, shading, etc.
>
>I've generated code in Excel that does the right thing, but when I
>move the code into an Access module, the compiler complains
>"variable not defined" highlighting Excel Intrinsic constants.
>I've changed some of the constants for literal values, which works,
>but there are lots that need to be done, and the code would be much
>more readable with the constants.
>
>So my question: Is there a way to allow an Access modukle to
>reverence the constants in Excel, by setting a reference (I tried,
>but maybe I did it wrong, it didn't work) or some other method... I
>know I can define the ones I need in my module, but that takes more
>time than I can afford.
>
>--
>Bob Quintal
>
>PA is y I've altered my email address.

Re: Excel constants in Access code

On Dec 20, 9:10 am, Tom van Stiphout wrote:
> On 20 Dec 2007 00:48:39 GMT, Bob Quintal
> wrote:
>
> You probably did it wrong. Set a reference. No need to create an Excel
> object. Just use the constants like this:
> Debug.Print Excel.Constants.xlLow
>
> -Tom.
>
Thanks.

>
>
> >I'm developing a tool that creates a spreadsheet, writes data from
> >several Access queries to ranges in the spreadsheet.
>
> >That part works.
>
> >What I need to do now is format the ranges, so that I can set
> >borders, shading, etc.
>
> >I've generated code in Excel that does the right thing, but when I
> >move the code into an Access module, the compiler complains
> >"variable not defined" highlighting Excel Intrinsic constants.
> >I've changed some of the constants for literal values, which works,
> >but there are lots that need to be done, and the code would be much
> >more readable with the constants.
>
> >So my question: Is there a way to allow an Access modukle to
> >reverence the constants in Excel, by setting a reference (I tried,
> >but maybe I did it wrong, it didn't work) or some other method... I
> >know I can define the ones I need in my module, but that takes more
> >time than I can afford.
>
> >--
> >Bob Quintal
>
> >PA is y I've altered my email address.- Hide quoted text -
>
> - Show quoted text -

Re: Excel constants in Access code

On 20 Dec 2007 00:48:39 GMT, Bob Quintal
wrote:

>I'm developing a tool that creates a spreadsheet, writes data from
>several Access queries to ranges in the spreadsheet.
>
>That part works.
>
>What I need to do now is format the ranges, so that I can set
>borders, shading, etc.
>
>I've generated code in Excel that does the right thing, but when I
>move the code into an Access module, the compiler complains
>"variable not defined" highlighting Excel Intrinsic constants.
>I've changed some of the constants for literal values, which works,
>but there are lots that need to be done, and the code would be much
>more readable with the constants.
>
>So my question: Is there a way to allow an Access modukle to
>reverence the constants in Excel, by setting a reference (I tried,
>but maybe I did it wrong, it didn't work) or some other method... I
>know I can define the ones I need in my module, but that takes more
>time than I can afford.
>
>--
>Bob Quintal
>
>PA is y I've altered my email address.

If it's of any value to you, here are the ones that I use:

' Excel Constants
Public Const xlLeft As Integer = -4131
Public Const xlThin As Integer = 1
Public Const xlCenter As Integer = -4108
Public Const xlBottom As Integer = -4107
Public Const xlTop As Integer = -4160
Public Const xlRight As Integer = -4152
Public Const xlAutomatic As Integer = -4105
Public Const xlSolid As Integer = 1
Public Const xlMedium As Integer = -4138
Public Const xlDouble As Integer = -4119
Public Const xlThick As Integer = 4
Public Const xlEdgeBottom As Integer = 9
Public Const xlDatabase As Integer = 1
Public Const xlPageField As Integer = 3
Public Const xlColumnField As Integer = 2
Public Const xlDataField As Integer = 4
Public Const xlRowField As Integer = 1
Public Const xlEdgeRight As Integer = 10
Public Const xlDown As Integer = -4121
Public Const xlToLeft As Integer = -4159
Public Const xlToRight As Integer = -4161
Public Const xlCellValue As Integer = 1
Public Const xlLess As Integer = 6
Public Const xlGreater As Integer = 5
Public Const xlGreaterEqual As Integer = 7
Public Const xlBetween As Integer = 1
Public Const xlMaximized As Integer = -4137

Re: Excel constants in Access code

On Dec 20, 12:17 pm, Arch wrote:
> On 20 Dec 2007 00:48:39 GMT, Bob Quintal
> wrote:
>
>
>
>
>
> >I'm developing a tool that creates a spreadsheet, writes data from
> >several Access queries to ranges in the spreadsheet.
>
> >That part works.
>
> >What I need to do now is format the ranges, so that I can set
> >borders, shading, etc.
>
> >I've generated code in Excel that does the right thing, but when I
> >move the code into an Access module, the compiler complains
> >"variable not defined" highlighting Excel Intrinsic constants.
> >I've changed some of the constants for literal values, which works,
> >but there are lots that need to be done, and the code would be much
> >more readable with the constants.
>
> >So my question: Is there a way to allow an Access modukle to
> >reverence the constants in Excel, by setting a reference (I tried,
> >but maybe I did it wrong, it didn't work) or some other method... I
> >know I can define the ones I need in my module, but that takes more
> >time than I can afford.
>
> >--
> >Bob Quintal
>
> >PA is y I've altered my email address.
>
> If it's of any value to you, here are the ones that I use:
>
> ' Excel Constants
> Public Const xlLeft As Integer = -4131
> Public Const xlThin As Integer = 1
> Public Const xlCenter As Integer = -4108
> Public Const xlBottom As Integer = -4107
> Public Const xlTop As Integer = -4160
> Public Const xlRight As Integer = -4152
> Public Const xlAutomatic As Integer = -4105
> Public Const xlSolid As Integer = 1
> Public Const xlMedium As Integer = -4138
> Public Const xlDouble As Integer = -4119
> Public Const xlThick As Integer = 4
> Public Const xlEdgeBottom As Integer = 9
> Public Const xlDatabase As Integer = 1
> Public Const xlPageField As Integer = 3
> Public Const xlColumnField As Integer = 2
> Public Const xlDataField As Integer = 4
> Public Const xlRowField As Integer = 1
> Public Const xlEdgeRight As Integer = 10
> Public Const xlDown As Integer = -4121
> Public Const xlToLeft As Integer = -4159
> Public Const xlToRight As Integer = -4161
> Public Const xlCellValue As Integer = 1
> Public Const xlLess As Integer = 6
> Public Const xlGreater As Integer = 5
> Public Const xlGreaterEqual As Integer = 7
> Public Const xlBetween As Integer = 1
> Public Const xlMaximized As Integer = -4137- Hide quoted text -
>
> - Show quoted text -

Thanks. I tried to use Tom's suggestion, now when I try to set a
property like the Font, or the Borders, I get the error message 438
"Object does not support this property or method."
but the code works in Excel.

I wonder if there's a way to write a string to Excel's VB editor? Then
I could store the code in Access and paste it into Excel, then execute
it there....

Re: Excel constants in Access code

rquintal@sympatico.ca wrote:
[snip]
>
> Thanks. I tried to use Tom's suggestion, now when I try to set a
> property like the Font, or the Borders, I get the error message 438
> "Object does not support this property or method."
> but the code works in Excel.
>
> I wonder if there's a way to write a string to Excel's VB editor? Then
> I could store the code in Access and paste it into Excel, then execute
> it there....


There are lot of things that can go wrong when automating. Perhaps you
could show us some of your code that errors?

Give us also how the objects are declared and instantiated.

--
Roy-Vidar

Re: Excel constants in Access code

RoyVidar wrote in
news:476cdb61$0$13728$c83e3ef6@nn1-read.tele2.net:

> rquintal@sympatico.ca wrote:
> [snip]
>>
>> Thanks. I tried to use Tom's suggestion, now when I try to set a
>> property like the Font, or the Borders, I get the error message
>> 438 "Object does not support this property or method."
>> but the code works in Excel.
>>
>> I wonder if there's a way to write a string to Excel's VB editor?
>> Then I could store the code in Access and paste it into Excel,
>> then execute it there....
>
>
> There are lot of things that can go wrong when automating. Perhaps
> you could show us some of your code that errors?
>
> Give us also how the objects are declared and instantiated.
>

I snipped out a lot of stuff that's working, because it's a couple
of hundred lines.

Code originally from Access Web, and it works to write.the cells.

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Sub sTestXL()
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

'**************************
' declare and open recordset,
' and run a loop through the rows
' writing to selected cells.in the sheet.
' snipped because this part works,
' The inner loop works but is shown here for reference.
For iptr 0 to rsFrom.fields.count - 1
With objActiveWkb.Worksheets(1).Cells(row, col) _
= rsFrom.fields(col+offset)
End With
next iptr
'**************************


'****************************
' Here I set the range I wish to format. - this works.

ObjXL.Range("C5:" & fMakeRange(row + 5) & col).Select
' fMakeRange is in mdlUtility


' but the code from here down does not.
' I get an error # 438 "Object does not support
' this property or method."
' This code was generated from a working Excel macro.
'
' I have added the modifier as suggested by Tom van Stiphout
' This fixed the initial problem I was having.
'****************************

With objActiveWkb.Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
'.Underline = xlUnderlineStyleNone
.Underline = ObjXl.Constants.xlUnderlineStyleNone
'.ColorIndex = xlAutomatic
.ColorIndex = ObjXl.Constants.xlAutomatic
End With

'[ snipped other format settings as similar ]

'****************************
' and this works.
objActiveWkb.saveas strNewFileName

Set objActiveWkb = Nothing: Set objXL = Nothing
End Sub
'************ Code End *********


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Excel constants in Access code

Having a bit of problems with my ISP, it seems, I see your reply through
google, but not through the interface I'm using.

By declaring your Excel objects as Object, and using Get/Create-object,
it seems you're using/intending late binding, but you're saying you have
a reference?

I'd try removing the reference, and instead of trying to refer through
Excel for the contants, either declare them somewhere in the project, or
use the literal numbers.

I would try to use explicit objects for each excel "level" objects I'm
using, and I would try being even more explicit in my instantiations.

Set objActiveWkb = objXL.workbooks.Add

dim sh as object ' excel.worksheet
set sh = objActiveWkb.Worksheets(1)

For iptr 0 to rsFrom.fields.count - 1
With sh
.Cells(row, col) = rsFrom.fields(col+offset)
End With
' or just
' sh.Cells(row, col) = rsFrom.fields(col+offset)
next iptr

What you're doing here, is using the excel object to do some selection,
which might (if you're lucky) be on the sheet you wish, but then it
might not. That's one danger of not being explicit. Afterwards, you're
not using this selection, but attempting to use a workbooks selection
method, which it doesnt have (438)

' ObjXL.Range("C5:" & fMakeRange(row + 5) & col).Select

dim rng as object ' Excel.Range
set rng = sh.Range("C5:" & fMakeRange(row + 5) & col)
With rng.font
.your code

A workbook doesnt' have a selection method, object or property.
Application does.

sh.select
sh.Range("C5:" & fMakeRange(row + 5) & col).Select
With With ObjXL.Selection.Font
.your code

Or try just

With sh.Range("C5:" & fMakeRange(row + 5) & col)
.your code

--
Roy-Vidar

Re: Excel constants in Access code

RoyVidar wrote in
news:476d5a2e$0$13723$c83e3ef6@nn1-read.tele2.net:

> Having a bit of problems with my ISP, it seems, I see your reply
> through google, but not through the interface I'm using.
>
> By declaring your Excel objects as Object, and using
> Get/Create-object, it seems you're using/intending late binding,
> but you're saying you have a reference?
>
> I'd try removing the reference, and instead of trying to refer
> through Excel for the contants, either declare them somewhere in
> the project, or use the literal numbers.
>
> I would try to use explicit objects for each excel "level" objects
> I'm using, and I would try being even more explicit in my
> instantiations.
>
> Set objActiveWkb = objXL.workbooks.Add
>
> dim sh as object ' excel.worksheet
> set sh = objActiveWkb.Worksheets(1)
>
> For iptr 0 to rsFrom.fields.count - 1
> With sh
> .Cells(row, col) = rsFrom.fields(col+offset)
> End With
> ' or just
> ' sh.Cells(row, col) = rsFrom.fields(col+offset)
> next iptr
>
> What you're doing here, is using the excel object to do some
> selection, which might (if you're lucky) be on the sheet you wish,
> but then it might not. That's one danger of not being explicit.
> Afterwards, you're not using this selection, but attempting to use
> a workbooks selection method, which it doesnt have (438)
>
> ' ObjXL.Range("C5:" & fMakeRange(row + 5) & col).Select
>
> dim rng as object ' Excel.Range
> set rng = sh.Range("C5:" & fMakeRange(row + 5) & col)
> With rng.font
> .your code
>
> A workbook doesnt' have a selection method, object or property.
> Application does.
>
> sh.select
> sh.Range("C5:" & fMakeRange(row + 5) & col).Select
> With With ObjXL.Selection.Font
> .your code
>
> Or try just
>
> With sh.Range("C5:" & fMakeRange(row + 5) & col)
> .your code
>
Thank you very much Roy, I'll attempt to incorporate your
suggestions when I get back to work next year.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com