Solved

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

Posted on 2014-01-30
5
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 37

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 37

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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