Access Drawing Rectangles in Excel

[ddecoste]

I have a need to add a visual representation to some data in Access.
I need to draw a matix of squares inside another square. I have all
the data that I need in a record in Access. The data changes
according to what the user inputs. I know that Access does not allow
you to draw on forms so I decided to put the output in an Excel file
for the user but I am having trouble drawing the rectange from
Access. Here is my simple code so far:

Private Sub View_Record_Click()
Dim objXL As Object
Dim newbook As Excel.Workbook
Dim newsht1 As Excel.Worksheet

Set objXL = CreateObject(Class:="Excel.Application")

Set newbook = objXL.Workbooks.Add

Set newsht1 = newbook.Sheets("Sheet1")
newsht1.Shapes.AddShape(msoShapeRectangle, 89.25, 72.75, 113.25,
92.25).Select
objXL.Visible = True

End Sub

In my routine the position, size and number of Rectangles will be
controlled by variables. It could be 4 boxes in a 2x2 or 18 boxes in
a 3 x 6, but I have all that data.

It is giving me a run time error 1004, The specified value is out of
range. And this is on the AddShape line. Anybody have any experice
in using Access to control Excel Adding Shapes.

Thank you for the help.

Darren

[Rich P]

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[ddecoste]

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

[Gord]

On Feb 21, 9:06 am, "ddecoste" <[e-mail]:

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

I think what Rich was hinting at is that you can manipulate
the .Top, .Left, .Width and .Height properties of objects on forms and
reports at runtime. For example, I created a report for survey results
that included the following for each question

Score
Average_Score
Confidence_Interval_Lower_Bound
Confidence_Interval_Upper_Bound

In the detail section of the report I included a fixed white rectangle
to represent the total range of scores, a grey rectangle to represent
the confidence interval for the mean (within the total range), and an
"X" to represent the actual score. I put code in the OnFormat event of
the detail band to adjust the position and width of the grey rectangle
based on the values of [Confidence_Interval_Lower_Bound] and
[Confidence_Interval_Upper_Bound], and to change the .Left property of
the label containing the "X" based on the value of [Score]. The result
was something like this:

+-------------------+------------+--------------------+
| | | X | Question 1
+-------------------+------------+--------------------+

+---------------+--------------------+----------------+
| | X | | Question 2
+---------------+--------------------+----------------+

[ddecoste]

On Feb 21, 8:37 am, "Gord" <[e-mail]:

On Feb 21, 9:06 am, "ddecoste" <[e-mail]:

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

I think what Rich was hinting at is that you can manipulate
the .Top, .Left, .Width and .Height properties of objects on forms and
reports at runtime. For example, I created a report for survey results
that included the following for each question

Score
Average_Score
Confidence_Interval_Lower_Bound
Confidence_Interval_Upper_Bound

In the detail section of the report I included a fixed white rectangle
to represent the total range of scores, a grey rectangle to represent
the confidence interval for the mean (within the total range), and an
"X" to represent the actual score. I put code in the OnFormat event of
the detail band to adjust the position and width of the grey rectangle
based on the values of [Confidence_Interval_Lower_Bound] and
[Confidence_Interval_Upper_Bound], and to change the .Left property of
the label containing the "X" based on the value of [Score]. The result
was something like this:

+-------------------+------------+--------------------+
| | | X | Question 1
+-------------------+------------+--------------------+

+---------------+--------------------+----------------+
| | X | | Question 2
+---------------+--------------------+----------------+- Hide quoted text -

- Show quoted text -

Ok, I did read that I can manipulate rectangles that are on the form
or report during runtime. I think maybe the problem is not
understood. This is good but I would have to make sure that all the
rectangles are present before hand. That would require controlling
400+ rectangles at the current set point in the software. Based on a
current max of 20 x 20 matrix. Most of the time I will be using under
40 but in some cases this number will be higher. I was hoping to be
able to make each as needed on the fly. To me it is easier to Code a
few lines in a nested loop than manually have to make up all rectanges
then manipulate.

I can definately add these to form and control them but it seems like
overkill.

I was hoping that somebody has used the Excel
AddShape(msoShapeRectangle) from Access. If this can be made to work
with one rectangle being created in an Excel file then the battle is
won.

Anybody have any other thoughts.

Thank you for your help.

[Gord]

On Feb 21, 11:01 am, "ddecoste" <[e-mail]:

On Feb 21, 8:37 am, "Gord" <[e-mail]:

On Feb 21, 9:06 am, "ddecoste" <[e-mail]:

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

I think what Rich was hinting at is that you can manipulate
the .Top, .Left, .Width and .Height properties of objects on forms and
reports at runtime. For example, I created a report for survey results
that included the following for each question

Score
Average_Score
Confidence_Interval_Lower_Bound
Confidence_Interval_Upper_Bound

In the detail section of the report I included a fixed white rectangle
to represent the total range of scores, a grey rectangle to represent
the confidence interval for the mean (within the total range), and an
"X" to represent the actual score. I put code in the OnFormat event of
the detail band to adjust the position and width of the grey rectangle
based on the values of [Confidence_Interval_Lower_Bound] and
[Confidence_Interval_Upper_Bound], and to change the .Left property of
the label containing the "X" based on the value of [Score]. The result
was something like this:

+-------------------+------------+--------------------+
| | | X | Question 1
+-------------------+------------+--------------------+

+---------------+--------------------+----------------+
| | X | | Question 2
+---------------+--------------------+----------------+- Hide quoted text -

- Show quoted text -

Ok, I did read that I can manipulate rectangles that are on the form
or report during runtime. I think maybe the problem is not
understood. This is good but I would have to make sure that all the
rectangles are present before hand. That would require controlling
400+ rectangles at the current set point in the software. Based on a
current max of 20 x 20 matrix. Most of the time I will be using under
40 but in some cases this number will be higher. I was hoping to be
able to make each as needed on the fly. To me it is easier to Code a
few lines in a nested loop than manually have to make up all rectanges
then manipulate.

I can definately add these to form and control them but it seems like
overkill.

I was hoping that somebody has used the Excel
AddShape(msoShapeRectangle) from Access. If this can be made to work
with one rectangle being created in an Excel file then the battle is
won.

Anybody have any other thoughts.

Thank you for your help.

FWIW, I took the sample code from your original post and pasted it
into an Access module. Once I added references to the Excel and Office
object libraries it compiled and ran without errors (and placed a
rectangle on the Excel worksheet).

[ddecoste]

On Feb 21, 10:41 am, "Gord" <[e-mail]:

On Feb 21, 11:01 am, "ddecoste" <[e-mail]:

On Feb 21, 8:37 am, "Gord" <[e-mail]:

On Feb 21, 9:06 am, "ddecoste" <[e-mail]:

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

I think what Rich was hinting at is that you can manipulate
the .Top, .Left, .Width and .Height properties of objects on forms and
reports at runtime. For example, I created a report for survey results
that included the following for each question

Score
Average_Score
Confidence_Interval_Lower_Bound
Confidence_Interval_Upper_Bound

In the detail section of the report I included a fixed white rectangle
to represent the total range of scores, a grey rectangle to represent
the confidence interval for the mean (within the total range), and an
"X" to represent the actual score. I put code in the OnFormat event of
the detail band to adjust the position and width of the grey rectangle
based on the values of [Confidence_Interval_Lower_Bound] and
[Confidence_Interval_Upper_Bound], and to change the .Left property of
the label containing the "X" based on the value of [Score]. The result
was something like this:

+-------------------+------------+--------------------+
| | | X | Question 1
+-------------------+------------+--------------------+

+---------------+--------------------+----------------+
| | X | | Question 2
+---------------+--------------------+----------------+- Hide quoted text -

- Show quoted text -

Ok, I did read that I can manipulate rectangles that are on the form
or report during runtime. I think maybe the problem is not
understood. This is good but I would have to make sure that all the
rectangles are present before hand. That would require controlling
400+ rectangles at the current set point in the software. Based on a
current max of 20 x 20 matrix. Most of the time I will be using under
40 but in some cases this number will be higher. I was hoping to be
able to make each as needed on the fly. To me it is easier to Code a
few lines in a nested loop than manually have to make up all rectanges
then manipulate.

I can definately add these to form and control them but it seems like
overkill.

I was hoping that somebody has used the Excel
AddShape(msoShapeRectangle) from Access. If this can be made to work
with one rectangle being created in an Excel file then the battle is
won.

Anybody have any other thoughts.

Thank you for your help.

FWIW, I took the sample code from your original post and pasted it
into an Access module. Once I added references to the Excel and Office
object libraries it compiled and ran without errors (and placed a
rectangle on the Excel worksheet).- Hide quoted text -

- Show quoted text -

Looks good, I had the Excel reference but I had not checked the
reference for Microsoft Office Library. Once I did that, it did
work great. Just for information how did you know to use that
reference, I would have thought it would have been contained the Excel
reference.

Another option that I am thinking of using would be writing directly
to an HTML file then just have IE open the document.

Once again Thank you for your help.

[Gord]

