1

I am currently having an error with a vba script, tried to fix it but still gives an error as listed in the title.

The aim of the script is to copy file names based on an input form a worksheet and then copy them to a destination saving them with the current date in the name.

    Set FSO = CreateObject("scripting.filesystemobject")
    FILE = Sheet1.Range("G3").Value
    FILE2 = Sheet1.Range("G4").Value
    SourceFile = Source & "\" & FILE & ".xls"
    DestFile = DestPath & "\" & FILE & " " & ShortDate & ".csv"
    SourceFile2 = Source & "\" & FILE2 & ".xls"
    DestFile2 = DestPath & "\" & FILE2 & " " & ShortDate & ".csv"

    'Setsup Flag File
    Dim oFile As Object
    Set oFile = FSO.CreateTextFile(DestPath & "\OIS.FLAG")
    oFile.WriteLine Format(Sheets("Sheet1").Range("C7").Value, "yyyy/mm/dd")
    oFile.Close
    FSO.CopyFile SourceFile, DestFile
    FSO.CopyFile SourceFile2, DestFile2

Source is just set to "C:\Users\Data" DestPath is just "C:\Users\updates"

When I run the script the first copy works, so SourceFile is copied, but then the runtime error occurs for the second one SourceFile2, but I've checked multiple times and the SourceFile2 Exists...

Any Tips, or something I'm missing? Also Checked other similar threads, and it's not because the string is too long?

If I input the whole name for SourceFile2 i.e "C:\Users\Data\file2.xls" then it works but I've checked the syntax a million times and seems to be fine, maybe a fresh pair of eyes will help, any suggestions would be massively appreciated :)

RK1
  • 2,384
  • 1
  • 19
  • 36
  • Have you done `debug.print source` and `file2`? – findwindow Apr 21 '16 at 15:33
  • 1
    `C:\Users\\Data` must be `C:\Users\Data` (remove a \ \) – Paul Ogilvie Apr 21 '16 at 15:36
  • @PaulOgilvie that would mean `sourcefile` would also break which it did not. – findwindow Apr 21 '16 at 15:39
  • I think Paul is correct, you may need to remove a backslash from after Users – IIJHFII Apr 21 '16 at 15:48
  • 1
    My thoughts. Since copyfile SourceFile works but copyfile SourceFile2 doesn't it is an issue introduced at FILE2. I would check if the file exists (question [here](http://stackoverflow.com/questions/16351249/vba-check-if-file-exists) with some checks). I would also check to see if any of the files are open (source or destination) and if the destination has any sort of write protection on it. – gtwebb Apr 21 '16 at 15:56
  • Check that files in your worksheets don't start with '\'. Check that Source does not end with a '\'. – Paul Ogilvie Apr 21 '16 at 16:12
  • Hi, yeah sorry the path Paul was just a typo, but It's weird as I changed FILE2, and it still doesn't work, but yeah gtwebb I think you might be right, I just need to investigate why FILE2 is giving problems as it just reads it from the excel sheet the same as FILE... – RK1 Apr 22 '16 at 05:16
  • The code is `FILE2 = Sheet1.Range("G4").Value ` but when I insert the path Manually for SourceFile2 it works say `SourceFile2 = "C:\Users\Data\file2.xls"` but I checked FILE2 = file2.xls when I debug and the path is the same, so a bit confusing... – RK1 Apr 22 '16 at 05:26
  • Thanks for the help Everyone, the `Source` was the issue, thanks @PaulOgilvie and @gtwebb for the fresh eyes, it just happened to be that `FILE` existed in the wrong `Source` declaration , which confused me, but Thanks :), working great now – RK1 Apr 22 '16 at 05:57

0 Answers0