-1

i’m trying to fill a cell in excel with a Y or N if a file exists.. i think i need a macro because i’m not sure a formula will work. If my files name is \xyzmedia\08242018abcapp\wyoming\archive and would be in cell C10; what code can i use to fill a specific excel cell with a Y or N? in this particular instance, let’s say cell D10. i’ll be doing this for about 8 files per day, so that’s 8 cells that need to be updated. ideally, i’d want them all updated via the same macro.

if i could also have a “Search Files” button embedded on the worksheet to run the macro, that would be AMAZING.

the dates change everyday, so i’m guessing i’d have to change the dates in the 5 macros manually each week, which is fine. but is there a workaround? again, if not, completely ok.

I have NO clue what i’m doing as i’m very new to macros .. but after doing some research, maybe the code would be something like this?? (Check if the file exists using VBA):

Sub test()

thesentence = InputBox("\\xyzmedia\08242018abcapp\wyoming\archive", "Raw Data File")

Range("A1").Value = thesentence

If Dir("thesentence") <> "" Then
    D10 = "Y"
Else
    D10 = "N"
End If

End Sub

please help! thank you!!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
McHenny
  • 1
  • 4
  • Welcome! Please [edit] your question to show what you' tried and what the *specific* issue was. (This isn't a site where you put in your request and get a "free coding service". ;-) Also take a look at what's on-topic in the [help/on-topic], as well a "[ask]" and **how to create a [mcve]**. – ashleedawg Aug 25 '18 at 12:14
  • 1
    Also when choosing tags for your post, it's important to read the tag descriptions (by clicking or hovering over the tag.) I removed [tag:macros] – ashleedawg Aug 25 '18 at 12:17
  • 1
    Thinking about this afterwards I remembered the Excel has a 'hidden' worksheet function leftover from Excel 4.0 that might help you out. See: https://exceloffthegrid.com/excel-function-to-list-files-in-a-folder-without-vba/ – ashleedawg Aug 25 '18 at 12:36
  • hi! thanks for the info! i followed the “Check if a specific file exists within a folder” directions to a tee and still no luck. i’m going crazy lol.. not sure what i’m doing wrong. i keep getting an #N/A error when the file i’m referencing clearly exists in the folder; i even copied and pasted the names to be sure, and put he asterisk at the end of the folder name. no luck! my match function is the exact same as in the directions, just with my own folder names. i’m stuck – McHenny Aug 25 '18 at 18:54
  • ASHLEEDAWG IT WORKED. thank you! how do i upvote you – McHenny Aug 25 '18 at 19:37
  • @ashleedawg pls post ur response in “answers” so i can upvote you! this could get me a promotion – McHenny Aug 26 '18 at 04:30

1 Answers1

0

Here is some code for you ...... to help a bit.

'To check if a particular file exists
'excelFile = False, if it is not an Excel file that is being checked
Public Function isAnExistingFile(ByVal fileNameStr As Variant, Optional ByVal excelFile As Boolean = True) As Boolean
Dim wb As Workbook

isAnExistingFile = True
On Error Resume Next
If Not VarType(fileNameStr) = vbString Then
    isAnExistingFile = False
ElseIf Len(fileNameStr) = 0 Then
    isAnExistingFile = False
ElseIf Len(Dir(fileNameStr)) = 0 Then
    isAnExistingFile = False
ElseIf ((GetAttr(fileNameStr) And vbDirectory) <> vbDirectory) = False Then
    isAnExistingFile = False
Else
    If excelFile Then
        Set wb = Application.Workbooks.Open(Filename:=fileNameStr, UpdateLinks:=0, ReadOnly:=True)
        If wb Is Nothing Then isAnExistingFile = False
        If Not wb Is Nothing Then
            wb.Close False
            Set wb = Nothing
        End If
    End If
End If
Err.Clear: On Error GoTo 0

End Function
Guest
  • 430
  • 2
  • 4