Macro to copy data from File

[howard]

I have the source CSV File in C:\Downloads

The source file starts with 9101810 followed by _ for e.g. 911810_202219174.csv


When runing the macro "Copy_SourceDat ()", I get a run time error "file not found and the code below is highlighted

 x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbNewLine)

It would be appreciated if somene could kindly amend my code

 Sub Open_911810_CSV()
  Dim ws As Workbook, File As String
    File = Dir("C:\Extract" & "\911810*.csv")
    Set ws = Workbooks.Open("C:\downloads\" & File)
    ActiveSheet.UsedRange.Columns("A:D").Offset(1).Clear
Close_111900
End Sub
Sub Close_911810()
 
    Dim wbk As Workbook
    For Each wbk In Application.Workbooks
        If wbk.Name Like "*911810*.csv" Then
            wbk.Close SaveChanges:=True
        End If
    Next wbk
    '
End Sub

 Sub Copy_SourceData()
 Clear_DaTA
Open_911810_CSV
   Dim fn As String, x, y, z, a(2), e, w, cols
    Dim i As Long, ii As Long, n As Long
    fn = Dir("C:\Extract" & "\*911810*.csv")
If fn = "" Then MsgBox "No csv files": Exit Sub
 
    x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbNewLine)
    Sheets("Fassets").Select
    
    ReDim w(1 To UBound(x), 1 To 1)
    For i = 0 To 2: a(i) = w: Next
    cols = Array(Array(5, 9), Array(9, 5), Array(6, 12))
    For i = 1 To UBound(x)
        If x(i) <> "" Then
            n = n + 1: y = Split(x(i), ",")
            For ii = 0 To UBound(cols)
                If y(cols(ii)(0) - 1) Like "*/*/*" Then
                    z = Split(y(cols(ii)(0) - 1), "/")
                    a(ii)(n, 1) = DateSerial(z(2), z(1), z(0))
                Else
                    a(ii)(n, 1) = y(cols(ii)(0) - 1)
                End If
            Next
        End If
    Next
    For i = 0 To UBound(cols)
        Cells(2, cols(i)(1)).Resize(n) = a(i)
    Next
    Sheets("Macro").Select
End Sub

 

[HongRu]

Maybe you should replace the code line

x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbNewLine)

by

x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\Extract\" &fn).ReadAll, vbNewLine)

another issue.
Why do you open the "*911810*.csv" twice ?

Set ws = Workbooks.Open("C:\downloads\" & File)

Above also open and the "C:\downloads\" should be "C:\Extract\"

anyway, have a good time.

 

[howard]

Thanks for the help HongRu

The code below was not necessary but did not realise it until you brought it up. Thanks for pointing it out

Hope you have a good day as well

 Set ws = Workbooks.Open("C:\downloads\" & File)