-1

I've been looking all over the web for an answer to this question. I see many blogs and postings regarding opening an excel file based on a cell value NOT within that file.

My question: Is it possible to open a file based on a cell value within the file I am trying to open? Another way to word it: Can VBA search for a cell value within a file, that is not open, and then open that file if it finds that cell value?

Further Explanation:

I save reports from a system into a folder and these files being saved have a common name (Report, Report (1), Report (2), Report (3).....). However, the data within the reports are different. They all have a common cell (Cell A7) that designates what the report is for. I want to use VBA code to open one of these files based on its value in cell A7. Is this even possible?

Thank you in advanced for any direction here.

deadxcell
  • 11
  • 4
  • 2
    https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook – Tim Williams Apr 20 '21 at 15:40
  • You could consider using a filename that reflects the contents, evn if it starts with 'Report'. – freeflow Apr 20 '21 at 15:54
  • Yeah. That is one extra step I was trying to avoid. However, upon looking at Tim's link, I think this task is outside my scope of knowledge. I have no idea how to use the "Ret" string in the current code I have. – deadxcell Apr 20 '21 at 16:22
  • Is it A7 on a specific sheet common to all the files in the folder? – norie Apr 20 '21 at 16:27
  • The value in A7 will be different in each report. But the value I need will always be located in cell A7. There will always only be 1 sheet in the workbook. It is a data download from a database @norie – deadxcell Apr 20 '21 at 16:47
  • is the name of the sheet always the same? – norie Apr 20 '21 at 16:51
  • The name of the sheet will always start off the same "Report" but it will then be follows by the current date(the date will change depending on what day I download the report). – deadxcell Apr 20 '21 at 16:53
  • The biggest problem is determining the worksheet name, but it could be done by using the 'file datestamp' e.g. `fsoFile.DateCreated` or something similar. Try posting a practical example that contains the folder path, worksheet name (workbook (file) name is useless), and the file datestamp (date created). Explain the date format if not obvious. After figuring out the worksheet issue, you can loop through the files of the folder and apply something like the proposed solution on each file until you 'hit a match'. – VBasic2008 Apr 20 '21 at 17:55

1 Answers1

0

If you know the sheet name you can use ExecuteExcel4Macro. If the sheet name is not known then you can use ADO to get that first (easier here as there's only one sheet)

Example:

Sub Tester()
    
    Const fldr As String = "C:\Excel\Temp\"
    Dim f, v
    
    f = Dir(fldr & "*.xlsx")
    'loop over all files in folder
    Do While Len(f) > 0
        'passing empty string for sheetname, since we don't know it...
        v = ReadCell(fldr, f, "", Range("A7").Address(True, True, xlR1C1))
        Debug.Print f, v
        f = Dir
    Loop

End Sub

'Read a cell value given a workbook path, name, optional sheetname,
'  and cell address (R1C1 format)
Function ReadCell(fPath, wbName, wsName, addr)
    Const adSchemaTables = 20
    Dim cn, conStr, rs
    
    If Len(wsName) = 0 Then
        'sheet name is not known, so find the first name using ADO
        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fPath & wbName & "';" & _
                 "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;"";"
        Set cn = CreateObject("ADODB.connection")
        cn.Open conStr
        Set rs = cn.openschema(adSchemaTables)
        wsName = Replace(rs("TABLE_NAME").Value, "$", "")
        rs.Close
        cn.Close
    End If
    
    ReadCell = ExecuteExcel4Macro("'" & fPath & "[" & wbName & "]" & _
                                  wsName & "'!" & addr)
    
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I've tried the code out. I get an error on "Set rs = cn.openschema(adSchemaTables)". I am also getting an error with the ExecuteExcel4Macro coding. Error 2023? – deadxcell Apr 21 '21 at 18:14
  • "an error" is not a very useful description of what happens when you run your code. If you need help figuring this out then you will need to provide a little bit more information, such as *what error* you're seeing. What is the format of the files you're working with? (xls/xlsx/?) – Tim Williams Apr 21 '21 at 18:22
  • Sorry. In the locals window: ReadCell is returning as Error 2023. The file type that this code will be looking up is xls. I already revised the "tester" sub (f) to reflect it correctly. I did not see anything in the function that speaks to the file type. – deadxcell Apr 22 '21 at 01:52
  • What is the text of the error you get with `cn.openschema` ? Are you able to share one of your files? Occasionally "excel" exports are not actually Excel format but sometimes HTML with and xls extension... – Tim Williams Apr 22 '21 at 02:17
  • The file is an accounting ledger. Unless it is absolutely necessary, I would prefer not to share it. You are correct. It is an HTML with xls extension. Does that change anything? – deadxcell Apr 22 '21 at 16:00
  • Yes that changes a lot - pretty much your only option here is to open each file in turn in Excel and examine the value in the required range until you find the one you want. – Tim Williams Apr 22 '21 at 16:07
  • Well that would explain the errors I am getting. That is unfortunate. I appreciate the help with this and I am sorry if I wasted anyone's time figuring this out. – deadxcell Apr 22 '21 at 17:52
  • If you have many files then it would be worth creating a table to match the filename with the value in A7 (and maybe the file modified date). At least then once you've performed the initial scan you can just do an incremental update as needed. – Tim Williams Apr 22 '21 at 19:50