X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Can I rename files in VBA using wildcards?    
 
   
Grateful
post Mar 23 2004, 10:55 PM
Post #1

UtterAccess Addict
Posts: 166
From: Los Angeles, CA



Here's basically what I want to do...

I have thousands of filenames like...

AB8515-Product_Description_is_wrong_format.txt
AB8516-Product_Desc_is_not_right_format.txt
CB8582 - Product_Description_Is_Not_standardized.txt

These filenames consist of a 6-character product code, which is always correct and there is only one filename per product, and then a description that isn't valid.

I have the correct product descriptions and product codes in a database. So I want to rename:

[Product] & [*.*] to [Product] & " - " & [Product_Description]

I was thinking about doing a filesearch for like "AB8515", and then retriving the filename from there to use the NAME statement, but I'm not sure of the code for it all. With the NAME statement, it appears I always have to specify the full filename to rename.

Any help? Thanks.
Edited by: Grateful on 03.23.04.
Go to the top of the page
 
+
TimK
post Mar 23 2004, 11:02 PM
Post #2

UtterAccess VIP
Posts: 6,250
From: Khon Kaen, Thailand



Check this POST out.
smile.gif
Go to the top of the page
 
+
Grateful
post Mar 23 2004, 11:03 PM
Post #3

UtterAccess Addict
Posts: 166
From: Los Angeles, CA



Thanks smile.gif
Go to the top of the page
 
+
WildBird
post Mar 23 2004, 11:04 PM
Post #4

UtterAccess VIP
Posts: 3,174
From: Perth, Australia



I owuld use the scripting.filesystem object. With that you can check the name, including the first n number of characters, and rename it after finding it.
dd a reference to Microsoft Scripting runtime, and dim fs as new scripting.filesystem Will give you a lot of options. Hope this helps,
Go to the top of the page
 
+
TimK
post Mar 24 2004, 03:57 AM
Post #5

UtterAccess VIP
Posts: 6,250
From: Khon Kaen, Thailand



You're welcome.
smile.gif
Go to the top of the page
 
+
Grateful
post Mar 24 2004, 08:47 PM
Post #6

UtterAccess Addict
Posts: 166
From: Los Angeles, CA



OK I revised the code (posted below) for file renaming for my purposes... Now I'm having a slight problem and thought I could get a quick answer here.

Some of my string variables contain invalid filepath characters such as "?", "*", "/" etc. Is there an easy way to search/replace characters within strings? For this example, I want to change...


String = "f:\test\mydir\who can program?.zip" to...
NewString = "f:\test\mydir\who can program.zip" (i.e., remove all question marks)

FOr this...

String = "f:\test\mydir\I want to switch the / to a hyphen.txt" to...
NewString = "f:\test\mydir\I want to switch the - to a hyphen.txt" (i.e., search/replace all slashes for hyphens within a string)


I guess I could write a loop that determine the length of the field, then go through each character and examine it, but there must be an easier way.


Thanks.


****


Private FindFileAndRename()

Dim objFS, ObjFolder, objFiles, ObjF1 As Object
Dim FoundIt As Boolean

FoundIt = False

On Error GoTo BadDirectory

Set objFS = CreateObject("Scripting.FileSystemObject")
Set ObjFolder = objFS.GetFolder(Me!FullDirectory)
Set objFiles = ObjFolder.Files

For Each ObjF1 In objFiles
If Left(ObjF1.Name, 9) = (Me!Productcode) And Right(ObjF1.Name, 3) = "zip" Then
On Error GoTo CannotRename
Name Me!FullDirectory & ObjF1.Name As Me!FullFileName
FoundIt = True
End If
Next

If FoundIt Then GoTo ResetVars Else GoTo CannotFind


CannotFind:

(error box here then goto resetvars)


CannotRename:

(error box here then goto resetvars)


BadDirectory:

(error box here then goto resetvars)


ResetVars:

Set ObjF1 = Nothing
Set objFiles = Nothing
Set ObjFolder = Nothing
Set objFS = Nothing
If FoundIt Then DoCmd.GoToRecord , , acNext

End Sub

Edited by: Grateful on 03.24.04.
Go to the top of the page
 
+
WildBird
post Mar 24 2004, 09:01 PM
Post #7

UtterAccess VIP
Posts: 3,174
From: Perth, Australia



You can do a check for characters with Instr(), and a replace with replace function. You could set up a case statement to handle these.
Go to the top of the page
 
+
Grateful
post Mar 24 2004, 09:14 PM
Post #8

UtterAccess Addict
Posts: 166
From: Los Angeles, CA



Great works...
I used something like:
ToRenameAs = FullFileName
If InStr(1, FullFileName, Chr(63)) Then ToRenameAs = Replace(FullFileName, Chr(63), "", 1, Len(FullFileName))
If InStr(1, FullFileName, Chr(42)) Then ToRenameAs = Replace(FullFileName, Chr(42), "-", 1, Len(FullFileName))

Name Me!FullDirectory & ObjF1.Name As ToRenameAs
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 20th December 2014 - 03:06 PM