?
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
?
271 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 39

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 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 39

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

Hire Technology Freelancers with Gigs

Work with 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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