The Name function (1 Viewer)

Dave E

Registered User.
Local time
Yesterday, 22:27
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
 
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
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
I had to Google this one.
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.
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:


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.
 
According to this link:
........
The tick / apostrophe is supposedly not allowed for Windows file names.

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.
 
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!)
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.
 
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)
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.
I had to Google this one.
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.
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.
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.
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.
 
@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.
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:
what's the sql of the recordset? also you're missing a slash after C
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:
 
error 53 - file not found
What if you believe the error message:
Code:
If FileExists(TableName) Then
   Name TableName As FolderName
Else
   Debug.Print "not found: " & TableName
End If
 
Last edited:
I don't know why the author of the link thinks so, but most of those characters can be used in files name.
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.
 
What if you believe the error message:
Code:
If FileExists(TableName) Then
   Name TableName As FolderName
Else
   Debug.Print "not found: " & TableName
End If
It isn't about the error message so much as the strange appearance of the table's name over the Statement.
 
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.
I tried your idea of changing Name to VBA.Name without success.
According to this link:


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.
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
 

Users who are viewing this thread

Back
Top Bottom