Opening .csv files sequentially using VBA

[S(0RP!0N]

Hi Guys,

I have been modifying a program but somehow I'm stuck.

Here's the program:

Dim g As Integer
g = 0
Do g = g + 1
Select Case g
Case 1 Workbooks.Open Filename:="C:\File directory\Log\P" & i & ".csv"
Case 2 Workbooks.Open Filename:="C:\File directory\Log\N" & i & ".csv"
Case 3 Workbooks.Open Filename:="C:\File directory\Log\L" & i & ".csv"
End Select
Loop Until g = 3

There will be many csv files in this directory "C:\File directory\Log\".
for example, P1040.csv, P1041.csv, P1042.csv, N1043.csv, P1044.csv, L1045.csv, etc.

The initial letters of the data files vary from P, N and L and these letters refer to the type of data.
i means the serial number of the file.

Among the various types and serial number of data files, there will be only one file type(P,L,N) with the serial number i inside the directory,

for example, if there is one file named P1042.csv (where P is the type and 1042 is the serial number i), then there will be no more N1042.csv or L1042.csv. but of course the next file type will be either P, N or L followed by 1043.csv.

The purpose of this program is to open all the files of the serial number i regardless of the file type.
However, when I tested the program, the program look for all "P", "N" and "L" file type of the same serial number (which does not exist) so for example, if N1043.csv was found, it was opened and returned as Error for P type and L type.

What I want is to open all type of files according to the serial number regardless of the file type.
I hope you all can help me with this.

 

[Domenic]

Try something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] FileNum [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPath = "C:\File directory\Log\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    [color=darkblue]For[/color] i = 1040 [color=darkblue]To[/color] 1045 [color=green]'change accordingly[/color]
        FileNum = i
        strFile = Dir(strPath & "?" & FileNum & ".csv", vbNormal)
        [color=darkblue]If[/color] Len(strFile) > 0 [color=darkblue]Then[/color]
            Workbooks.Open Filename:=strPath & strFile
        [color=darkblue]Else[/color]
            Cnt = Cnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
            MyArray(Cnt) = FileNum
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] wksDest = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
        [color=darkblue]With[/color] wksDest
            .Range("a1").Value = "The following files were not found:"
            .Range("a2").Resize(UBound(MyArray)).Value = WorksheetFunction.Transpose(MyArray)
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

If the file numbers start from 1, but are in the format "P0001.csv", replace...

[font=Verdana]        FileNum = i[/font]

with

[font=Verdana]        FileNum = Format(i, "0000")[/font]

 

[S(0RP!0N]

thanks a lot, bro.
I'll try it and let you know again.