Help with copying/renaming file (1 Viewer)

businesshippieRH

Registered User.
Local time
Today, 10:51
Joined
Aug 8, 2014
Messages
60
I haven't used FSO before, and it seems that the syntax is a little different than typical VBA for strings, but I can't quite figure out what I'm doing wrong here... I keep getting a "file not found" error on the "FSO.CopyFile..." line. I have printed all of my strings to the immediate window to check that they're printing correctly. It all looks good. My code is as follows:
Code:
'Copy file
    'Set up strings for coding file names
    Dim FilePath As String
        FilePath = Me.txt_FilePath
    Dim FileName As String
        FileName = Me.txt_FileName
    Dim FullPath As String
        FullPath = FilePath & FileName
    Dim NewTitle As String
        NewTitle = Me.txt_Title
    Dim RecFile As String
        RecFile = "T:\DATABASE\RecordsDB\Records\"
            'Set up FSO declarations
            Dim FSO As Scripting.FileSystemObject
            Set GetFSO = CreateObject("Scripting.FileSystemObject")
            Set FSO = GetFSO
                If FSO Is Nothing Then 'Error handling procedure
                Exit Sub
                End If
                    'Copy File
                    FSO.CopyFile "'FullPath'", RecFile & "'FileName'", False
                    'Rename File to Record Name
                    Name "RecFile & 'FileName'" As "RecFile & 'NewTitle'"

Thanks in advance!
 

MarkK

bit cruncher
Local time
Today, 08:51
Joined
Mar 17, 2004
Messages
8,178
What do you get when you print this "'FullPath'" to the immediate window? Is there a disk and a filepath and file on your system called . . .
Code:
'FullPath'
?
 

businesshippieRH

Registered User.
Local time
Today, 10:51
Joined
Aug 8, 2014
Messages
60
I get:
C:\Users\Rharrell\Documents\POSolutionsIES_0914.xlsx
Which is the path of the original file.
FullPath is the String called out as:
Dim FullPath As String
FullPath = FilePath & FileName
 

MarkK

bit cruncher
Local time
Today, 08:51
Joined
Mar 17, 2004
Messages
8,178
Do this in the immediate pane . . .
Code:
? "'Fullpath'"
What do you get? That is what is in your code. That can't be the name of a file.
 

businesshippieRH

Registered User.
Local time
Today, 10:51
Joined
Aug 8, 2014
Messages
60
Correct. It's not the name of a file. It's a combination of two strings:
Code:
    Dim FilePath As String
        FilePath = Me.txt_FilePath
    Dim FileName As String
        FileName = Me.txt_FileName
    Dim FullPath As String
        FullPath = FilePath & FileName

FilePath and FileName are pulled from unbound text boxes on my form.

I removed the quotes from my code (I'm guessing this is what you were getting at). Still "file not found". Print out from immediate window looks as follows:

Code:
? Filepath
C:\Users\Rharrell\Documents\
?Filename
POSolutionsIES_0914.xlsx
? Filepath & Filename
C:\Users\Rharrell\Documents\POSolutionsIES_0914.xlsx
? Fullpath
C:\Users\Rharrell\Documents\POSolutionsIES_0914.xlsx
 

MarkK

bit cruncher
Local time
Today, 08:51
Joined
Mar 17, 2004
Messages
8,178
You should be able to get away with code something like this . . .
Code:
Sub test1092348012947()
    Const PATH As String = "T:\DATABASE\RecordsDB\Records\"
    Dim fso As New Scripting.FileSystemObject
    Dim src As String
    Dim dst As String
        
    src = Me.txt_FilePath & Me.txt_FileName
    dst = PATH & Me.txt_Title
            
    If Not fso Is Nothing Then fso.CopyFile src, dst, False
End Sub
I think CopyFile will put it in the new destination with a new name, if you so specify.
 

businesshippieRH

Registered User.
Local time
Today, 10:51
Joined
Aug 8, 2014
Messages
60
That works perfectly... the only problem is that my new file does not have an extension.

They will not be consistent, so I'll have to find a way to add that into my code.

However, unless you have a suggestion, I'll likely start a new thread (after a little searching).

Thanks!
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 08:51
Joined
Aug 22, 2012
Messages
205
Question: What exactly is in ME.txt_Title? Does it have the extension in it, or is it just the base file name?

If it is just the base file name, then you need to copy the file extension from the original filename, then append it to the contents in Me.txt_Title.

You could use something like this to find the file extension:
Code:
Public Function GetFileExt(ByVal InString As String) As String
    GetFileExt = Right(InString, Len(InString) - InStrRev(InString, "."))
End Function

Then call it like this:
Code:
strFileExtension = GetFileExt(Me.txt_FileName)
Finally, using MarkK's code, change it to:
Code:
dst = PATH & Me.txt_Title [COLOR=red]& strFileExtension[/COLOR]
 

businesshippieRH

Registered User.
Local time
Today, 10:51
Joined
Aug 8, 2014
Messages
60
Awesome. I think I'd lose my mind if not for this forum sometimes.:banghead: Thanks!
For others that may find themselves in the same boat, this was my final code:
Code:
'Set up strings for coding file names
    'Old Path
    Dim FilePath As String
        FilePath = Me.txt_FilePath
    Dim FileName As String
        FileName = Me.txt_FileName
        'Combined
        Dim FullPath As String
            FullPath = FilePath & FileName
    'New Path
    'Set up string for adding file extension
        strFileExtension = GetFileExt(Me.txt_FileName)
    'New Path Strings
    Dim NewTitle As String
        NewTitle = Me.txt_RecordName & "." & strFileExtension
    Dim RecFile As String
        RecFile = "T:\DATABASE\RecordsDB\Records\"
        'Combined
        Dim NewFullPath As String
            NewFullPath = RecFile & NewTitle
'Set up FSO declarations
Dim FSO As New Scripting.FileSystemObject
If Not FSO Is Nothing Then 'Error handling procedure
    'Copy File
    FSO.CopyFile FullPath, NewFullPath, False
    End If
 

Users who are viewing this thread

Top Bottom