Log in

View Full Version : Copy data from excel using code


daniels012
10-02-2009, 09:13 AM
Can I add a button that when clicked adds a date.
Let me try to explain:
I have an Access form FrmWorkOrderData.mdb
I have a field: ProposalNumber and another field: ProposalDate
I want to hit a button, it looks at an Excel spreadsheet "A List of Proposals.xlxm" in the worksheet "Running Total" and finds in the "Prop#" column the matching proposal number then finds the date in "Date" column then puts it back in the Database in the ProposalDate field.

Is this possible?
Michael
CreganTur
10-02-2009, 10:31 AM
Have you considered making your excel file a linked table? Then you could query it or use the DLookup function to find what you want.

HTH:thumb
daniels012
12-16-2009, 01:17 PM
That sounds really cool.
A couple of things...
If I link like you are suggesting, do I have to have the excel file open?
Also, If I put a Proposal number in the in one box it would then in another field The Date field would have the date that correlated to the excel sheet by having a dlookup function somewhere in the field controls.

Am I sort of close?
Michael
CreganTur
12-16-2009, 01:43 PM
If I link like you are suggesting, do I have to have the excel file open?
No, the excel file will be closed

Sorry, but I don't understand your second question.
geekgirlau
12-16-2009, 07:46 PM
Yes Michael, if I understand you correctly you could enter a proposal number in one field, and then Access can use a DLookup to your linked Excel workbook to locate the relevant date and insert it into your proposal date field.
daniels012
12-17-2009, 10:03 AM
That is what i wanted to know!
Thank You
Michael
daniels012
12-31-2009, 09:27 AM
OK I need a little help understanding this. Again, I am very green when it comes to all of this.
Right now I have 2 fields: Proposal Number and Proposal Date
Both of which are from the existing fields list which the form is based on a query (QRY-WOQryandRequestTbl)

How do I add the dlookup to the existing Proposal Date field? Or can I do this?
If I cannot do this, and have to use an unbound field, how do I get it to populate the database with the date?

I am not sure if I am even using the proper terms! Let me know if I need to explain myself better.

Michael