>I had the Excel reference but I had not checked the reference for

>Microsoft Office Library. Once I did that, it did work great. Just
for information how did you know to use that reference, I would have
thought it would have been contained the Excel reference.

I started out with just the Excel reference too, but when I compiled
the code Access complained that it didn't know what

msoShapeRectangle

was. I figured that 'mso' was Microsoft Office, so I added that
reference as well.

On Feb 21, 1:45 pm, "ddecoste" <[e-mail]:

On Feb 21, 10:41 am, "Gord" <[e-mail]:

On Feb 21, 11:01 am, "ddecoste" <[e-mail]:

On Feb 21, 8:37 am, "Gord" <[e-mail]:

On Feb 21, 9:06 am, "ddecoste" <[e-mail]:

On Feb 20, 5:37 pm, Rich P <[e-mail]:

If you need to draw rectangles in access you can use a rectangle tool
from the form designer toolbox. And as long as you plan to add a shape
to an Excel sheet, how about adding a shape like a textbox to an Access
form - you can place the textbox inside the rectangle. Just some ideas.

The key to robust professional programming is to keep it simple, that is
- the least amount of code. Less code = less bugs.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

Thanks for the reply,

The only problem with that is that the drawing or illustration will be
different every time the user uses it. So the rectangles will have to
be dynamic. thus being added programatically. The user inputs a size
X and Y. The Access program then runs through all the calculations
and returns a list of the best possible sizes and % utilitzation.
What I would like is for the user to do is select any one from the
list then a graphic representation would appear showing the layout. We
have UserX and Y Size . This then has to fit in ManufacturingSize X
and Y then that has to fit in a StockSize Xand Y. Each size can be
rotated for better fit. What the program is doing is finding the
Optimum size for each step based on 1 to 6 Standard Stock sizes that
can used. I actually wish to put 2 illustrations up one the
ManufacturingSize inside the Stock Size. Lets say 4 rectangles inside
1 rectangle but it could 2 X 3 or any number of rectangles inside
one. Then another would be the User Xand Y inside the Manufacturing
and this could 2x2, 2x3, 3x3.... etc.

The only reason I choose Excel was that I found some code to add
rectangles. If I could do this in the Access runtime it would be
great and I would not need Excel.

Thank you for your help,

Darren

I think what Rich was hinting at is that you can manipulate
the .Top, .Left, .Width and .Height properties of objects on forms and
reports at runtime. For example, I created a report for survey results
that included the following for each question

Score
Average_Score
Confidence_Interval_Lower_Bound
Confidence_Interval_Upper_Bound

In the detail section of the report I included a fixed white rectangle
to represent the total range of scores, a grey rectangle to represent
the confidence interval for the mean (within the total range), and an
"X" to represent the actual score. I put code in the OnFormat event of
the detail band to adjust the position and width of the grey rectangle
based on the values of [Confidence_Interval_Lower_Bound] and
[Confidence_Interval_Upper_Bound], and to change the .Left property of
the label containing the "X" based on the value of [Score]. The result
was something like this:

+-------------------+------------+--------------------+
| | | X | Question 1
+-------------------+------------+--------------------+

+---------------+--------------------+----------------+
| | X | | Question 2
+---------------+--------------------+----------------+- Hide quoted text -

- Show quoted text -

Ok, I did read that I can manipulate rectangles that are on the form
or report during runtime. I think maybe the problem is not
understood. This is good but I would have to make sure that all the
rectangles are present before hand. That would require controlling
400+ rectangles at the current set point in the software. Based on a
current max of 20 x 20 matrix. Most of the time I will be using under
40 but in some cases this number will be higher. I was hoping to be
able to make each as needed on the fly. To me it is easier to Code a
few lines in a nested loop than manually have to make up all rectanges
then manipulate.

I can definately add these to form and control them but it seems like
overkill.

I was hoping that somebody has used the Excel
AddShape(msoShapeRectangle) from Access. If this can be made to work
with one rectangle being created in an Excel file then the battle is
won.

Anybody have any other thoughts.

Thank you for your help.

FWIW, I took the sample code from your original post and pasted it
into an Access module. Once I added references to the Excel and Office
object libraries it compiled and ran without errors (and placed a
rectangle on the Excel worksheet).- Hide quoted text -

- Show quoted text -

Looks good, I had the Excel reference but I had not checked the
reference for Microsoft Office Library. Once I did that, it did
work great. Just for information how did you know to use that
reference, I would have thought it would have been contained the Excel
reference.

Another option that I am thinking of using would be writing directly
to an HTML file then just have IE open the document.

Once again Thank you for your help.