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
250 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
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 40416061
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
ID: 40416072
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
ID: 40416083
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Accepted Solution

by:
ReneD100 earned 250 total points
ID: 40416095
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 36

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40416099
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
ID: 40416115
Thanks for the prompt help! I guess I should have mentioned that I was working on an append table query.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 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