Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 10-28-2014, 08:58 AM   #1
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Help with copying/renaming file

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!

businesshippieRH is offline   Reply With Quote
Old 10-28-2014, 09:42 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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'
?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-28-2014, 10:18 AM   #3
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with copying/renaming file

I get:
C:\Users\Rharrell\Documents\POSolutionsIES_0914.xl sx
Which is the path of the original file.
FullPath is the String called out as:
Dim FullPath As String
FullPath = FilePath & FileName

businesshippieRH is offline   Reply With Quote
Old 10-28-2014, 10:19 AM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-28-2014, 10:25 AM   #5
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with copying/renaming file

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
businesshippieRH is offline   Reply With Quote
Old 10-28-2014, 10:47 AM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
businesshippieRH (10-28-2014)
Old 10-28-2014, 10:56 AM   #7
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with copying/renaming file

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!

businesshippieRH is offline   Reply With Quote
Old 10-28-2014, 11:21 AM   #8
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Help with copying/renaming file

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 & strFileExtension
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
The Following User Says Thank You to BigHappyDaddy For This Useful Post:
businesshippieRH (10-28-2014)
Old 10-28-2014, 11:34 AM   #9
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with copying/renaming file

Awesome. I think I'd lose my mind if not for this forum sometimes. 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
businesshippieRH is offline   Reply With Quote
Old 10-28-2014, 11:39 AM   #10
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 205
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: Help with copying/renaming file

Quote:
Originally Posted by businesshippieRH View Post
I think I'd lose my mind if not for this forum sometimes.
I know what you mean. I'm just glad I could give just a little back!

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Reply

Tags
copy , file , fso , syntax help , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Renaming an external file WLC Macros 0 02-27-2014 05:03 PM
Renaming a file (Name function) rebel76 Modules & VBA 2 08-31-2009 11:18 AM
renaming files in a DOS batch file sparx General 9 10-27-2003 01:26 PM
Renaming Copying Existing Files The Real Yoda Forms 1 07-05-2001 03:12 AM
Renaming file names sdefl Modules & VBA 1 11-02-2000 04:26 PM




All times are GMT -8. The time now is 05:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World