By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,530 Members | 2,181 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,530 IT Pros & Developers. It's quick & easy.

How to update Stock in Excel from Access?

P: 26
Hi,

I am creating a Quote Database in MS Access 2007 that prepares Quote for a Printing Company.
I have 2 tables, tblClient and tblQuote with 1-to-many relationships.


in the Quote form, i have a substrate called BusinessCards.
when customer request for 500 Business cards, the user Clicks the checkbox and it calculates the value.
I have acheived this with few lines of VBA code but at the same time, i want the user to notify about the availability of substrate to preform the jobs.

all Stock (substrate) information are in Excel spreadsheet (C:/Stock/StockSheet.xls). for example, the available stock for business cards is in cell R10 of the spreadsheet. i want to access this cell to check for availability of stock while preparing the Quote.

Is it possible to access a particular cell in Excel from Access ?

if yes, could anyone tell me how to do this, please?!

thanks and regards,
Asle
Oct 24 '07 #1
Share this Question
Share on Google+
18 Replies


Jim Doherty
Expert 100+
P: 897
Hi,

I am creating a Quote Database in MS Access 2007 that prepares Quote for a Printing Company.
I have 2 tables, tblClient and tblQuote with 1-to-many relationships.


in the Quote form, i have a substrate called BusinessCards.
when customer request for 500 Business cards, the user Clicks the checkbox and it calculates the value.
I have acheived this with few lines of VBA code but at the same time, i want the user to notify about the availability of substrate to preform the jobs.

all Stock (substrate) information are in Excel spreadsheet (C:/Stock/StockSheet.xls). for example, the available stock for business cards is in cell R10 of the spreadsheet. i want to access this cell to check for availability of stock while preparing the Quote.

Is it possible to access a particular cell in Excel from Access ?

if yes, could anyone tell me how to do this, please?!

thanks and regards,
Asle
The simple way is to LINK to the spreadsheet

(File..Get external data..Link (note NOT import) . ...files of Type dropdown select the Excel version.browse for the file click ok and the spreadsheet will be attached to the tables interface in Access. You can then run queries and lookup functions across that 'Linked' table as though it were physically present IN your access database

Regards

Jim
Oct 24 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
Check out Application Automation.
Let us know how you get on :)
Oct 24 '07 #3

P: 26
Check out Application Automation.
Let us know how you get on :)

Hi guys,

I linked the excel sheet into the access database and included some basic VBA code..

its working really good... thanks a ton for your speedy reply.

Neopa, i looked at the post you asked me to ... i think i will try to use the outlook autmation at the later stage... it looks much simpler than i thought. thanks mate..


chuz,
Asle
Oct 25 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
No worries Asle.
I never could get into Outlook automation myself and my experience in automating other Office apps is limited too (:(). I put the article together from notes I had from a course I went on. I know there are others around here with experience there though.
From what I've seen (I do have a little experience you understand) though, once you have the other app started it is very much like coding directly for (within) it.
Good luck :)
Oct 25 '07 #5

P: 26
No worries Asle.
I never could get into Outlook automation myself and my experience in automating other Office apps is limited too (:(). I put the article together from notes I had from a course I went on. I know there are others around here with experience there though.
From what I've seen (I do have a little experience you understand) though, once you have the other app started it is very much like coding directly for (within) it.
Good luck :)
hi again,

i was wondering how to change the value from access to the linked table in excel...


the reason is, i wanted to update the stock record in linked excel table directly from access. i understand it is possible to make changes in excel table which will then update to the linked table in access but not vice versa.

is there any other solution regarding to this?

waiting for your response!!

chuz,
Asle
Oct 28 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
It should work Asle.
Have you tried it :S
Oct 29 '07 #7

P: 26
It should work Asle.
Have you tried it :S

Hi Neopa,

when i update the records, the changes occurs in the linked table within Access but not the source table (Excel spreadsheet).

if i make changes in the source table, it updates the linked table, which is fine.

but i want to change the information in the source table directly.

changing the value in the linked table withinin ms access is not updating in the source table.

need your assitance

chuz,
Asle
Oct 29 '07 #8

NeoPa
Expert Mod 15k+
P: 31,494
It seems that I was mistaken Asle.
Access DOESN'T support updating the Excel spreadsheet from the database as I'd thought :(
I'm afraid you'll have to try another route.
Oct 29 '07 #9

P: 26
It seems that I was mistaken Asle.
Access DOESN'T support updating the Excel spreadsheet from the database as I'd thought :(
I'm afraid you'll have to try another route.

thanks mate. i found a document in microsoft website reflecting your point.

i will try 2 find another solution if i can.

in the mean time lemme know if there is any other way to solve this issue.

thanks champ..

chuz,
Asle
Oct 29 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
Did you get anywhere with Automation?
That should give you full access (small a) to the data.
Oct 29 '07 #11

Jim Doherty
Expert 100+
P: 897
It seems that I was mistaken Asle.
Access DOESN'T support updating the Excel spreadsheet from the database as I'd thought :(
I'm afraid you'll have to try another route.
Hi Asle and Neo,

Neo your 'original' thoughts were correct I have them updating perfectly here back and forth either the link table edit or the spreadsheet edit
Oct 29 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
You've no idea why they might update in some circumstances yet not in others Jim?
Oct 29 '07 #13

Jim Doherty
Expert 100+
P: 897
You've no idea why they might update in some circumstances yet not in others Jim?
Not particularly no. I havent come a situation where it hasnt done the business for me. Provided Access can map the column headers (which if it can't then it will give it a F1, F2, F3, etc as proxy headers where a column of data exists but no header details in cells A) and so on either was you have an editable sheet in your db.

You can't access the spreadsheet if it is closed and you have the linked table 'open on screen' already obviously because there is a share conflict.

If you open the spreadsheet 'first' and then the linked table in that order then you can place values in the table and watch them upddated when you switch back and forth . The only issue here is you cannot REFRESH the linked table record using the classic command on the menu bar you have to close and reopen the table in that case at which point it is updated because it requeries the underlying linked spreadsheet (which incidentally whilst open in memory and even though you might not have saved the changes in the spreadsheet Access picks up on the changes in the already open unsaved spreadsheet.) If I am making any sense ?


Jim :)
Oct 29 '07 #14

NeoPa
Expert Mod 15k+
P: 31,494
Not particularly no. ...
You can't access the spreadsheet if it is closed and you have the linked table 'open on screen' already obviously because there is a share conflict.
...
Thanks for that Jim. I forgot to mention that testing this as you go is likely to cause it to fail :(

Asle, try testiing this without the Excel spreadsheet open at all, until after Access has finished and see if the updates 'take' then?
Oct 29 '07 #15

P: 26
Thanks for that Jim. I forgot to mention that testing this as you go is likely to cause it to fail :(

Asle, try testiing this without the Excel spreadsheet open at all, until after Access has finished and see if the updates 'take' then?

Hi guys,

I just saw your replies.

Neopa, will try to test without Spreadsheet open and will let you know.

hope it works.

chuz,
Asle
Oct 30 '07 #16

NeoPa
Expert Mod 15k+
P: 31,494
Yes, let us know K... Asle :)
Certainly having the spreadsheet open while trying this WOULD cause it to fail as far as I understand things.
Oct 30 '07 #17

P: 26
Yes, let us know K... Asle :)
Certainly having the spreadsheet open while trying this WOULD cause it to fail as far as I understand things.

Hi Neo :) ,

I got it working... man, its very srurpising 2 see it finally works...


thanks a lot Jim.

luv u guys,
Asle
Oct 31 '07 #18

NeoPa
Expert Mod 15k+
P: 31,494
All good Asle :)
It's lucky Jim jumped in when he did though, we could have gone on merrily thinking it was all wrong without realising the one small problem in the testing ;)
Oct 31 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.