| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining Whether File Exists
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determining Whether File Exists
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determining if a value already exists in column(s) | Excel Discussion (Misc queries) | |||
| Determining if a named range exists | Excel Programming | |||
| Determining whether a named textbox on a chart exists | Excel Programming | |||
| Determining if a Worksheet Exists | Excel Programming | |||
| Determining if a worksheet exists within a workbook | Excel Programming | |||
Hybrid Mode
