Variable file operations (move and rename files).

[NessPJ]

Hi all,

I am actually looking for a way to invoke a few file operations when working in Excel (Using VBA or so). Yet, i want the criteria for the files to be user customisable (the filename differs).

(1) In detail, i have a folder with report files from a WMS/PWS server.
This folder contains a file for each day of the month (where the file number equals the day number).
For instance... pws.stats.1.csv, pws.stats.2.csv, pws.stats.3.csv etc.

(2) Now i already have a little VBA code that opens up 2 explorer windows, 1 for the folder with the reports and one window that contains 7 folders for the user to drag the right reports in (Day 1 through Day 7).

(3) As a next step i have a shell commandline which renames all the files in the folder (it removes the .csv extension because if not, the files will not be imported correctly.
And then the final step is a Macro that imports the files into Excel (right format and right sheet of the workbook).

Step 2 is the one i actually want to automate, by giving the user the ability to input the number of the filename (which equal a day number) into 7 cells. When these are inserted i want a piece of VBA code to use those numbers as a variable to copy the right files into the according 7 folders (pws.stat.&number1&.csv, pws.stat.&number2&.csv).

When searching online i found this piece of code:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=827
Yet this was a little too much for me to comprehend (i'm no expert at VBA).

If anyone here could lent me a hand that would be awesome.
I'm guessing i need a piece of code that allows me to define 7 variables which then can be used to copy (and hopefully rename) these files.

Thanks in advance for your time.

 

[NessPJ]

I wrote the following VBA code to try and fix the problem myself, yet the Debugger keeps telling me Bold line below contains some error.

Sub Copytest2()
 
    Sheets("Sheet1").Select
 
    Dim Dag1 As String
 
    Dag1 = Range("O9").Value
 
[B]    FileCopy "U:\Fileserver\Data\journee.pws.stat." & Dag1 & ".csv", "U:\Fileserver\Myfolder\Dag1\"[/B]
 
End Sub

Any ideas?

 

[NessPJ]

Is there anyone that can tell me if i'm using the strings or Filecopy command the right way or?

TIA.

 

[Ruddles]

I'm fairly sure that FileCopy can only copy a file to a file, not a file to a folder.

Try:-

[B] FileCopy "U:\Fileserver\Data\journee.pws.stat." & Dag1 & ".csv", "U:\Fileserver\Myfolder\Dag1\[COLOR=red]journee.pws.stat." & Dag1 & ".csv"[/COLOR][/B]

Just be careful of what's in Dag1. If O9 contains a date, you'll end up trying to copy a file called something like journee.pws.stat.40670.csv. If so, you should format the date before you use it:-

[B] FileCopy "U:\Fileserver\Data\journee.pws.stat." & [COLOR=#ff0000]Format([/COLOR]Dag1[COLOR=#ff0000], "dd-mm-yy")[/COLOR]  & ".csv", "U:\Fileserver\Myfolder\Dag1\journee.pws.stat." & [COLOR=red]Format([/COLOR]Dag1[COLOR=red], "dd-mm-yy")[/COLOR] & ".csv"[/B]

That's an example: you need to use whatever format matches the format of the date in the file name.

Hope this helps.

 

[NessPJ]

Hey,

Thanks for the tip, its working like a charm now!

Now all i would like to add is an option, that when Cell O9 is left empty it will skip the line of code and go to the next piece of code (Until all 7 days are done).

I believe this is something with the OnError function?

 

[Ruddles]

No, you only use the On Error directive when you can't (easily) test for an error.

I can't see the piece of code which does the actual looping but perhaps something along the following lines:-

[FONT=Fixedsys]  If Not IsEmpty(Range("O9")) Then[/FONT]
[FONT=Fixedsys]    Dag1 = Range("O9").Value
    FileCopy... etc[/FONT]
[FONT=Fixedsys]  End If
[/FONT]

 

[NessPJ]

Again thanks a lot for the help!