Opening .csv files sequentially using VBA

S(0RP!0N

New Member
Joined
Nov 1, 2011
Messages
2
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try something like this...

Code:
[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...

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

with

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

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top