Find and Replace when a File Exists

[scotttiger]

I want to have a find and replace macro for a spreadsheet that uses vlookup to get a file for a particular month


The vlookup function will have a network path like

VLOOKUP("% Return - Month",'C:\DATA\ Spreadsheets\[a_spreadsheet.xls]Jun 14'!$R$5:$T$37,3,0)*100

For every month I need to find and replace the current month (Jun 14) with the next month's spreadsheet (Jul 14)


but if it hasn't been created yet I would like it to be skipped over/ keep the same month.


Any website/guide/code would be great... I'm a complete newby to VB, so far I've tried to look at tutorials on how write the find and replace portion of the code


but the obstacle is knowing if a file exists or does not exist...


If my end product can follow this scheme that would be awesome... or letting me know if this is possible or not possible for Microsoft Excel 2007 :


in each cell of Col A do this
if (C://.../.../.../[spreadsheet.xls]Jul '14 EXISTS)
replace Jun with Jul
else
replace Jun with Jun/ skip / do nothing

Sorry for the lengthy post thanks for reading!

 

[shg]

    If Len(Dir("\\someServer\somePath\someFile.xls")) Then
        ' the file exists
    End If

 

[scotttiger]

    If Len(Dir("\\someServer\somePath\someFile.xls")) Then
        ' the file exists
    End If

Thanks for your reply, but how would I include the month I am looking for in that spreadsheet? Since all the spreadsheets do exist... but I don't know if for a particular month if it exists or not...

 

[shg]

Select col A do Ctrl+G (Go to), tick Formulas, untick everything except Errors, then press OK. That selects all the cells with errors, correct?

Then you can do Find & Replace, and replace "Jul '14" with "Jun '14"

Does that do what you want, aside from not doing it with VBA?

 

[scotttiger]

The problem is I have a lot of network paths ... when I do Find and replace and replace "Jun 14" with "Jul 14" the issue is that most of the time the sheet doesn't have the new month sheet made yet so for each of the cells the "Select Sheet" window would pop up and I have to scroll past x amount of sheets to always select the most recent one for each one... I'll post a screen shot

This is a growing spreadsheet too which might be a pain for the next intern that takes over... I just want things easier for them because it is a huge waste of time

 

[scotttiger]

Maybe I'm approaching this problem the wrong way....

How do I easily update network paths for each month when they're made while leaving the other ones alone...

 

[shg]

How about instead putting the most recent value in the same place (or a workbook-level named range) in each of those workbooks?

 

[scotttiger]

I'm sorry shg, I'm new to excel as well... will a workbook level named range have to be updated manually? If so I don't think that's going to solve the problem :C

 

[scotttiger]

Will this work?

Replace Jun with Jul....

If vlookup return #N/A then

replace jul with jun?

 

[shg]

Umm -- if that works for you, sure.