Progress bar needed during Excel macro call from MS Access.

I don't know if there's even a way to do what I want. I click on a
button in an Access form, and it opens an instance of Excel, and opens
a workbook and runs a macro that's in it. So far, fine, I know how
to do this. I'm preferring to keep the Excel instance invisible, but
while the macro is running, it would be nice to see some sort of
progress indicator.

(By the way, here's basically what the Excel macro is doing: it's
opening nine workbooks, doing a lot of copying and pasting, and then
saving a workbook. I'd be satisfied if, after each of the 9 workbooks
that get opened, some sort of display shows on the screen so the user
knows that workbook is finished.)

It almost seems as if Excel would need to have a way to display info
on an Access form. Is that even possible? Or alternatively, maybe I
should not make the Excel instance invisible, and then the progress
could be displayed on some sort of Excel screen. The only thing is,
if the Excel instance is visible, the screen will be flashing kind of
annoyingly, due to the large amount of copying and pasting going on.

re: Progress bar needed during Excel macro call from MS Access.

Add the following to your Excel VBA code:

at the beginning:

Application.ScreenUpdating = False

And obviously at the end:

Application.ScreenUpdating = True

This will also speed up your Excel code.

P



"Mrs Howl" <timckelley@yahoo.com> wrote in message
news:5bfe86c.0309241233.e129acf@posting.google.com ...[color=blue]
> I don't know if there's even a way to do what I want. I click on a
> button in an Access form, and it opens an instance of Excel, and opens
> a workbook and runs a macro that's in it. So far, fine, I know how
> to do this. I'm preferring to keep the Excel instance invisible, but
> while the macro is running, it would be nice to see some sort of
> progress indicator.
>
> (By the way, here's basically what the Excel macro is doing: it's
> opening nine workbooks, doing a lot of copying and pasting, and then
> saving a workbook. I'd be satisfied if, after each of the 9 workbooks
> that get opened, some sort of display shows on the screen so the user
> knows that workbook is finished.)
>
> It almost seems as if Excel would need to have a way to display info
> on an Access form. Is that even possible? Or alternatively, maybe I
> should not make the Excel instance invisible, and then the progress
> could be displayed on some sort of Excel screen. The only thing is,
> if the Excel instance is visible, the screen will be flashing kind of
> annoyingly, due to the large amount of copying and pasting going on.[/color]

re: Progress bar needed during Excel macro call from MS Access.

Thanks for that great tip! I suppose I can let Excel do the progress
reporting, and just before each progress reporting stat, I could
reenable screen updating, display the stat (maybe by changing a cell
and then selecting that cell), and then disable screen updating again.
So I suppose I better not make the Excel task invisible.

"Phobos" <laughing@spammers.co.uk> wrote in message news:<m86dndmR9L5vm--iU-KYiA@brightview.com>...[color=blue]
> Add the following to your Excel VBA code:
>
> at the beginning:
>
> Application.ScreenUpdating = False
>
> And obviously at the end:
>
> Application.ScreenUpdating = True
>
> This will also speed up your Excel code.
>
> P
>
>
>
> "Mrs Howl" <timckelley@yahoo.com> wrote in message
> news:5bfe86c.0309241233.e129acf@posting.google.com ...[color=green]
> > I don't know if there's even a way to do what I want. I click on a
> > button in an Access form, and it opens an instance of Excel, and opens
> > a workbook and runs a macro that's in it. So far, fine, I know how
> > to do this. I'm preferring to keep the Excel instance invisible, but
> > while the macro is running, it would be nice to see some sort of
> > progress indicator.
> >
> > (By the way, here's basically what the Excel macro is doing: it's
> > opening nine workbooks, doing a lot of copying and pasting, and then
> > saving a workbook. I'd be satisfied if, after each of the 9 workbooks
> > that get opened, some sort of display shows on the screen so the user
> > knows that workbook is finished.)
> >
> > It almost seems as if Excel would need to have a way to display info
> > on an Access form. Is that even possible? Or alternatively, maybe I
> > should not make the Excel instance invisible, and then the progress
> > could be displayed on some sort of Excel screen. The only thing is,
> > if the Excel instance is visible, the screen will be flashing kind of
> > annoyingly, due to the large amount of copying and pasting going on.[/color][/color]