Hi All,
I am having an issue implementing the FileSystemObject in a piece of code and wondered if anyone could help....!
The code I am successful with is:
However when I try and use this code as an alternative it falls over with a 'Run-time Error 5
Please Login or Register to view this content.
Please Login or Register to view this content.
You're not getting objects off the sheet, you're getting text, which is what copyfile is expecting.
Remove the 2 x Set for the file names.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMShucks,
Thanks for replying. Do mean as follows:
I tried that and I'm getting the same error but at the 'filename' linePlease Login or Register to view this content.
Regards, witham
Lose the "Me"
Regards, TMS
I think my limited knowledge is hindering me here...!
I've tried with the set removed and the me removed and with the set in and the me removed and I'm getting the same error.
Maybe the copyfile doesn't like the variant variables. Try Dimming them as String.
You use Set when you are dealing with objects. So, when you say:
Please Login or Register to view this content.
You are setting the variables to be the range objects.
"Me" in relation to a worksheet would be the workbook.
If you look back at the original code, you were passing copyfile two strings which were fully qualified source and destination locations/filenames. So, you need to pass it two string variables each containing a fully qualified location/filename.
I hope that clarifies and solves the problem.
If not, please post a sample workbook with your code.
Regards, TMS
I kind of understand what you are saying TMSchucks (and really appreciate your help),
but er..... I've attached the file!
TestArchivingBook.xlsm
You know, sometimes you can't see the wood for the trees:
Please Login or Register to view this content.
Regards, TMS
Oh, and be careful about using what might be Excel/VBA reserved words. Hence, sFileName instead of just FileName, etc
TMS,
Thank you. It works perfectly.
Notes on naming conventions received and understood!
Have a great weekend. Witham.
You're welcome. Thanks for the rep.
You have a good one too. We're going to Fountain's Abbey tomorrow so I hope the weather improves!
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks