Excel Output written from Access Form

[Thelma Lubkin]

I am trying to modify an Access form that allows a user to specify
the information s/he needs from a census-type database.

The form's code builds a SQL statement from the user's request, and
creates from this a record set that is used to populate a new Excel
workbook. The form's code opens the new workbook and writes both a
datasheet and a graph based on the data to it.

The graph appears on the user's screen very briefly and then
disappears as the workbook is closed as soon as all information has
been written to it.

I have tried to put a MsgBox in the Access code immediately before the
statement that closes the Excel Application. This allows the graph [&
an opportunity to switch to the Excel data screen] to remain on screen but
the Excel application is using all the screen space so that the MsgBox
that I want to function as a "return to form execution button"
remains hidden. I can return to the form by clicking its
minimized icon but I'd like that more obtrusive MsgBox button visible
for a user while s/he's seeing the Excel output.

So can I customize the Excel display screen within the Acess form?

thanks, --thelma

[deko]

> So can I customize the Excel display screen within the Acess form?

You should be able to do just about anything with Excel automation. Can you
modify whatever automation code you are using? Perhaps leave the workbook
open after populating it?

[Thelma Lubkin]

deko <de**@nospam.com> wrote:
:> So can I customize the Excel display screen within the Acess form?

: You should be able to do just about anything with Excel automation.Can you
: modify whatever automation code you are using? Perhaps leave the workbook
: open after populating it?

Yes, I could modify the code, but I don't know what I should
modify. I don't understand how leaving the workbook open will
help.

I am leaving the workbook open until the user clicks the
MsgBox button, but as s/he has to return to the Access form to see
that button the way the form works now, that open workbook
seems as much a hindrance as a help.

--thelma

[deko]

> Yes, I could modify the code, but I don't know what I should

modify.
It's always a good idea to post your code when you have questions like this.
I don't understand how leaving the workbook open will
help.

As I understand it, you want the user to be able to view a chart in an Excel
worksheet after it is created via automation from Access. If you open
Excel, create the chart, and leave the workbook open, that should leave the
Excel application window on top. If not, you could minimize the Access app
window with DoCmd.RunCommand acCmdAppMinimize.

[Thelma Lubkin]

deko <de**@nospam.com> wrote:
: As I understand it,you want the user to be able to view a chart in an Excel
: worksheet after it is created via automation from Access. If you open
: Excel, create the chart, and leave the workbook open, that should leave the
: Excel application window on top. If not, you could minimize the Access app
: window with DoCmd.RunCommand acCmdAppMinimize.

Yes: this is exactly the situation I have right now. The Excel
worksheet is open over the Access application which shows as a
minimized icon.

I had written, in part:

I have tried to put a MsgBox in the Access code immediately before
the statement that closes the Excel Application....
but the Excel application is using all the screen space so that the
MsgBox that I want to function as a "return to form execution button"
remains hidden. I can return to the form by clicking its
minimized icon but I'd like that more obtrusive MsgBox button visible
for a user while s/he's seeing the Excel output.

I want the Excel worksheet display to take up less than the entire
screen, so that I can also provide user with a MsgBox for
signalling that s/he's through looking at it; then I can close
it and move on.

--thelma

[deko]

> I want the Excel worksheet display to take up less than the entire

screen, so that I can also provide user with a MsgBox for
signalling that s/he's through looking at it; then I can close
it and move on.

If you want to resize the Excel application window and position it on top of
the Access application window, that will require some API programming. You
might be able to adapt code from this article:
http://support.microsoft.com/?kbid=210085

If you don't need Excel for anything but drawing the chart, you might try
using a chart report - i.e. open a new report, select the Chart Wizard and
assign your query to it. Or perhaps use some other charting component from
within Access.

Another solution is to open a popup form with code that exports the Excel
chart to a .gif and displays it in an Image control.

This code assumes the Excel chart already exists, though you could probably
incorporate any necessary automation code here as well. As you know, you
can size the chart however you want before exporting it to a gif.

The GetTempPath API function is so you don't have to worry about deleting
the gif after creating it

Private Declare Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Private Const MAXPATH = 255
Private strTempGif As String

Private Function TempPath() As String
Dim strBuffer As String
Dim lngBytes As Long
strBuffer = Space(MAXPATH)
lngBytes = GetTempPath(Len(strBuffer), strBuffer)
TempPath = Left(strBuffer, lngBytes)
End Function

Private Sub Form_Open(Cancel As Integer)
Dim xlapp As Excel.Application
strTempGif = TempPath & "MyChart.gif"
Set xlapp = New Excel.Application
xlapp.Workbooks.Open "C:\Book1.xls"
xlapp.ActiveWorkbook.Worksheets("Sheet1") _
.ChartObjects(1).Chart.Export _
fileName:=strTempGif
DoEvents
Set xlapp = Nothing
End Sub

Private Sub Form_Load()
Me!imgChart.Picture = strTempGif
End Sub

[Thelma Lubkin]

deko <de**@nospam.com> wrote:
: Another solution is to open a popup form with code that exports the Excel
: chart to a .gif and displays it in an Image control.

Thanks a lot for the code that I've snipped. It would
give me good control over the display, and could be generated
after I saved the Excel output. I'll have to look at it and
decide whether to try to incorporate it.

Today I managed to get the Excel window and the Access window
sharing the screen -- it was just what I wanted, the only
problem being that I haven't the vaguest idea of what I did to
get it that way. I'm sure it had nothing to do with the code.

--thelma