1

I have this Walkenbach code that everybody uses to pull data from a closed workbook and it's working just fine for me:

Sub ImportData()
    Dim p As String, f As String, s As String, a As String
    p = "\\MyServer\MyFolder\MySubFolder"
    f = "MyWorkbook.xlsx"
    s = "MySheet"
    a = "$A$1"
    GetValue(p, f, s, a)
End Sub

Private Function GetValue(path, file, sheet, ref)
    Dim arg As String
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

What I want to do is determine the interior color of each cell...if the manager of the source spreadsheet has changed the color of a cell, it indicates that the value within is invalid and should not be included in the analysis.

So currently 'arg' is set to...

'\\MyServer\MyFolder\MySubFolder\[MyWorkbook.xlsx]MySheet'!R1C1

and running ExecuteExcel4Macro with that argument pulls the value in A1.

How can I restructure the 'arg' assignment to pull the interior color instead of the value?

If necessary, I can ask the manager to change the font color instead of the interior color to mark data to be excluded.

MBB70
  • 375
  • 2
  • 16
  • According to the [documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.application.executeexcel4macro), it seems like you potentially could use something like `ExecuteExcel4Macro("GET.CELL(42)")` and change the input number there to match up to some value from here https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html – Marcucciboy2 Sep 13 '18 at 15:07
  • 1
    I ran across this post and get.cell is frowned upon. I really wanted to restructure Walkenbach's code to pull interior colors ... https://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color/24383150#24383150 – MBB70 Sep 13 '18 at 15:25
  • Good find, this might help getting it to refer to the other sheet http://sulprobil.com/Get_it_done/IT/Excel_Fun/GET_CELL/get_cell.html – Marcucciboy2 Sep 13 '18 at 15:29
  • But I will go ahead and try to make this work, thanks for your input. – MBB70 Sep 13 '18 at 15:29
  • Yeah, it's a bit difficult to work on since you don't want to open the workbook to get the formatting. Honestly I dont know if the `ExecuteExcel4Macro` will work like we want it to but I'm interested in trying. – Marcucciboy2 Sep 13 '18 at 15:33
  • I think that most would recommend you just open it, get the value, then close it with `screenupdating` off or something just for simplicity since the user doesn't really notice that its been opened – Marcucciboy2 Sep 13 '18 at 15:35
  • 1
    The issue is that many users access the same workbook during the course of the day. Opening read-only would work since I'm not changing the source workbook in this particular method, but I've been there and done that. I wanted to try something new that didn't require "physically" opening the file to pull cell properties. Also, some users get click-happy and activate workbooks that shouldn't be activated during a routine, which (as you probably well know), if all of the workbook objects aren't specifically set by the code, can create problems. – MBB70 Sep 13 '18 at 21:18

0 Answers0