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

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 04-11-2005, 06:19 AM   #1
ipr1
Registered User
 
Join Date: Jun 2003
Location: Boston area
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
ipr1
FileCopy Function

Hi - I am trying to copy one file (the source) to another location (the destination) and. ideally, I would like to add a date to the file as it is copied.

This is the code I am using:

Function Nightly()

Dim CurrentDate As String
CurrentDate = Date
CurrentDate = Format(CurrentDate, "MMDDYY")

FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\Backup Secure\Nightly_CurrentDate.rpt"

End Function

The error message I am receiving is "RunTime Error 76", "Path not found". I assume that my problem lies with the fact that both the source and destinations need to be string functions and in that regard I have tried concatenations etc. (e.g. "\\nel\mfsc-dsndm-d\" & "Nightly" & ".rpt" and "netapp3\ds-mfsas-d\BackupSecure\" & "Nightly" & "__CurrentDate"& ".rpt" but NOTHING seems to work.

Any suggestions? Thanks in advance for your help.

__________________
Bob
ipr1 is offline  
Old 04-11-2005, 06:27 AM   #2
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Code:
Dim fso As New FileSystemObject
fso.CopyFile "file name", "to", Overwrite (true/false)
Note you need to include a reference to the Windows Script Host
modest is offline  
Old 04-11-2005, 06:39 AM   #3
Shadez
Registered User
 
Join Date: Jan 2003
Posts: 192
Thanks: 0
Thanked 0 Times in 0 Posts
Shadez
Quote:
Originally Posted by modest
Code:
Dim fso As New FileSystemObject
fso.CopyFile "file name", "to", Overwrite (true/false)
Note you need to include a reference to the Windows Script Host
No you dont FileCopy is a VBA function.


Bob,

Ive just done a couple of tests with the function and can find no critical problems with what you are trying to do.

I suggest you do as the error message suggests and check both the paths and source file exist.


Code:
Function Nightly()

     Dim CurrentDate As String
     CurrentDate = Format(now,"MMDDYY")  'should be DDMMYY, unless you are american

     FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\Backup Secure\Nightly" & CurrentDate & ".rpt"

End Function



Last edited by Shadez; 04-11-2005 at 06:41 AM.
Shadez is offline  
Old 04-11-2005, 06:59 AM   #4
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Quote:
Originally Posted by Shadez
No you dont FileCopy is a VBA function
For my code you do, as i suggested using Windows's File System Object. There are ways to access the filesystemobject without actually using the reference file, by using the CreateObject() method.

Shadez, please read my post before you comment on it.
modest is offline  
Old 04-11-2005, 07:17 AM   #5
Shadez
Registered User
 
Join Date: Jan 2003
Posts: 192
Thanks: 0
Thanked 0 Times in 0 Posts
Shadez
Quote:
Originally Posted by modest
Shadez, please read my post before you comment on it.
I did and was forced to respond the way i did, your post offered no help, if you are suggesting he use the FSO instead of the VBA functions then you should say so and not just post a couple of lines that probably mean nothing to bob and will just confuse him.

besides why get him to rewrite his code when the problem is clearly one to do with the paths used and not the code.
Shadez is offline  
Old 04-11-2005, 09:43 AM   #6
ipr1
Registered User
 
Join Date: Jun 2003
Location: Boston area
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
ipr1
THANKS everybody - I finally got it to work. The problem was in the destination file name (I concatenated the name using a SPACE where, in fact, I should have used an underline.

The correct function is as follows:


Function Nightly()


Dim CurrentDate As String
CurrentDate = Date
CurrentDate = Format(CurrentDate, "MMDDYY")

'FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\Backup Secure\Nightly.rpt"
FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\BackupSecure\Nightly" & "_" & CurrentDate & ".rpt"
End Function
__________________
Bob
ipr1 is offline  
Old 04-11-2005, 09:43 AM   #7
ipr1
Registered User
 
Join Date: Jun 2003
Location: Boston area
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
ipr1
THANKS everybody - I finally got it to work. The problem was in the destination file name (I concatenated the name using a SPACE where, in fact, I should have used an underline.

The correct function is as follows:


Function Nightly()


Dim CurrentDate As String
CurrentDate = Date
CurrentDate = Format(CurrentDate, "MMDDYY")

'FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\Backup Secure\Nightly.rpt"
FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\BackupSecure\Nightly" & "_" & CurrentDate & ".rpt"
End Function

__________________
Bob
ipr1 is offline  
Old 04-11-2005, 09:49 AM   #8
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 86 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Try this [see blue code] ...

Code:
Function Nightly()

FileCopy "\\nel\mfsc-dsndm-d\Nightly.rpt", "\\netapp3\ds-mfsas-d\Backup Secure\Nightly_" & Format(Date, "MMDDYY") & ".rpt"

End Function
You can use the Dir() function to verify if the source file is valid.
Code:
If Dir("\\nel\mfsc-dsndm-d\Nightly.rpt") <> "" Then
     msgbox "file exists"
Else
     msgbox "file does not exist"
     Exit Sub 'quit the sub if the file does not exist
End If
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline  
Old 04-11-2005, 01:43 PM   #9
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Quote:
Originally Posted by Shadez
if you are suggesting he use the FSO instead of the VBA functions then you should say so
It seems as though I need to baby you and Shadez. I'll note the next time I post a possible solution, I will write an essay for you to read. If the problem is with the file name, there is nothing we can do. He should know what the file name is and should have looked to see if he inputted the correct name before posting(since that is what the error message said was the problem)... psychics are somewhat of a rarity amongst programmers.


Quote:
besides why get him to rewrite his code when the problem is clearly one to do with the paths used and not the code.
Many reasons. But the first is that by rewriting, you'll discover something that you did not notice before (such as misspellings). Second, it gets you to rethink your whole approach. Third, unless specified is is often helpful to see other ways of doing things, if not just for learning.

He might come across the filesystemobject in the future and not recognize what it is... in this case he'll remember this post and see a way to call it and something you can do with it.

Sorry shadez, everyone isn't a preschool genius like you.
modest is offline  
Old 04-11-2005, 02:57 PM   #10
meboz
Registered User
 
Join Date: Aug 2004
Posts: 71
Thanks: 0
Thanked 0 Times in 0 Posts
meboz is on a distinguished road
Well look at this, Modest is causing more trouble.

Is there possibly a way that the Moderator can have you barred from making comment in these forums?
meboz is offline  
Old 04-12-2005, 12:41 AM   #11
Shadez
Registered User
 
Join Date: Jan 2003
Posts: 192
Thanks: 0
Thanked 0 Times in 0 Posts
Shadez
Quote:
Originally Posted by modest
It seems as though I need to baby you and Shadez. I'll note the next time I post a possible solution, I will write an essay for you to read. If the problem is with the file name, there is nothing we can do. He should know what the file name is and should have looked to see if he inputted the correct name before posting(since that is what the error message said was the problem)... psychics are somewhat of a rarity amongst programmers.
no need for an essay just a line of text saying something like, "personally I would use the fso, this is how you do it". If he has not used the fso before he would have to be psychic to tell what you were trying to say.

Quote:
Originally Posted by modest
Many reasons. But the first is that by rewriting, you'll discover something that you did not notice before (such as misspellings). Second, it gets you to rethink your whole approach. Third, unless specified is is often helpful to see other ways of doing things, if not just for learning.
i cant deny that there may be benefits to rewriting code. But you dont rewite code unless there is a good reason.

Quote:
Originally Posted by modest
He might come across the filesystemobject in the future and not recognize what it is... in this case he'll remember this post and see a way to call it and something you can do with it.
only because i told you what a load of **** your post was and have explained for you what you were saying. The post by its self would not be remembered because it doesnt solve his problem, just confuse him.

Quote:
Originally Posted by modest
Sorry shadez, everyone isn't a preschool genius like you.
Modest grow up and stop being so childish, if you cant take constructive criticism then i suggest you post else ware.
Shadez is offline  
Old 04-12-2005, 06:58 AM   #12
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
I think I must leave, you all obviously have nothing better to do with your time and I can't be arguing with idiots all day. If the moderator could, I'd have them block/filter out morons from the forum. But since they can't, I would rather them just delete all these off-topic posts. Including my own.
modest is offline  
Old 04-12-2005, 07:23 AM   #13
Shadez
Registered User
 
Join Date: Jan 2003
Posts: 192
Thanks: 0
Thanked 0 Times in 0 Posts
Shadez
Quote:
Originally Posted by modest
I think I must leave, you all obviously have nothing better to do with your time and I can't be arguing with idiots all day. If the moderator could, I'd have them block/filter out morons from the forum. But since they can't, I would rather them just delete all these off-topic posts. Including my own.




LOL do you know anything......


you dont need a moderator to block/filter ppl from the forum, there is a simple ignore list that you can edit your self

as for the idot comment, i didnt see you spot the error in bobs code or even know how to ignore ppl in a forum.....




Shadez is offline  
Old 04-12-2005, 09:38 AM   #14
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Quote:
Originally Posted by Shadez
as for the idot comment, i didnt see you spot the error in bobs code or even know how to ignore ppl in a forum.....
This is hopefully my last post. I'm sure you're going to try and get the last word, so you can have it if you want.

First: It's idiot, not "idot"

Second: You can't spot a spelling error.. if the error says "no reference included" my first thought would be... there is not a reference. The same thing went for his error, it said path not found, which means there is something messed up with string. None of us can be the one to say how a non-system folder is spelled.

Third: You're a moron for doing the tests in the first place. You shouldn't have had to do the tests. You should have just been able to look at the code and see if the programming syntax was correct (and it was). Therefore my suggestion should have got him to retype his code and hopefully got him to find his error.

Finally: My suggestion was that that the moderators block you from the forum all together if it was possible. This goes beyond ignore. This prevents you from posting an unintelligent comment ever again, additionally it will prevent you wasting server space/bandwidth. I stick to what I said.

I'll end on the quote, "You are but a fool to reason with a fool." -- I guess I'm a fool for trying.
modest is offline  
Old 04-13-2005, 01:14 AM   #15
Shadez
Registered User
 
Join Date: Jan 2003
Posts: 192
Thanks: 0
Thanked 0 Times in 0 Posts
Shadez
Quote:
Originally Posted by modest
This is hopefully my last post. I'm sure you're going to try and get the last word, so you can have it if you want.

First: It's idiot, not "idot"
You really hit the bottom if you have to point out simple typing errors and use them to boost your already failing argument.

Quote:
Second: You can't spot a spelling error.. if the error says "no reference included" my first thought would be... there is not a reference. The same thing went for his error, it said path not found, which means there is something messed up with string. None of us can be the one to say how a non-system folder is spelled.
lol did i not say that in my first post , again i dont see in your post any mention of a path problem.

Quote:
Third: You're a moron for doing the tests in the first place. You shouldn't have had to do the tests. You should have just been able to look at the code and see if the programming syntax was correct (and it was). Therefore my suggestion should have got him to retype his code and hopefully got him to find his error.
Could you say with 100% certainty say the problem was not to do with the changing of the filename, the use of network paths or possibly invalid characters. This is what i checked to confirm the limitations of the function to see if a rewrite was needed in this situation, which it wasnt.(tbh its not a function ive used before, and i prefer to be safe and not look like a idiot who doesn't solve the problem). Your suggestion, if you can call a badly written function reference a suggestion? would of had him rewriting his code unnecessary. He may of spotted the problem in the process but more likely would copy and paste the paths and end up reposting with a similar problem.

Quote:
Finally: My suggestion was that that the moderators block you from the forum all together if it was possible. This goes beyond ignore. This prevents you from posting an unintelligent comment ever again, additionally it will prevent you wasting server space/bandwidth. I stick to what I said.
As you please, i have reported this thread to the mods we will see what they have to say.

Quote:
I'll end on the quote, "You are but a fool to reason with a fool." -- I guess I'm a fool for trying.
I think the quote you were looking for is;

Dont Argue with an idiot they will drag you down to their level and beat you with experience..............


Last edited by Shadez; 04-13-2005 at 01:22 AM.
Shadez is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:32 PM.


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