Greetings!
I have a macro that opens files for which the path is indicated in a range,
say A1:A50. Before I run the macro, I would like to test whether the file
paths are entered correctly. I'm trying to create a macro that would test
whether the files contained in cells A1:A10 exist, but cant come up with
anything.
Any insight you provide would be greatly appreciated.
Sincerely,
Magnivy
Try something like
Dim Rng As Range
For Each Rng In Range("A1:A10")
If Dir(Rng.Text) <> "" Then
' file exists
Else
' file doesn't exist
End If
Next Rng
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> Greetings!
>
> I have a macro that opens files for which the path is indicated
> in a range,
> say A1:A50. Before I run the macro, I would like to test
> whether the file
> paths are entered correctly. I'm trying to create a macro that
> would test
> whether the files contained in cells A1:A10 exist, but cant
> come up with
> anything.
>
> Any insight you provide would be greatly appreciated.
>
> Sincerely,
>
> Magnivy
Better code:
Dim Rng As Range
For Each Rng In Range("A1:A10")
If Rng.Text <> "" Then
If Dir(Rng.Text) <> "" Then
' file exists
Else
' file doesn't exist
End If
End If
Next Rng
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Chip Pearson" <chip@cpearson.com> wrote in message
news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> Try something like
>
>
> Dim Rng As Range
> For Each Rng In Range("A1:A10")
> If Dir(Rng.Text) <> "" Then
> ' file exists
> Else
> ' file doesn't exist
> End If
> Next Rng
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
>> Greetings!
>>
>> I have a macro that opens files for which the path is
>> indicated in a range,
>> say A1:A50. Before I run the macro, I would like to test
>> whether the file
>> paths are entered correctly. I'm trying to create a macro that
>> would test
>> whether the files contained in cells A1:A10 exist, but cant
>> come up with
>> anything.
>>
>> Any insight you provide would be greatly appreciated.
>>
>> Sincerely,
>>
>> Magnivy
>
>
Chip, thanks a lot for your help. I've been trying to run your macro, but an
error messege pops up. Its says "Run-time error 52: bad file name or number."
Would you know whats causing it?
"Chip Pearson" wrote:
> Better code:
>
> Dim Rng As Range
> For Each Rng In Range("A1:A10")
> If Rng.Text <> "" Then
> If Dir(Rng.Text) <> "" Then
> ' file exists
> Else
> ' file doesn't exist
> End If
> End If
> Next Rng
>
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> > Try something like
> >
> >
> > Dim Rng As Range
> > For Each Rng In Range("A1:A10")
> > If Dir(Rng.Text) <> "" Then
> > ' file exists
> > Else
> > ' file doesn't exist
> > End If
> > Next Rng
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> > "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> > news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> >> Greetings!
> >>
> >> I have a macro that opens files for which the path is
> >> indicated in a range,
> >> say A1:A50. Before I run the macro, I would like to test
> >> whether the file
> >> paths are entered correctly. I'm trying to create a macro that
> >> would test
> >> whether the files contained in cells A1:A10 exist, but cant
> >> come up with
> >> anything.
> >>
> >> Any insight you provide would be greatly appreciated.
> >>
> >> Sincerely,
> >>
> >> Magnivy
> >
> >
>
>
>
I can't reproduce the problem you describe. Usually, you use that
error when performing file IO operations. Are you getting the
error with the EXACT code I posted, or have you modified the
code? Post your code. What is in the cell referenced by Rng when
the error occurs?
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
news:C6026001-3BC6-4493-BB0F-295C9C3A6B7E@microsoft.com...
> Chip, thanks a lot for your help. I've been trying to run your
> macro, but an
> error messege pops up. Its says "Run-time error 52: bad file
> name or number."
>
> Would you know whats causing it?
>
> "Chip Pearson" wrote:
>
>> Better code:
>>
>> Dim Rng As Range
>> For Each Rng In Range("A1:A10")
>> If Rng.Text <> "" Then
>> If Dir(Rng.Text) <> "" Then
>> ' file exists
>> Else
>> ' file doesn't exist
>> End If
>> End If
>> Next Rng
>>
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Chip Pearson" <chip@cpearson.com> wrote in message
>> news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
>> > Try something like
>> >
>> >
>> > Dim Rng As Range
>> > For Each Rng In Range("A1:A10")
>> > If Dir(Rng.Text) <> "" Then
>> > ' file exists
>> > Else
>> > ' file doesn't exist
>> > End If
>> > Next Rng
>> >
>> >
>> > --
>> > Cordially,
>> > Chip Pearson
>> > Microsoft MVP - Excel
>> > Pearson Software Consulting, LLC
>> > www.cpearson.com
>> >
>> >
>> >
>> > "Magnivy" <Magnivy@discussions.microsoft.com> wrote in
>> > message
>> > news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
>> >> Greetings!
>> >>
>> >> I have a macro that opens files for which the path is
>> >> indicated in a range,
>> >> say A1:A50. Before I run the macro, I would like to test
>> >> whether the file
>> >> paths are entered correctly. I'm trying to create a macro
>> >> that
>> >> would test
>> >> whether the files contained in cells A1:A10 exist, but cant
>> >> come up with
>> >> anything.
>> >>
>> >> Any insight you provide would be greatly appreciated.
>> >>
>> >> Sincerely,
>> >>
>> >> Magnivy
>> >
>> >
>>
>>
>>
Even better:
Function bFileExists(ByVal sFile As String) As Boolean
Dim lAttr As Long
On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0
End Function
RBS
"Chip Pearson" <chip@cpearson.com> wrote in message
news:ON3wjwniGHA.1264@TK2MSFTNGP05.phx.gbl...
> Better code:
>
> Dim Rng As Range
> For Each Rng In Range("A1:A10")
> If Rng.Text <> "" Then
> If Dir(Rng.Text) <> "" Then
> ' file exists
> Else
> ' file doesn't exist
> End If
> End If
> Next Rng
>
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
>> Try something like
>>
>>
>> Dim Rng As Range
>> For Each Rng In Range("A1:A10")
>> If Dir(Rng.Text) <> "" Then
>> ' file exists
>> Else
>> ' file doesn't exist
>> End If
>> Next Rng
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
>> news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
>>> Greetings!
>>>
>>> I have a macro that opens files for which the path is indicated in a
>>> range,
>>> say A1:A50. Before I run the macro, I would like to test whether the
>>> file
>>> paths are entered correctly. I'm trying to create a macro that would
>>> test
>>> whether the files contained in cells A1:A10 exist, but cant come up with
>>> anything.
>>>
>>> Any insight you provide would be greatly appreciated.
>>>
>>> Sincerely,
>>>
>>> Magnivy
>>
>>
>
>
Sometimes the contents of the cell are enough to break the dir() command.
I like this modified version of Chip's routine:
Dim Rng As Range
dim TestStr as string
For Each Rng In Range("A1:A10").cells
If Rng.Text <> "" Then
teststr = ""
on error resume next
teststr = dir(rng.text)
on error goto 0
If teststr <> "" Then
' file exists
Else
' file doesn't exist
End If
End If
Next Rng
Magnivy wrote:
>
> Chip, thanks a lot for your help. I've been trying to run your macro, but an
> error messege pops up. Its says "Run-time error 52: bad file name or number."
>
> Would you know whats causing it?
>
> "Chip Pearson" wrote:
>
> > Better code:
> >
> > Dim Rng As Range
> > For Each Rng In Range("A1:A10")
> > If Rng.Text <> "" Then
> > If Dir(Rng.Text) <> "" Then
> > ' file exists
> > Else
> > ' file doesn't exist
> > End If
> > End If
> > Next Rng
> >
> >
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> > > Try something like
> > >
> > >
> > > Dim Rng As Range
> > > For Each Rng In Range("A1:A10")
> > > If Dir(Rng.Text) <> "" Then
> > > ' file exists
> > > Else
> > > ' file doesn't exist
> > > End If
> > > Next Rng
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > >
> > > "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> > > news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> > >> Greetings!
> > >>
> > >> I have a macro that opens files for which the path is
> > >> indicated in a range,
> > >> say A1:A50. Before I run the macro, I would like to test
> > >> whether the file
> > >> paths are entered correctly. I'm trying to create a macro that
> > >> would test
> > >> whether the files contained in cells A1:A10 exist, but cant
> > >> come up with
> > >> anything.
> > >>
> > >> Any insight you provide would be greatly appreciated.
> > >>
> > >> Sincerely,
> > >>
> > >> Magnivy
> > >
> > >
> >
> >
> >
--
Dave Peterson
Chip,
I see why it wasnt working out. The syntax of the file path was incorrect.
It now works great. Thank you very much for your help!
Magnivy
"Chip Pearson" wrote:
> I can't reproduce the problem you describe. Usually, you use that
> error when performing file IO operations. Are you getting the
> error with the EXACT code I posted, or have you modified the
> code? Post your code. What is in the cell referenced by Rng when
> the error occurs?
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> news:C6026001-3BC6-4493-BB0F-295C9C3A6B7E@microsoft.com...
> > Chip, thanks a lot for your help. I've been trying to run your
> > macro, but an
> > error messege pops up. Its says "Run-time error 52: bad file
> > name or number."
> >
> > Would you know whats causing it?
> >
> > "Chip Pearson" wrote:
> >
> >> Better code:
> >>
> >> Dim Rng As Range
> >> For Each Rng In Range("A1:A10")
> >> If Rng.Text <> "" Then
> >> If Dir(Rng.Text) <> "" Then
> >> ' file exists
> >> Else
> >> ' file doesn't exist
> >> End If
> >> End If
> >> Next Rng
> >>
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Chip Pearson" <chip@cpearson.com> wrote in message
> >> news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> >> > Try something like
> >> >
> >> >
> >> > Dim Rng As Range
> >> > For Each Rng In Range("A1:A10")
> >> > If Dir(Rng.Text) <> "" Then
> >> > ' file exists
> >> > Else
> >> > ' file doesn't exist
> >> > End If
> >> > Next Rng
> >> >
> >> >
> >> > --
> >> > Cordially,
> >> > Chip Pearson
> >> > Microsoft MVP - Excel
> >> > Pearson Software Consulting, LLC
> >> > www.cpearson.com
> >> >
> >> >
> >> >
> >> > "Magnivy" <Magnivy@discussions.microsoft.com> wrote in
> >> > message
> >> > news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> >> >> Greetings!
> >> >>
> >> >> I have a macro that opens files for which the path is
> >> >> indicated in a range,
> >> >> say A1:A50. Before I run the macro, I would like to test
> >> >> whether the file
> >> >> paths are entered correctly. I'm trying to create a macro
> >> >> that
> >> >> would test
> >> >> whether the files contained in cells A1:A10 exist, but cant
> >> >> come up with
> >> >> anything.
> >> >>
> >> >> Any insight you provide would be greatly appreciated.
> >> >>
> >> >> Sincerely,
> >> >>
> >> >> Magnivy
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Dave,
I see why Chip's wasnt working out. The syntax of the file path was
incorrect. It now works great. Thank you very much for your help!
Magnivy
"Dave Peterson" wrote:
> Sometimes the contents of the cell are enough to break the dir() command.
>
> I like this modified version of Chip's routine:
>
> Dim Rng As Range
> dim TestStr as string
> For Each Rng In Range("A1:A10").cells
> If Rng.Text <> "" Then
>
> teststr = ""
> on error resume next
> teststr = dir(rng.text)
> on error goto 0
>
> If teststr <> "" Then
> ' file exists
> Else
> ' file doesn't exist
> End If
> End If
> Next Rng
>
>
>
> Magnivy wrote:
> >
> > Chip, thanks a lot for your help. I've been trying to run your macro, but an
> > error messege pops up. Its says "Run-time error 52: bad file name or number."
> >
> > Would you know whats causing it?
> >
> > "Chip Pearson" wrote:
> >
> > > Better code:
> > >
> > > Dim Rng As Range
> > > For Each Rng In Range("A1:A10")
> > > If Rng.Text <> "" Then
> > > If Dir(Rng.Text) <> "" Then
> > > ' file exists
> > > Else
> > > ' file doesn't exist
> > > End If
> > > End If
> > > Next Rng
> > >
> > >
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > > "Chip Pearson" <chip@cpearson.com> wrote in message
> > > news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> > > > Try something like
> > > >
> > > >
> > > > Dim Rng As Range
> > > > For Each Rng In Range("A1:A10")
> > > > If Dir(Rng.Text) <> "" Then
> > > > ' file exists
> > > > Else
> > > > ' file doesn't exist
> > > > End If
> > > > Next Rng
> > > >
> > > >
> > > > --
> > > > Cordially,
> > > > Chip Pearson
> > > > Microsoft MVP - Excel
> > > > Pearson Software Consulting, LLC
> > > > www.cpearson.com
> > > >
> > > >
> > > >
> > > > "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> > > > news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> > > >> Greetings!
> > > >>
> > > >> I have a macro that opens files for which the path is
> > > >> indicated in a range,
> > > >> say A1:A50. Before I run the macro, I would like to test
> > > >> whether the file
> > > >> paths are entered correctly. I'm trying to create a macro that
> > > >> would test
> > > >> whether the files contained in cells A1:A10 exist, but cant
> > > >> come up with
> > > >> anything.
> > > >>
> > > >> Any insight you provide would be greatly appreciated.
> > > >>
> > > >> Sincerely,
> > > >>
> > > >> Magnivy
> > > >
> > > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>
RB,
Thank you for your help! For function works great ! Would you mind to
briefly explain how does the fifth line work ["bFileExists = (Err.Number = 0)
And ((lAttr And vbDirectory) = 0)"]. I'm new to creating custom functions and
am trying to learn as much as possible.
Thanks a lot!
Magnivy
"RB Smissaert" wrote:
> Even better:
>
> Function bFileExists(ByVal sFile As String) As Boolean
>
> Dim lAttr As Long
>
> On Error Resume Next
> lAttr = GetAttr(sFile)
> bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
> On Error GoTo 0
>
> End Function
>
>
> RBS
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:ON3wjwniGHA.1264@TK2MSFTNGP05.phx.gbl...
> > Better code:
> >
> > Dim Rng As Range
> > For Each Rng In Range("A1:A10")
> > If Rng.Text <> "" Then
> > If Dir(Rng.Text) <> "" Then
> > ' file exists
> > Else
> > ' file doesn't exist
> > End If
> > End If
> > Next Rng
> >
> >
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:eQ5wWoniGHA.3496@TK2MSFTNGP04.phx.gbl...
> >> Try something like
> >>
> >>
> >> Dim Rng As Range
> >> For Each Rng In Range("A1:A10")
> >> If Dir(Rng.Text) <> "" Then
> >> ' file exists
> >> Else
> >> ' file doesn't exist
> >> End If
> >> Next Rng
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >> "Magnivy" <Magnivy@discussions.microsoft.com> wrote in message
> >> news:892F592D-4F25-458E-B6CB-395F0BE409CC@microsoft.com...
> >>> Greetings!
> >>>
> >>> I have a macro that opens files for which the path is indicated in a
> >>> range,
> >>> say A1:A50. Before I run the macro, I would like to test whether the
> >>> file
> >>> paths are entered correctly. I'm trying to create a macro that would
> >>> test
> >>> whether the files contained in cells A1:A10 exist, but cant come up with
> >>> anything.
> >>>
> >>> Any insight you provide would be greatly appreciated.
> >>>
> >>> Sincerely,
> >>>
> >>> Magnivy
> >>
> >>
> >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks