The Name function

D

Dave E

Registered User.
Local time
Today, 12:08
Joined
Sep 23, 2019
Messages
139
Hi all,
I have never used the Name statement before and I want to rename some filenames to match the path/filename stored in the db.
The format is Name oldname As newname

Code: 
Myset.MoveFirst
Do Until Myset.EOF
    TableName = Myset![Picture]
    FolderName = "C:\Access Files\OurGardenPics\" & Myset![LatinName] & ".jpg"
    Name TableName As FolderName
        MCount = MCount + 1
    Myset.MoveNext
Loop

The code is simple enough, where it uses a recordset which supplies the oldname and the code assembles the newname by taking the [LatinName] and adding it to the path, as below, and loops through the records replacing the filenames (complete with the full path). The recordset is not affected by the process so it doesn't require any Update.

The trouble arises on the firsts record where, although the path/filenames are both valid, an error 53 - file not found pops up.
The two path names are:

oldname = "C:\Access Files\OurGardenPics\Finicia 'Ice Crystals'.jpg"
newname = "C:Access Files\OurGardenPics\Finicia truncata 'Ice Crystals'.jpg"

The oldname tallies with the Folder filename so it should find that one.
The newname is created and the format/syntax etc., appears to be correct.

The odd thing is that I noticed was that when I hover the cursor over the statement - Name, in the Debug mode, (when the error arises and the line of code is highlighted in yellow), I get the Form name appearing... ("ImageTest").

I've tried all that I can think of to solve this one so I'm asking for help.

Any ideas/suggestions/solutions would be appreciated, as always.

Thanks Dave E
 
Dave E said:
Hi all,
I have never used the Name statement before and I want to rename some filenames to match the path/filename stored in the db.
The format is Name oldname As newname

Code: 
Myset.MoveFirst
Do Until Myset.EOF
    TableName = Myset![Picture]
    FolderName = "C:\Access Files\OurGardenPics\" & Myset![LatinName] & ".jpg"
    Name TableName As FolderName
        MCount = MCount + 1
    Myset.MoveNext
Loop

The code is simple enough, where it uses a recordset which supplies the oldname and the code assembles the newname by taking the [LatinName] and adding it to the path, as below, and loops through the records replacing the filenames (complete with the full path). The recordset is not affected by the process so it doesn't require any Update.

The trouble arises on the firsts record where, although the path/filenames are both valid, an error 53 - file not found pops up.
The two path names are:

oldname = "C:\Access Files\OurGardenPics\Finicia 'Ice Crystals'.jpg"
newname = "C:Access Files\OurGardenPics\Finicia truncata 'Ice Crystals'.jpg"

The oldname tallies with the Folder filename so it should find that one.
The newname is created and the format/syntax etc., appears to be correct.

The odd thing is that I noticed was that when I hover the cursor over the statement - Name, in the Debug mode, (when the error arises and the line of code is highlighted in yellow), I get the Form name appearing... ("ImageTest").

I've tried all that I can think of to solve this one so I'm asking for help.

Any ideas/suggestions/solutions would be appreciated, as always.

Thanks Dave E
Click to expand...
Try to change the statement "Name ..." with "VBA.name ..." (Not tested).
Because "name" Is also a property of the form object. If you include VBA. name you'll force to use the statement.
 
Last edited:
what's the sql of the recordset? also you're missing a slash after C
 
The format is Name oldname As newname
Click to expand...
I had to Google this one.
learn.microsoft.com

Name statement (VBA)

Office VBA reference topic
learn.microsoft.com
Never seen anything in VBA with a similar syntax.
If you cannot get this to work you can use the File System Object to rename a file. Similar syntax using the move method.

VBA MoveFile - FileSystemObject - Move files in Excel VBA

The VBA MoveFile function of the FileSystemObject allows you to move one or more files between folders in Excel VBA
analystcave.com analystcave.com
However as pointed out by @Isaac you want to make sure the names are legit and valid. I would think you first need to use the DIR or FSO to determine if the file exists before trying to rename/move it.
 
Make a small adjustment to your code first:
Code: 
Myset.MoveFirst
Do Until Myset.EOF
    TableName = Myset![Picture]
    FolderName = "C:\Access Files\OurGardenPics\" & Myset![LatinName] & ".jpg"
    Debug.Print "Renaming:" & vbNewLine & TableName & vbNewLine & "to:" & vbNewLine & FolderName
    Debug.Print "TableName exists: " & Len(Dir(TableName)) > 0
    Debug.Print "FolderName exists: " & Len(Dir(Left(FolderName, InstrRev(FolderName, "\")),vbDirectory)) > 0
    Debug.Print
    Name TableName As FolderName
        MCount = MCount + 1
    Myset.MoveNext
Loop

Then post back the output from the Immediate Window (Ctrl+G) here.

(NB. the above amendment is aircode, untested!)
 
According to this link:

Characters to Avoid in Filenames and Directories | Michigan Tech

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.
www.mtu.edu

These two names are illegal as file names.

Code: 
oldname = "C:\Access Files\OurGardenPics\Finicia 'Ice Crystals'.jpg"
newname = "C:Access Files\OurGardenPics\Finicia truncata 'Ice Crystals'.jpg"

The tick / apostrophe is supposedly not allowed for Windows file names. The error "File not found" is perhaps misleading because there should not be a file name matching what you used as oldname.
 
The_Doc_Man said:
According to this link:
........
The tick / apostrophe is supposedly not allowed for Windows file names.
Click to expand...

I don't know why the author of the link thinks so, but most of those characters can be used in files name.
For sure, both single quote and apastrophe are among allowed characters.
This one works for me.

SQL: 
Sub test()
 
    Dim FSO As New FileSystemObject
 
    FSO.CopyFile "D:\{Finicia 'Ice &%#Crystals'}.jpg", "D:\{Finicia 'Ice &%#Crystals'}3.jpg"
    Name "D:\{Finicia 'Ice &%#Crystals'}.jpg" As "D:\{Finicia 'Ice &%#Crystals'}4.jpg"
 
End Sub

test.png


In windows file names, only the following characters can not be used. Anything out of these, are allowed.
\ (backslash)
/ (forward slash)
: (colon)
* (asterisk)
? (question mark)
" (double quote)
< (less than)
> (greater than)
| (pipe)
 
Last edited:
@KitaYama - I know you can force the issue through some interfaces. I won't (actually, can't) disagree with you. But some languages and some command line input processors disallow the characters noted in the article.
 
cheekybuddha said:
Make a small adjustment to your code first:
Code: 
Myset.MoveFirst
Do Until Myset.EOF
    TableName = Myset![Picture]
    FolderName = "C:\Access Files\OurGardenPics\" & Myset![LatinName] & ".jpg"
    Debug.Print "Renaming:" & vbNewLine & TableName & vbNewLine & "to:" & vbNewLine & FolderName
    Debug.Print "TableName exists: " & Len(Dir(TableName)) > 0
    Debug.Print "FolderName exists: " & Len(Dir(Left(FolderName, InstrRev(FolderName, "\")),vbDirectory)) > 0
    Debug.Print
    Name TableName As FolderName
        MCount = MCount + 1
    Myset.MoveNext
Loop

Then post back the output from the Immediate Window (Ctrl+G) here.

(NB. the above amendment is aircode, untested!)
Click to expand...
Thanks for that. The code section I submitted is just the crux of the problem.

In previous code, the Stored path/filename ([Picture] in the db MainTable) is copied to a new table, "ImageTest" together with the MainTable [LatinName] for the corresponding record IF its filename doesn't match the MainTable's {LatinName], and offers a corrected name for the Stored name(s) in the new continuous form.

At this point, the option to make the changes is requested, either as single records (tickbox) or by a Select All tickbox and button.

All this works well, without error, until I reach the Name oldfile As newfile Statement which is as listed on the MS website list of functions and Statements.

Thanks for you input. I will examine the code you supplied and try it out.

Dave E
 
The code I posted isn't going to solve your problem(s) - all it will do is try to highlight where the problem may be.
 
KitaYama said:
I don't know why the author of the link thinks so, but most of those characters can be used in files name.
For sure, both single quote and apastrophe are among allowed characters.
This one works for me.

SQL: 
Sub test()
 
    Dim FSO As New FileSystemObject
 
    FSO.CopyFile "D:\{Finicia 'Ice &%#Crystals'}.jpg", "D:\{Finicia 'Ice &%#Crystals'}3.jpg"
    Name "D:\{Finicia 'Ice &%#Crystals'}.jpg" As "D:\{Finicia 'Ice &%#Crystals'}4.jpg"
 
End Sub

View attachment 115658

In windows file names, only the following characters can not be used. Anything out of these, are allowed.
\ (backslash)
/ (forward slash)
: (colon)
* (asterisk)
? (question mark)
" (double quote)
< (less than)
> (greater than)
| (pipe)
Click to expand...
It works with all the filenames that use single apostrophes and have plantname and stored name matching. There are over 400 plants in the db and only a small percentage have thrown up the problem.
MajP said:
I had to Google this one.
learn.microsoft.com

Name statement (VBA)

Office VBA reference topic
learn.microsoft.com
Never seen anything in VBA with a similar syntax.
If you cannot get this to work you can use the File System Object to rename a file. Similar syntax using the move method.

VBA MoveFile - FileSystemObject - Move files in Excel VBA

The VBA MoveFile function of the FileSystemObject allows you to move one or more files between folders in Excel VBA
analystcave.com analystcave.com
However as pointed out by @Isaac you want to make sure the names are legit and valid. I would think you first need to use the DIR or FSO to determine if the file exists before trying to rename/move it.
Click to expand...
xavier.batlle said:
Try to change the statement "Name ..." with "VBA.name ..." (Not tested).
Because "name" Is also a property of the form object. If you include VBA. name you'll force to use the statement.
Click to expand...
It seems odd that MS have a listed statement using Name oldfile As newfile. But the MS site shows examples the same way I used the statement in my code. I will try the VBA.Name statement to see if it makes a difference.
I wonder why, when I hover the cursor over the Statement 'Name' in debug mode, it shows me the table name.
 
The_Doc_Man said:
@KitaYama - I know you can force the issue through some interfaces. I won't (actually, can't) disagree with you. But some languages and some command line input processors disallow the characters noted in the article.
Click to expand...
Sorry, but I didn't force anything. Simply changed the name. Once manually (right click and selecting rename), then from vba.
And OS (Windows), internally doesn't care about which languages it's running on. It follows the same set of rules.
But as long as you're aware that list is not accurate, it's OK.
 
Last edited:
Isaac said:
what's the sql of the recordset? also you're missing a slash after C
Click to expand...
The query for the table "ImageTest" is: SELECT ImageTest.Tot, ImageTest.ID, ImageTest.LatinName, ImageTest.Picture, ImageTest.Corrected, ImageTest.Correct FROM ImageTest ORDER BY ImageTest.Tot;

Hmm...there is a slash after the C:
 
Dave E said:
error 53 - file not found
Click to expand...
What if you believe the error message:
Code: 
If FileExists(TableName) Then
   Name TableName As FolderName
Else
   Debug.Print "not found: " & TableName
End If

VBA - Check if a File Exists or Not

Two approaches you can use in VBA to determine whether a file exists, or not. One using Dir and the other using the File System Object's FileExists method.
www.devhut.net
 
Last edited:
KitaYama said:
I don't know why the author of the link thinks so, but most of those characters can be used in files name.
Click to expand...
He was not talking about just Windows. He was talking about all the other operating systems out there that interact with the web. He was also talking best practice. It is really poor practice to include special characters in file and folder names even if Windows allows them. And the OP is paying the price.
 
ebs17 said:
What if you believe the error message:
Code: 
If FileExists(TableName) Then
   Name TableName As FolderName
Else
   Debug.Print "not found: " & TableName
End If

VBA - Check if a File Exists or Not

Two approaches you can use in VBA to determine whether a file exists, or not. One using Dir and the other using the File System Object's FileExists method.
www.devhut.net
Click to expand...
It isn't about the error message so much as the strange appearance of the table's name over the Statement.
 
xavier.batlle said:
Try to change the statement "Name ..." with "VBA.name ..." (Not tested).
Because "name" Is also a property of the form object. If you include VBA. name you'll force to use the statement.
Click to expand...
I tried your idea of changing Name to VBA.Name without success.
The_Doc_Man said:
According to this link:

Characters to Avoid in Filenames and Directories | Michigan Tech

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.
www.mtu.edu

These two names are illegal as file names.

Code: 
oldname = "C:\Access Files\OurGardenPics\Finicia 'Ice Crystals'.jpg"
newname = "C:Access Files\OurGardenPics\Finicia truncata 'Ice Crystals'.jpg"

The tick / apostrophe is supposedly not allowed for Windows file names. The error "File not found" is perhaps misleading because there should not be a file name matching what you used as oldname.
Click to expand...
These two lines were typed in and the newname has a missing backslash for the newname.
That oldname (that which is stored in the db) has been used, in the syntax shown above, without error, for many years.
 
I use FSO to move or copy files. I check for a matching name in the destination folder and if found append a number to the file name and check again until it is not found. Then do the copy/move.

Code: 
Public Sub MoveFileRename(SourceFile As String, DestFolder As String, Optional CopyF As Boolean = False)

    If Right(DestFolder, 1) <> "\" Then DestFolder = DestFolder & "\"
    
    Dim fso As New FileSystemObject
    Dim pthA As String, pthB As String, pthC As String, Fpath As String
    Dim TName As String, Ext As String, i As Integer
    
    pthA = SourceFile
    pthB = DestFolder

    TName = fso.GetBaseName(pthA)
    Ext = fso.GetExtensionName(pthA)
    
    pthC = fso.BuildPath(pthB, TName & "." & Ext)

    If fso.FileExists(pthC) Then
    
        For i = 1 To 100
            pthC = fso.BuildPath(pthB, TName & "(" & i & ")" & "." & Ext)
            If Not fso.FileExists(pthC) Then
                Fpath = pthC
                Exit For
            End If
        Next
    Else
        Fpath = pthB
    End If

    If CopyF = True Then
        fso.CopyFile pthA, Fpath, False
    Else
        fso.MoveFile pthA, Fpath
    End If
    
End Sub
 
Thanks for that. It is an interesting routine and I will take it away and try it out.

Dave E
 
You must log in or register to reply here.

Similar threads

T
Solved Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
378
June7
June7
S
Rename multiple parts of the same file name for all files in a directory
Replies
1
Views
866
theDBguy
theDBguy
D
Solved Returning a value from the Funtion
Replies
2
Views
396
donsi
D
I
Making pivots in xlsx files in Access VBA
Replies
2
Views
812
JHB
J
W
Do Loop for renaming files in folder
Replies
5
Views
2,750
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom