need help with this

[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

 

[Guest]

hi,
if your xl range does not change and stays C3:F12 all the
time, then there is no need to copy and paste.
Name the range in xl then link to that range.
in access file>get external data>link table.
when the link dialog comes up select file type excel.
Brouse and find your xl file. It will ask you to link
sheet or range. choose range.
This link will act like an access table which you can
display in your form.
not sure but you may have to requery the form to get it to
display updated info. but you can use the on time event to
run a requery sub.
post back if you have more questions.
good luck

 

[J_J]

Hi,
Thank you for your answer anonymous.
I am using Office 2003 and I followed your suggestions step by step.
Unfortuntely it didn't ask for a "link" or "range". It just added a new
linked "sheet" to the file (not within the form) which does not displays the
original time values or text retrieved from the excel file (no data -which
is displayed becuse of the macro executed- is get). It just displayed
ordinary text in some cells...
I am kind of stuck...I guess.
J_J

 

[Guest]

hi again,
I am using 97 at work (which is where i am now) and i use
xp at home. I work for a cheep company. i have no
experience with 03. but i just walked through the link
process in 97 and on the first form of the wizard at the
top is were you select sheets or ranges.
as i said, i've no experiance with 03 but i can't believe
that MS would change this. linking ranges is far more
percise than linking to the whole sheet. when linking to
the sheet, access sometimes picks up empty columns or rows
that it sensed data in. that is because the used cells
setting in excel is off.
The general advice i gave is still valid. i use it all the
time with about 20 linked excel files that i use as tables
for querys and MSQ's.
hope you can sort it out.
good luck