Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
276 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 40

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 5

Accepted Solution

by:
ReneD100 earned 1000 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 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 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

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!

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

916 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