Solved

I am trying to create an Access query that will reference a cell in an Excel worksheet where the worksheet's file name is "Like" the table's primary key.

Posted on 2014-10-31
6
233 Views
Last Modified: 2014-10-31
I am trying to create an Access query that will reference a cell in an Excel worksheet. There are hundreds of Excel files located in a single folder but  I want to reference the one that has a file name matching the table's primary key. Something similar to: =(C:\folder\filename\sheet1!A1) where filename is Like *SITE_ID*.xls. SITE_ID is a column in Table1. Any suggestions?
0
Comment
Question by:Ben Rapier
  • 2
  • 2
  • 2
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Is SITE_ID the complete filename except for the extension or are you really trying to do a fuzzy search?
0
 
LVL 5

Expert Comment

by:ReneD100
Comment Utility
You'll have to write a user-defined function in VBA to check all the files in folder that are based on the name and return the first one found.
0
 

Author Comment

by:Ben Rapier
Comment Utility
The SITE_ID column has hundreds of unique values but each one is in the format of '1234'. There is a corresponding Excel file for each site. The files are stored in the C:\design folder. An example file name is '1234.xls' and I am looking for the value of cell 'A1' on the 'Work' sheet (tab).
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Accepted Solution

by:
ReneD100 earned 250 total points
Comment Utility
Sorry, I misunderstood the question at first. Actually I am still not 100% sure I got it now...
But this could be a good start - assuming I did understand it.

Option Compare Database

Public Function FindFileName(strFileFolder As String) As String
    Dim strFile As String
    strFile = Dir(strFileFolder)
    FindFileName = strFile
End Function

Public Function GetExcelData(strFileFolder As String) As String
    'open the excel sheet
    Dim objXLS As Object
    Dim ws As Object
    Dim wb As Object
    Dim r As Object
    Dim strValue As String
    
    Set objXLS = CreateObject("Excel.Application")
    Set wb = objXLS.workbooks.Open(strFileFolder)
    Set ws = wb.worksheets(1)
    Set r = ws.cells(1, 1)
    
    strValue = r.Value
    wb.Close
    Set ws = Nothing
    Set wb = Nothing
    Set r = Nothing
    Set objXLS = Nothing
    
    GetExcelData = strValue
End Function

Open in new window


I am not sure where you need the query for, but constantly opening and closing excel is going to take some time.
It would be better to first start Excel (Set objXLS = CreateObject("Excel.Application")) and then pass the Excel object to a function which would open the workbook, get the data and close the workbook.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
A query can't do this directly.  You can use the query to create the file name of the file you want to link to or import.  Do you need to import or link the spreadsheets?  Are you always referencingthe first sheet?  Do the links need to remain or are you doing a serial process where you do something with file1 then something with file2, etc but you don't need to keep the links or imported tables.  Are the files always in a specific folder or do you need to navigate to a folder and then start the process?
0
 

Author Closing Comment

by:Ben Rapier
Comment Utility
Thanks for the prompt help! I guess I should have mentioned that I was working on an append table query.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

771 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

15 Experts available now in Live!

Get 1:1 Help Now