Importing Multiple Files Into Excel Using VB

bsac · March 18th, 2008, 05:33 PM

Hello,

I am fairly new to VB. I am trying to open 183 files named SF001 thru SF183 into excel. I am able to open a file at a time and have it dump into a worksheet, however I would like to create a loop that opens all 183 and imports into a worksheet. Below Is what I have tried but I get a compile error. Does anyone know how I can fix this?

Thanks

Sub SFDATACOMPARISON()

Dim i As Integer
Dim Rowi As Integer

i = 1
Rowi = 2

While i <= 183

ImportTextFileC2 FNameC2:="C:\MIKE\Stress\SF_filesC2\SF" & i&, CC2:="1", RC2:="Rowi", FileNumberC2:=i

ImportTextFileC1 FNameC1:="C:\MIKE\Stress\SF_filesC2\SF" & i&, CC1:="10", RC1:="Rowi", FileNumberC1:=i

i = i + 1
Rowi = Rowi + 600

Wend

End Sub

stanleydgromjr · March 18th, 2008, 05:48 PM

bsac,

The full path to your text files is:
"C:\MIKE\Stress\SF_filesC2\"

Your files are named "SF001" thru "SF183".

What is the file extension for your text files?

Are you looking to put all the 183 text files into one worksheet in your workbook?

What is the worksheet name?

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

bsac · March 19th, 2008, 07:14 AM

Hi Stan,

Yes the path is as you have written.

The files are named SF001 Thru SF183.

There is no extension on the files. However, the files contain only text.

I am hoping to put all of the data into one worksheet, which right now is just called Sheet1.

I have a function " ImportTextFileC2 " which opens a file and puts the data in excel, but I can't seem to pass multiple paths into the function. That is why I am trying to use a loop.

Thanks for your help,
bsac

stanleydgromjr · March 19th, 2008, 11:54 AM

bsac,

Here you go.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:

Option Explicit
Option Base 1
Sub SFDATACOMPARISON()
    Dim lngLastRow As Long
    Dim lngITLastRow As Long
    Dim lngLoopCtr As Long
    Dim myArray(183) As String
    Dim myCount As Integer
    Dim Data
    Dim FName As String
    Dim ThisFile
    Dim Ctr As Integer
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    On Error Resume Next
    With Sheets("ImportText")
        If Err Then Worksheets.Add().Name = "ImportText"
        On Error GoTo 0
    End With
    With Sheets("ImportText")
        With Range(Cells(1, 1), Cells(1, 9))
            .FormulaR1C1 = "=""SF00"" & COLUMN() & "".txt"""
        End With
        With Range(Cells(1, 10), Cells(1, 99))
            .FormulaR1C1 = "=""SF0"" & COLUMN() & "".txt"""
        End With
        With Range(Cells(1, 100), Cells(1, 183))
            .FormulaR1C1 = "=""SF"" & COLUMN() & "".txt"""
        End With
        With Range(Cells(1, 1), Cells(1, 183))
            .Copy
            .Value = .Value
            Application.CutCopyMode = False
        End With
        myCount = 0
        For lngLoopCtr = 1 To 183
            myCount = myCount + 1
            myArray(myCount) = Cells(1, lngLoopCtr).Value
            Debug.Print myArray(myCount)
        Next lngLoopCtr
        .Rows("1:1").Clear
    End With

    '****************************************
    'Testing
    'ChDir "C:\TestData"

    'Runtime
    ChDir "C:\MIKE\Stress\SF_filesC2"
    '****************************************

    With Worksheets("ImportText")
        .Cells.Clear
        For myCount = LBound(myArray) To UBound(myArray)
            ThisFile = myArray(myCount)
            Open ThisFile For Input As #1
            Ctr = 0
            Do
                Line Input #1, Data
                Ctr = Ctr + 1
                Worksheets("ImportText").Cells(Ctr, 1).Value = Data
            Loop While EOF(1) = False
            Close #1
            lngITLastRow = Sheets("ImportText").Range("A" & Rows.Count).End(xlUp).Row
            lngLastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            Sheets("ImportText").Range("A1:A" & lngITLastRow).Copy Sheets("Sheet1").Range("A" & lngLastRow + 1)
        Next myCount
    End With
    Sheets("ImportText").Delete
    Worksheets("Sheet1").Columns("A:A").AutoFit
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Then run the "SFDATACOMPARISON" macro.

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

satish · January 27th, 2014, 11:37 AM

Hello,

Can You help me on following issue?

I have multiple csv files naming like this:"XP0012DYN101_07_01_2013.csv"

"XP0" is same for all files.
"XXX" is variable
"DYN" is constant
"101" may be same or different
"_07_01_2013" date ( weekly date interval)

Thank You

Satish