a refreshing data problem from an excel sheet

[J_J]

Hi,

I have two problems...associated with access & excel
I need to display an excel worksheet region on an access form.
My XL workbook beeing "abc.xls" and the involved region C3:F12 on Sheet3.

I can do that by activating the excel workbook, copying the region to memory
and then after running the access file pasting it to the involved form space
in "design" mode of Access Form. And after that when I save the changed
access file it holds the pasted excel info as it "was"...
So far so good. But unfortunately the info that is beeing pasted from the
excel sheet includes some "time" informatin and that changes each munite as
the XL macro executes at minute intervals. Now the "abc.xls" file is active
and the time figure changes on the excel file but not on the access form.

Here are my questions:

1. What should I do to activate my "C:\data\abc.xls" file automatically just
before my access file is beeing executed?
2. How can I configure my access file so that when the data imported from my
"abc.xls" file changes (due to the macro running in excel) the displayed
data on access form also will change?
Hope my questions are not to easy or hard for the NG.

Sincerely
J_J

 

[John Nurick]

Hi JJ,

Probably it would be best to cut Excel out of the loop and connect your
form directly to whatever data source Excel is using.

Otherwise, try creating a query like this:

SELECT *
FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$A10:E20]
;

and binding your form to it, perhaps using the form's Timer event to
force regular requeries.

To launch an Excel workbook, see e.g.
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

 

[J_J]

Hi John,
Thank you for the info. Could you please describe in detail how do I
"cut Excel out of the loop and connect" my "form directly to whatever data
source Excel is using" ?.
TIA

Hi JJ,

Probably it would be best to cut Excel out of the loop and connect your
form directly to whatever data source Excel is using.

Otherwise, try creating a query like this:

SELECT *
FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$A10:E20]
;

and binding your form to it, perhaps using the form's Timer event to
force regular requeries.

To launch an Excel workbook, see e.g.
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

Hi,

I have two problems...associated with access & excel
I need to display an excel worksheet region on an access form.
My XL workbook beeing "abc.xls" and the involved region C3:F12 on Sheet3.

I can do that by activating the excel workbook, copying the region to memory
and then after running the access file pasting it to the involved form space
in "design" mode of Access Form. And after that when I save the changed
access file it holds the pasted excel info as it "was"...
So far so good. But unfortunately the info that is beeing pasted from the
excel sheet includes some "time" informatin and that changes each munite as
the XL macro executes at minute intervals. Now the "abc.xls" file is active
and the time figure changes on the excel file but not on the access form.

Here are my questions:

1. What should I do to activate my "C:\data\abc.xls" file automatically just
before my access file is beeing executed?
2. How can I configure my access file so that when the data imported from my
"abc.xls" file changes (due to the macro running in excel) the displayed
data on access form also will change?
Hope my questions are not to easy or hard for the NG.

Sincerely
J_J

 

[John Nurick]

Not really. I'm assuming that Excel is accessing some external data
source in order to update itself at whatever the intervals are. As a
general rule, if Excel can use a particular data source Access should be
able to as well. But I have no idea what the data source is or how Excel
is connecting to it.

Hi John,
Thank you for the info. Could you please describe in detail how do I
"cut Excel out of the loop and connect" my "form directly to whatever data
source Excel is using" ?.
TIA

Hi JJ,

Probably it would be best to cut Excel out of the loop and connect your
form directly to whatever data source Excel is using.

Otherwise, try creating a query like this:

SELECT *
FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$A10:E20]
;

and binding your form to it, perhaps using the form's Timer event to
force regular requeries.

To launch an Excel workbook, see e.g.
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

Hi,

I have two problems...associated with access & excel
I need to display an excel worksheet region on an access form.
My XL workbook beeing "abc.xls" and the involved region C3:F12 on Sheet3.

I can do that by activating the excel workbook, copying the region to memory
and then after running the access file pasting it to the involved form space
in "design" mode of Access Form. And after that when I save the changed
access file it holds the pasted excel info as it "was"...
So far so good. But unfortunately the info that is beeing pasted from the
excel sheet includes some "time" informatin and that changes each munite as
the XL macro executes at minute intervals. Now the "abc.xls" file is active
and the time figure changes on the excel file but not on the access form.

Here are my questions:

1. What should I do to activate my "C:\data\abc.xls" file automatically just
before my access file is beeing executed?
2. How can I configure my access file so that when the data imported from my
"abc.xls" file changes (due to the macro running in excel) the displayed
data on access form also will change?
Hope my questions are not to easy or hard for the NG.

Sincerely
J_J

 

[J_J]

Ok...Thanks for your feedback...

Not really. I'm assuming that Excel is accessing some external data
source in order to update itself at whatever the intervals are. As a
general rule, if Excel can use a particular data source Access should be
able to as well. But I have no idea what the data source is or how Excel
is connecting to it.

Hi John,
Thank you for the info. Could you please describe in detail how do I
"cut Excel out of the loop and connect" my "form directly to whatever data
source Excel is using" ?.
TIA

Hi JJ,

Probably it would be best to cut Excel out of the loop and connect your
form directly to whatever data source Excel is using.

Otherwise, try creating a query like this:

SELECT *
FROM
[Excel 8.0;HDR=No;database=C:\folder\file.xls;].[Sheet1$A10:E20]
;

and binding your form to it, perhaps using the form's Timer event to
force regular requeries.

To launch an Excel workbook, see e.g.
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm




Hi,

I have two problems...associated with access & excel
I need to display an excel worksheet region on an access form.
My XL workbook beeing "abc.xls" and the involved region C3:F12 on Sheet3.

I can do that by activating the excel workbook, copying the region to memory
and then after running the access file pasting it to the involved form space
in "design" mode of Access Form. And after that when I save the changed
access file it holds the pasted excel info as it "was"...
So far so good. But unfortunately the info that is beeing pasted from the
excel sheet includes some "time" informatin and that changes each

munite
as

the XL macro executes at minute intervals. Now the "abc.xls" file is active
and the time figure changes on the excel file but not on the access form.

Here are my questions:

1. What should I do to activate my "C:\data\abc.xls" file

automatically
just

before my access file is beeing executed?
2. How can I configure my access file so that when the data imported

from
my

"abc.xls" file changes (due to the macro running in excel) the displayed
data on access form also will change?
Hope my questions are not to easy or hard for the NG.

Sincerely
J_J