Object Required

[Ark68]

I am using this code below to check to see if a file exists in a directory as specified by a user.

Sub find_file()
  Dim sFile1 As String
  If Target.Address <> "$E$16" Then Exit Sub
  sFile1 = "E:\SportsOps 2009\SportsOps 2009\Data\" & Format(Target, "d-mmm-yy") & ".xls"
  If Dir(sFile1) = "" Then
    MsgBox "Error"
    Exit Sub
  End If
MsgBox "OK."

End Sub

I am getting an error ("Object Required.") from the line highlighted in red.

As always, any help would be welcomed.

 

[Weaver]

where is the value of the object 'target' defined in this code? Is it a variable you're meant to be passing?

 

[Ark68]

Hmmm ... Target is a variable based on the cell value of E16.
I'm kinda lost.

 

[Weaver]

where is it defined?

 

[Ark68]

OK excelr8r ...

I gotta plea ignorance (long day). I'm uncertian as to what you mean by defining it.

Jenn

 

[Weaver]

okay, the variable target has to equal something, in this case it's going to have to represent a range

like

set target = range("A1:N77")

for example

Your code doesn't appear to tell VB what the value of target is i.e there's no definition.

My original question assumed you'd 'defined' it elsewhere and were failing to pass it to your subroutine

 

[Ark68]

Ok ... thats what I kinda thought you were going with this.
I have re-written the code with your suggestion.

Sub find_file()
  Dim sFile1 As String
  Dim target As Date
  target = Range("$E$16")
  If target.Address <> "$E$16" Then Exit Sub
  sFile1 = "E:\SportsOps 2009\SportsOps 2009\Data\" & Format(target, "d-mmm-yy") & ".xls"
  If Dir(sFile1) = "" Then
    MsgBox "Error"
    Exit Sub
  End If
    MsgBox "OK."

End Sub

Now receiving compilation error "Invalid qualifier." from the reference to 'target' in

If target.Address <> "$E$16" Then Exit Sub

.

Jenn

 

[Weaver]

If you're hard coding target to equal a given range, I'm assuming you always want the code to execute.

Why not test for blank or no data in your range

Sub find_file()
  Dim sFile1 As String
  Dim target As Date
  Set target = Range("$E$16")
  If target.Value = "" Then Exit Sub
  sFile1 = "E:\SportsOps 2009\SportsOps 2009\Data\" & Format(target, "d-mmm-yy") & ".xls"
  If Dir(sFile1) = "" Then
    MsgBox "Error"
    Exit Sub
  End If
    MsgBox "OK."

End Sub