Include PK in destination filename with CopyFile (1 Viewer)

Local time
Tomorrow, 00:25
Joined
Aug 3, 2005
Messages
66
Hello,

It is 2am on a Sunday so please be nice.

The form has a txtOriginalFile control. This is a hyperlink to a file at it's original location\folder.

The record source's PK is in control txtMyPK.

The following code works as expected and copies the file to the destination folder (myFolder).

I need help with this part:

Original file = C:\Folder_A\myFile.pdf
Destination = C:\Folder_B\321_myFile.pdf (where 321 is the PK + underscore).


Code:
Private Sub btnCopyFile_Click()

Dim myFilePath 'original file
Dim myFolder ' destination folder

myFolder = "C:\Folder_B\"

Set FSO = CreateObject("Scripting.FilesystemObject")

myFilePath = Me.txtOriginalFile 
'here i was thinking something like:
'myFilePath = Me.txtMyPK & "_" & Me.txtOriginalFile
'but ofcourse then i get:
'321_C:\Folder_A\myFile.pdf
     
If FSO.FileExists(myFilePath) Then 'overwrite
FSO.CopyFile myFilePath, myFolder, True
End If

End Sub

Please help.

Thank you,
Jamie
 

AlexHedley

Registered User.
Local time
Today, 22:25
Joined
Aug 28, 2012
Messages
171
Code:
Private Sub btnCopyFile_Click()

Dim myFilePath 'original file
Dim myFolder ' destination folder

myFolder = "C:\Folder_B\"

Set FSO = CreateObject("Scripting.FilesystemObject")

dim fileName
fileName.GetFileName(Me.txtOriginalFile)

myFilePath = myFolder & Me.txtMyPK & "_" & fileName
     
If FSO.FileExists(myFilePath) Then 'overwrite
FSO.CopyFile myFilePath, myFolder, True
End If

End Sub
 
Local time
Tomorrow, 00:25
Joined
Aug 3, 2005
Messages
66
Hi Alex, thank you for the reply.

I get 424 - Object Required on this line:

Code:
fileName.GetFileName(Me.txtOriginalFile)

(But then again I haven't slept since friday night)

I'll post back when I figured it out.

Thanks again.
Jamie.


Code:
Private Sub btnCopyFile_Click()

Dim myFilePath 'original file
Dim myFolder ' destination folder

myFolder = "C:\Folder_B\"

Set FSO = CreateObject("Scripting.FilesystemObject")

dim fileName
fileName.GetFileName(Me.txtOriginalFile)

myFilePath = myFolder & Me.txtMyPK & "_" & fileName
     
If FSO.FileExists(myFilePath) Then 'overwrite
FSO.CopyFile myFilePath, myFolder, True
End If

End Sub
 

StarGrabber

Junior App. Developer
Local time
Today, 23:25
Joined
Oct 21, 2012
Messages
165
Sorry Alex for jumping in but... was it too late for you, too?

You have certainly meant:
Code:
Dim [B]str[/B]FileName as String
[B]str[/B]FileName = FSO.GetFileName(Me.txtOriginalFile)

Why is that?

Because "FileName" is a VBA-reserved expression which confuses the interpreter, and... "GetFileName" is a method of the filesystem object and not of "fileName", so you should refer to that one when calling the method. ;)
 
Local time
Tomorrow, 00:25
Joined
Aug 3, 2005
Messages
66
Hello again.

For what it's worth, and if someone else can benefit from this:

As simple as it might seem, I couldn't get the code from previous threats to work. Thank you for your feedback anyway.

I'm sure this is a very 'crude' way and there's probably a more efficient way of doing it.

But here's the code that does work for me and that I understand.
(Credit to the authors of the original code snippets where applicable)

-----------------------------

Example (Open the file):

1. Open a file from any location, eg. Folder_A
2. Place it's Path in a control named : txtOrginalPath
3. Place the Filename in a control named : txtOrginalName, eg, myfile.pdf
4. Form already has PK field "ID_MyPK" (used later when file copy- see below)


Code:
'Open the file
'------------------------------------------------
    
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
                
            Me.txtOriginalPath = strFolder
            Me.txtOriginalName = strFile
                
    Next
    End If
    Set f = Nothing



Example (Copy File to another location):

1. Get the Orginal File's path&name from the controls: txtOriginalPath & txtOriginalName.

2. Define a pre-determined destination folder, eg. C:\Folder_B.
3. Define original file's full path (txtOriginalPath & txtOriginalName).
4. Copy original full path to destination folder and include the PK field in the filename, eg :

Original file > C:\Folder_A\myfile.pdf
Destination file > C:\Folder_B\321_myfile.pdf (where 321 is ID_myPK)


Code:
'Copy the File
'---------------------------------------------------

Dim OriginalFilePath
Dim DestinationFolder

DestinationFolder = "C:\Folder_B\"

Set fso = CreateObject("Scripting.FilesystemObject")

OriginalFilePath = Me.txtOriginalPath & Me.txtOriginalName
 
If fso.FileExists(OriginalFilePath) Then 'overwrite the file

'Copy the original file and include the PK and a underscore character
fso.CopyFile OriginalFilePath , DestinationFolder & Me.ID_myPK & "_" & Me.txtOriginalName, True
End If


Thank you once again to everyone who helped me with this.

Best regards,
Jamie.
 

AlexHedley

Registered User.
Local time
Today, 22:25
Joined
Aug 28, 2012
Messages
171
Thanks @StarGrabber, it was late and I was on my phone so I should have been more careful
 

StarGrabber

Junior App. Developer
Local time
Today, 23:25
Joined
Oct 21, 2012
Messages
165
@ Alex: don't mention it.

@ Jamie: I'm sure a lot of forum visitors will benefit from your code. Well explained!

Only one thing: if you don't allow multiselect in your file dialog you don't need a "For Each...Next" loop. You can retrieve the user's selection by "f.SelectedItems(1)". Try to keep things as simple as possible - always.
 
Local time
Tomorrow, 00:25
Joined
Aug 3, 2005
Messages
66
Thank you for the kind comment StarGrabber,

I was just now busy reading up on everything to do with File Handling in Access because i was wondering about the .SelectedItems(x). Thanks for clearing it up.

Working and Learning as I go along.
Thanks.
 

Users who are viewing this thread

Top Bottom