Supress prompt for file

Hi all

Not sure if this has a general or VBA only solution, but I'll start here.

I have a workbook that needs to pull data from another, closed workbook, based on dynamic references.

I of course found indirect didn't work on the constructed string, so I built a small piece of code that basically replaced the formula of cell B with the text of cell A which works a treat.

My problem is that if the closed work book doesn't exist I get a prompt asking me to browse for the file I can cancel past it and it causes no problems, but it's very irritating, I tried an iferror() which worked in filling the cell with a "Not yet available" string, but the prompt still came up.

Is there a way to prevent it. One suggestion from a colleague has been to create blanks of the files and then have them overwritten, which would work but seems a hassle.

Thanks in advance.
Sig.

I don't have the code to hand but from what I can remember

        Cells(1,1).formula= _
        Cells(1,2).text

It's basically a work around for the fact that Indirect won't pull data from closed workbooks it sets the formula of a cell from a text string along the lines of ="=C;/blah blah" & date & ."xls". I have an array aswell that basically applies the same logic to alot of cells, a = b, c = d, etc etc it works perfectly.

It's to take the data from a daily spreadsheet and collate it into a single spreadsheet weekly, it obviously gives me the prompt if it's any other day but friday. I want it to give me the data for what it has and populate the rest with "Not available yet"

I was hoping to see your code so I could provide a very specific solution. Such as it is, I will provide a general solution.

This assumes that the file you are referencing is in the same folder as the file you are referencing it from. It puts an error message instead of a link if the file is not found.

   Dim fileName As String
   Dim ref As Range
   
   ' Full reference is in B1
   ' It should look just like it would appear in a formula:
   '            '[external referencedx.xlsx]Sheet1'!$A$1
   Set ref = Range("B1")
   
   ' Get the file name from the cell
   fileName = Mid(ref, InStr(1, ref, "[") + 1, InStr(1, ref, "]") - InStr(1, ref, "[") - 1)
   
   ' Does the file exist?
   If Dir(ThisWorkbook.Path & "\" & fileName) <> "" Then
   
      ' You have to add the single quote after the "=" because
      ' when you include it in the cell text, Excel interprets it as a
      ' flag to treat the cell as text, and does not return it as
      ' part of the cell's value
     Range("A1").Formula = "='" & Range("B1")
   
   Else
   
      Range("A1") = "NOT FOUND!"
      
   End If

That should work and I'll give it a go when I get home from work, though if you're interested I'll be able to post the full code then as well, in about 2 or so hours.

Thanks
Sig

Originally Posted by sigfreid

That should work and I'll give it a go when I get home from work, though if you're interested I'll be able to post the full code then as well, in about 2 or so hours.

No need to post the code unless you have problems figuring out how to implement what I provided.