• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simple copy file macro

DE_Tx

Member
I need a very simple macro to copy a file from one folder to another. Based on some internet searches I came across Ron DeBruin's webpage for this and attempted to use the one file copy. I also looked at the MS documentation for FileCopy

My one line of code in the macro (wrapped in the Sub and EndSub of course) would be:

Code:
FileCopy ("H:\SourceFolder\SourceFileExample.xlsx", "H:\DestinationFolder\SourceFileExample.xlsx")
When I run this, the error I get is "Compile Error: Syntax Error"

My understanding of the FileCopy is it should be FileCopy (Source as string, Destination as string). I assume "as string" means to wrap it in double quotes like above.

What am i missing here?

Any help would be greatly appreciated. I am sure it is something simple I have overlooked.

T
 

Hui

Excel Ninja
Staff member
Try:
Code:
FileCopy "H:\SourceFolder\SourceFileExample.xlsx", "H:\DestinationFolder\SourceFileExample.xlsx"
without the Brackets
 

DE_Tx

Member
Thanks, Hui.
Removing the parenthesis worked on my sample files. That led me back to the actual files on my network to see what might be the issue. I am copying this one file across directories so I tested keeping it in a different folder in the same directory and that didn't work.
Then I looked at the file name itself and that was the issue. This file comes from a stored procedure (SP) using a SQL query and it writes out the file name with segments of where the data came from like "Segment1.Segment2.Segment3.Filename.xlsx".
I made a copy of the file and renamed it without the segments and the process worked fine.
So, I will add a step in the macro to copy the file, rename it, and then copy the renamed file to where it can be readily accessed by the end user.
Or, I may rename the file before I copy it to a new location. The SP overwrites what is there from previous day so the file not being there shouldn't be a problem.
Or, I may ask our IT guys to save the file without the segments. (No telling how long that will take).

Thanks for the help in getting me over the hurdle. I should have tested that simple change before I sent out a call for help.
But, now everyone knows about the segment issue.

T
 
Top