Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA Run time Error "424
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2005
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Run time Error "424

    I am having a problem with the VBA code below it works fine with

    Fileout.Write "This is a test"

    but files when

    Fileout.Write Cl.Value, Fldr & ""

    Also it seems to be overwriting the same line so I think a new line command somewhere. Also os it posible to get the code to append to the list rather than overwrite.

    Your help will be greatly appreciated

    Code:
    Sub Copy_Move_Files()
       Dim fso As Object
       Dim Cl As Range
       Dim Fldr As String
       
       Set fso = CreateObject("scripting.filesystemobject")
       With Application.FileDialog(4)
          .AllowMultiSelect = False
          If .Show = -1 Then Fldr = .SelectedItems(1)
       End With
       
       For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
          If fso.FileExists(Cl.Value) Then
          fso.CopyFile Cl.Value, Fldr & "\"
          Call Write_file
          End If
       Next Cl
    End Sub
    
    
    Sub Write_file()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    
     Dim Fileout As Object
        Set Fileout = fso.CreateTextFile("C:\Users\desmo\Desktop\Audit Trail.txt", True, True)
        Fileout.Write Cl.Value, Fldr & "\"
        Fileout.Close
    End Sub
    Regards
    Des

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,050
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run time Error "424

    I have not tested but maybe:
    Code:
    Sub Copy_Move_Files()
       Dim fso As Object
       Dim Cl As Range
       Dim Fldr As String
       
       Set fso = CreateObject("scripting.filesystemobject")
       With Application.FileDialog(4)
          .AllowMultiSelect = False
          If .Show = -1 Then Fldr = .SelectedItems(1)
       End With
       
       Set Fileout = fso.CreateTextFile("C:\Users\desmo\Desktop\Audit Trail.txt", True, True)
       For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
          If fso.FileExists(Cl.Value) Then
            fso.CopyFile Cl.Value, Fldr & "\"
            Fileout.Write Cl.Value, Fldr & "\"
          End If
       Next Cl
       Fileout.Close
    End Sub

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run time Error "424

    Hi Kenneth,

    I am getting the following error message

    Capture-2
    Capture-1
    Last edited by purceld2; Sep 23rd, 2019 at 08:55 AM.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Run time Error "424

    You should pass Cl.Value and Fldr as arguments to the called sub:

    Code:
    Sub Copy_Move_Files()
       Dim fso As Object
       Dim Cl As Range
       Dim Fldr As String
       
       Set fso = CreateObject("scripting.filesystemobject")
       With Application.FileDialog(4)
          .AllowMultiSelect = False
          If .Show = -1 Then Fldr = .SelectedItems(1)
       End With
       
       For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
          If fso.FileExists(Cl.Value) Then
          fso.CopyFile Cl.Value, Fldr & "\"
          Call Write_file(Cl.Value, Fldr & "\")
          End If
       Next Cl
    End Sub
    
    
    Sub Write_file(CellValue as string, FolderPath as string)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    
     Dim Fileout As Object
        Set Fileout = fso.CreateTextFile("C:\Users\desmo\Desktop\Audit Trail.txt", True, True)
        Fileout.Write CellValue, FolderPath
        Fileout.Close
    End Sub

  5. #5
    Board Regular
    Join Date
    Aug 2005
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run time Error "424

    Hi Rory,

    Please excuse me if I have missed something obvious but still getting an error message see below

    Capture-2
    Capture-1

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Run time Error "424

    Sorry, didn't look at your original code closely enough. Write only takes one argument, so I assume you intend to write those two values with a comma betweeen them? If so, it should be:

    Code:
    Fileout.Write CellValue & ", " & FolderPath

  7. #7
    Board Regular
    Join Date
    Aug 2005
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run time Error "424

    Hi Rory,

    Thanks for all your time. the code is coping the files fine but the audit seems to be overwriting the first line in the file.

    Is there some way for each file copied it puts it on a new line in the audit file and if it's not too much trouble always appends new data to the file.

    Once again thanks for all your help.

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Run time Error "424

    Your current code overwrites the text file each time. You could do something like this:

    Code:
    Sub Write_file(CellValue as string, FolderPath as string)
    Const ForAppending as long = 8
    Dim LogFileName as string
    LogFileName = "C:\Users\desmo\Desktop\Audit Trail.txt"
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
     Dim Fileout As Object
       Set Fileout = fso.OpenTextFile(logfilename, forappending, True) ' the True allows it to be created if it doesn't already exist
    
        Fileout.WriteLine CellValue & ", " & FolderPath
        Fileout.Close
    End Sub

  9. #9
    Board Regular
    Join Date
    Aug 2005
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Run time Error "424

    Hi Rory

    I have run the amended code and it has moved the expected files successfully to the target folder but when I checked the Audit files it appears to be writing to the file but apparently in a different language (see below)

    Any advice

    㩃䑜獥潭摮䴠獵捩䵜獵捩䰠扩慲祲㈠㄰尸〰䐠捥㈠㄰‸敓敬瑣摥吠湵獥ぜ‴*⁁畃⁰晏吠慥洮㍰‬㩃啜敳獲摜獥潭䑜獥瑫灯乜睥䐠湥楮⁳牂睯屮਍㩃䑜獥潭摮䴠獵捩䵜獵捩䰠扩慲祲㈠㄰尸〰䐠捥㈠㄰‸敓敬瑣摥吠湵獥ぜ‹*汏⁥慍楒敶⹲灭ⰳ䌠尺獕牥屳敤浳屯敄歳潴屰敎 ⁷敄湮獩䈠潲湷൜䌊尺敄浳湯⁤畍楳屣畍楳⁣楌牢牡⁹〲㠱ぜ‰楋杮摳污⁥佈⁔慂正灵䑜湥楮⁳牂睯*桔敲⁥敍污⁳⁁慄⁹㈱湩档洮㍰‬㩃啜敳獲摜獥潭䑜獥瑫灯乜睥䐠湥楮⁳牂睯屮਍㩃䑜獥潭摮䴠獵捩䵜獵捩䰠扩慲祲㈠㄰尸〰匠汥捥整⁤潴倠慬⁹〲㤱ぜ‰潄湷潬摡摥㈠ 〵㈴㄰‹敓敬瑣摥吠汐祡䑜湥楮⁳牂睯†敗汬圠瑩潨瑵圠瑡牥ㅛ⹝灭ⰳ䌠尺獕牥屳敤浳屯敄歳潴屰敎⁷敄湮獩䈠潲湷൜䌊尺敄浳湯⁤畍楳屣畍楳⁣楌牢牡⁹〲㠱ぜ‰敓敬瑣摥琠汐祡㈠㄰尹〰䐠睯汮慯敤⁤㔲㐰〲㤱匠汥捥整⁤潔倠慬屹敄湮獩䈠潲湷ⴠ䔠獡⹹灭ⰳ䌠尺獕 牥屳敤浳屯敄歳潴屰敎⁷敄湮獩䈠潲湷൜䌊尺敄浳湯⁤畍楳屣畍楳⁣楌牢牡⁹〲㠱ぜ‰敓敬瑣摥琠汐祡㈠㄰尹〰䐠睯汮慯敤⁤㔲㐰〲㤱匠汥捥整⁤潔倠慬屹敄湮獩䈠潲湷ⴠ䔠敶祲潢祤⁳敎摥⁳潌敶洮㍰‬㩃啜敳獲摜獥潭䑜獥瑫灯乜睥䐠湥楮⁳牂睯屮਍㩃䑜獥潭摮䴠獵捩䵜 獵捩䰠扩慲祲㈠㄰尸〰匠汥捥整⁤潴倠慬⁹〲㤱ぜ‰潄湷潬摡摥㈠〵㈴㄰‹敓敬瑣摥吠汐祡䑜湥楮⁳牂睯*慒湩䘠潲桔⁥歓敩⹳灭ⰳ䌠尺獕牥屳敤浳屯敄歳潴屰敎⁷敄湮獩䈠潲湷൜䌊尺敄浳湯⁤畍楳屣畍楳⁣楌牢牡⁹〲㠱ぜ‰敓敬瑣摥琠汐祡㈠㄰尹〰䬠湩獧慤敬䈠 捡畫⁰畔敮屳㄰ⴠ䐠湥楮⁳牂睯*潃据湥牴瑡潩䔨瑸湥敤⥤洮㍰‬㩃啜敳獲摜獥潭䑜獥瑫灯乜睥䐠湥楮⁳牂睯屮਍

    Regards

    Desmond

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,804
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Run time Error "424

    I can't replicate that. I've just tested the code (only changing the file path) and it puts the data in in English, not Chinese!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •