de-concatenating?

[avenue]

(Apologies if this is against etiquette, but this is a repost of a simple question that I feared wouldn't be seen, as it is buried at the end of a long post: http://www.excelforum.com/showthread.php?t=587929 )

I have a macro that goes through a bunch of dbf files and deletes certain among them, according to a certain criterion (which happens to be E2 = 0, though that's irrelevant here). Let's say, of files a_1.dbf thru a_100.dbf, the macro deletes a_7.dbf and a_22.dbf. My question is this: I would also like the macro to delete a few other random files called a_7.prj, a_7.shp, a_22.prj, and a_22.shp. I think this is a simple matter of removing the .dbf extension and concatenating the .prj and .shp extensions to the result, and then killing those files, but I don't know how to remove (de-concatenate?) parts of filenames. Can anyone tell me if there is there a simple way to do this?

Many thanks.

[Trebor76]

Hi,

Try replacing
If Right(f.Name, 7)="XYZ.DBF" Then
in your existing code with
If Right(f.Name, 7)="XYZ.DBF" Or If Right(f.Name, 7)="XYZ.PRJ" Or If Right(f.Name, 7)="XYZ.SHP" Then

Hope this solves the problem.

Kind regards,

Robert

[avenue]

Thank you for the advice. Unfortunately, the .shp and .prj files are not tabular data files and are not readable in Excel. I just want to find a way to delete all the .shp and .prj files associated with the "bad" (i.e. E2 = 0) dbfs. Your code would involve trying to open these other files, which it can't do.

[Trebor76]

If the existing macro works, why not make two copies of it, adjust the copies as required and run them automatically (see below):

Please Login or Register  to view this content.

Hope this helps,

Robert

[avenue]

I'm sorry, I think I wasn't really clear-- the problem with this, again, is the part:

If Right(f.Name, 7) = "XYZ.PRJ" Then
fname = f.Name
Workbooks.Open FolderPath & "\" & fname
If ActiveSheet.Range("F1") = "0" Then

A .prj file has no "F1"; it is not tabular data--it is a GIS projection file that has no relation to Excel and can't be opened by it. Only the dbf files are this kind of data, so they're the only ones that can match the criterion that a particular entry be 0. What I would like to do is take those dbfs that have this entry as zero, and deconcatenate the ".dbf" and concatenate the ".prj" and ".shp" and delete these files (without opening them in a workbook, as they cannot be opened).
Do you know how I might perform this deconcatenation? I just need to be able to perform the following operations on the filename:

a_1.dbf --> a_1 --> a_1.prj

I can do the second of these but not the first. (And note I don't know the number of characters before the dbf, since a_1 is just an example, so I don't think I can use Left( ) )

[Zygan]

Just so i understand so i can hopefully make a solution

assuming
A_1.dbf ---> cell E2= 0 then
A_1.shp
A_1.prj
needs to be deleted

Do these files share common name before the file extension ?
e.g

A_1.dbf always have the files

A_1.shp
A_1.prj


the A_1 part never changes so this could never occur

A_1.dbf relates to

AB1.shp
PK1.prj

are they always the same file name as the original .dbf file ?

[avenue]

Zygan:

That's exactly right--the related files always share a common name before the extension. For instance, if a_417.dbf is one of those to be deleted, I need to figure out how also to delete a_417.prj and a_417.shp.

[Trebor76]

Hi,

Put the following API function into a VBA module:

Please Login or Register  to view this content.

Then following code will delete the three files mentioned (from your C drive) directly from excel if there is a zero in cell E2:

Please Login or Register  to view this content.

Could this be incorporated this into your code?

Kind regards,

Robert