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
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
247 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 35

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 35

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

856 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