We help IT Professionals succeed at work.

MS Access - VBA - Take file name from Source to Destination path in File Dialog Picker

WS
WS asked
on
Hey,

I have a form that have two text boxes as Source and Target. The Source take the source path and the destination textbox xopy the file to the destination path. Copying file is working well, i want that when the user copy the file from Source to destination in file dialog picker it also take the file name when the destination is open from source with it's extension. Is there a way to achieve this?

Private Sub txtSource_Click()
 Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    
    Set Dialog = FileDialog(msoFileDialogFilePicker)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtSource.Value)
        .Title = "Select file to copy"
        Selected = .Show
        If Selected <> 0 Then
            Me!txtSource.Value = .SelectedItems.Item(1)
        End If
    End With
    
    Cancel = True
End Sub

Private Sub txtTarget_Click()
Dim Dialog      As FileDialog
    
    Dim Selected    As Long
    Set Dialog = FileDialog(msoFileDialogSaveAs)
    With Dialog
        .AllowMultiSelect = False
        .InitialFileName = Nz(Me!txtTarget.Value)
        .Title = "Name saved file"
         
        Selected = .Show
        If Selected <> 0 Then
         Me!txtTarget.Value = .SelectedItems.Item(1)
         TargetFile = .SelectedItems.Item(1)
            If Not IsNull(Me!txtSource.Value) Then
                 FileCopy Me!txtSource.Value, Me!txtTarget.Value
                 Me.txtTarget = Split(TargetFile, ":")(1)
            End If
           ' Application.FollowHyperlink Me.txtTarget
            
        End If
    End With
    
    Cancel = True
   
End Sub

Open in new window


Also as shown in first image to copy file the file name is test, i want that when the other target location open there file name test should come automatically with extension also.

Thank you.
getfilename1.png
getfilename2.png
Comment
Watch Question

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You are close. Change to:

With Dialog
    .AllowMultiSelect = False
    .InitialFileName = Nz(Me!txtSource.Value)

Open in new window

/gustav
WSStudent

Author

Commented:
it works.

Any error pop up if you can guide me in this: it's run time error says "Permission Denied contact Administrator", i am the administrator of the server and the write permission is set to yes. When i manually copy the file it's working but when i try to copy using this VBA code it's giving this error. Although considering the file gets copied but still it's poping up this error, do you have any idea about this?

Thank you Gustav.
Máté FarkasDatabase Developer and Administrator
Commented:
I don't exactly understand your problem because your code works fine.
You did not ask any question or problem.
In SaveAs dialog you have to set InitialFileName property to show a default filename but while your txtTarget.Value is empty it will show an empty filename. You probably have to replace this:
.InitialFileName = Nz(Me!txtTarget.Value)

Open in new window

to this:
.InitialFileName = Nz(Me!txtTarget.Value, "Test.xlsx")

Open in new window

and see what happens.
WSStudent

Author

Commented:
As you can see in the image the file is copied as i have write permission. Also when i manually tried to copy its working without any error.Why is this error coming? error.png
WSStudent

Author

Commented:
@Mate, the issue was that the initial file name was empty and i wanted the file name to be the one from source in the file dialog, the solution which Gustav mention start working as in the target location i was taking the target name file, it should be the initial filename from Source.

I asked a question, i was close just a bit problem which Gustav solution solved.

Sorry for not explaining well.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK.
The file permission error is completely separate issue - so don't blame your code for this.

/gustav
WSStudent

Author

Commented:
yeah but why is this coming just in Access ? like if i copy manually it doesn't appear. The code which i mention is the only code im using to copy. Is there anything other than copying also?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Perhaps the path is listed (or not listed) in:

Settings, Center for Security etc., Settings for Center for Security etc., Trusted Paths

/gustav
WSStudent

Author

Commented:
Thank you.

For the other error i think i should ask another question as that is something different i guess.

Thank you.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
WS, for the permissions, if you are sure you have sufficient Windows privileges, perhaps:

File, Options, Trust Center, Trust Center Settings, Trusted Locations --> check: Allow Trusted Locations on my network

?

please let us know if this works, thanks

have an awesome day,
crystal
WSStudent

Author

Commented:
Thank you Crystal, Allow Trusted Locations on my network is set to true like the it's checked but still the same. Anything else to check?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome. I notice you are writing to N:\ ... did you also do as Gustav suggested and set N:\ as a trusted location? same dialog box -- "Add new location" -- and when you set that, be sure to check "Subfolders of this location are also trusted", if you want to be able to use any file on the drive
WSStudent

Author

Commented:
yes the server is mapped to N drive. i try setting as Gustav said and it show this, why is this not getting trusted location here??? Kindly Look at the attach image.
error1.png
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
apparently you are blocked from using the root of N:

make a directory and use that instead.  Perhaps something like N:\temp
then the file would be N:\temp\test3.pdf

... or maybe you already have a folder you can use?
WSStudent

Author

Commented:
I am the Administrator of this server so how could i be blocked from using root? i tries with directory also as N:\GLO which is there and still it gives the same error. Is there anything i should change in server ? The permission for this N:\ are set to Read,Write and Execute. Yes it's not allowed to modify because in that case user can delete the file also which is a huge mess. But as the write permission and other are enable for this folder it should work and mark as trusted.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Hard to guess without being there and exploring ... what about using a different drive letter? What is the N: drive mapped to?
WSStudent

Author

Commented:
N drive is mapped to Server Address like \\163.xx.xx.x\D, first is the server address and in the server there is a folder D which is mapped to my PC.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
>>  folder D which is mapped to my PC <<

can you not use a local location instead then?  do you have any local drives or paths that automatically synchronize to the server?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Even if you are administrator, you should normally not run as such. Thus, the shared folder for this purpose should be granted rights for "Users" (or "Domain Users" if on a domain).

/gustav
WSStudent

Author

Commented:
@Crystal, As it is mapped to my local PC as N:\ so it's a local path which is synchronize as i add something here it is there also on server, isn't it?

@Gustav,
the shared folder for this purpose should be granted rights for "Users" (or "Domain Users" if on a domain).
how can i do that? how can i grant right?