Place txtField from Excel File1 into a field in Excel File 2 using a VLookup function

Posted on 2014-01-30
Medium Priority
Last Modified: 2014-02-16
I have 1/.  An Access dbase with tblBookings linked to 2/. An Excel File "AccessBookings" Sheet(AccessBookings) which consists of Rows from Access dBase 1/.  I have 3/. Excel File "Quote" with a sheet (FltSheet).
In 2/. the first column contains the integerID from the Access dBase and another column contains a  txtField (DestText)  that will be generated in VBA from 3/.Excel File "Quote".(SourceText)
I would like to take the integerID generated in  3/."Quote" and use it to locate and enter    3/. SourceText in the 2/. DestText field. (vlookup ?)
With that in place the Access dBase can be updated to reflect the addition of the DestText
The Excel File 3/. starts as an .xltm file which changes to a RandomFile.xlsx .
I was advised that DAO may be the way to go but that is beyon me at present (Much as I'd like to try).  Hope there's a Fundi out there who can assist. Thanks
Question by:LapunKiap
  • 3
  • 2
LVL 41

Expert Comment

ID: 39821435
This is an Excel question rather than an Access one.

Why does the excel VLookup() not work?  I don't use Excel so I don't know the range of its capabilities but does it allow you to lookup a value from another workbook?  I know it can reference different sheets in the same workbook.

Author Comment

ID: 39822454
I'm having problems writing the lookup in VBA. Also, my mistake, the File 3/. is .xlsm
LVL 41

Expert Comment

ID: 39822523
DLookup() is the VBA function that is the equivalent of VLookup().  Please post your code and tell us what is happening.

Accepted Solution

LapunKiap earned 0 total points
ID: 39825587
Pat thanks. Am aware of the Access DLookup. Just having problems placing a string generated in Wkb 1 into a cell in Wkb2.  In wkb1 I ascertain if a cell contains data. If it does the data will include a number (eg ABC-8). I need to place a str in wkb2 using VLookup(8,wkb2(A:AC), 5,True).   With this in place an Access dBase linked to wkb2 can be updated.

Think I hit the wrong button then.............am still working on solving this problem

Finally managed to sort my fingers out and have a solution....many thanks

Author Closing Comment

ID: 39862513
Studied the code a bit longer

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question