Solved

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

Posted on 2014-01-30
5
221 Views
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
0
Comment
Question by:LapunKiap
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:PatHartman
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.
0
 

Author Comment

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

Expert Comment

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

Accepted Solution

by:
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
VLookup-Function-XL.docx
0
 

Author Closing Comment

by:LapunKiap
ID: 39862513
Studied the code a bit longer
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now