Import csv

[ryan8200]

I have two csv files need to import to Excel. I would like to import both csv files to current workbook as below
(a) create Sheet_Company_A and import 1st csv files
(b) create Sheet_Company_B and import 2nd csv files

Sub Import()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim Filename As Variant, Path As Variant, FileToRead As Variant
    Dim Brand() As String, TextString As String
    Dim i As Integer
    Type = Split("A,B", ",")
    Path = "C:\Users\My\Downloads\"
    Filename = Dir(Path & "Report*")
    For i = LBound(Type()) To UBound(Type())
    Do While Filename <> ""
        ' Destination & Source Path
           Filename = Dir()
           Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet_Company_" & Type(i)
           Set FileToRead = FSO.OpenTextFile(Path & Filename, ForReading) 'add here the path of your text file
           TextString = FileToRead.ReadAll
           ThisWorkbook.Sheets("Sheet_Company_& Type(i)").Range("A1").Value = TextString
           FileToRead.Close
    Loop
    Next i
End Sub

How to modify above code so that it can execute dynamically for n csv files ?

 

[mohadin]

Hi
Try this (Not tested)

Sub Import()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim Filename As Variant, Path As Variant, FileToRead As Variant
    Dim Brand() As String, TextString As String
    Dim i As Integer
    '**********************************
    Type_ = Application.Transpose(Evaluate("row(65:91)"))
    For i = 1 To UBound(Type_)
        Type_ = Chr(Type_)
    Next
    '**********************************
    Path = "C:\Users\My\Downloads\"
    Filename = Dir(Path & "Report*")
    For i = LBound(Type_) To UBound(Type_)
        Do While Filename <> ""
            ' Destination & Source Path
            Filename = Dir()
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet_Company_" & Type_(i)
            Set FileToRead = FSO.OpenTextFile(Path & Filename, ForReading)    'add here the path of your text file
            TextString = FileToRead.ReadAll
            ThisWorkbook.Sheets("Sheet_Company_& Type_(i)").Range("A1").Value = TextString
            FileToRead.Close
        Loop
    Next i
End Sub

 

[ryan8200]

Hi
Try this (Not tested)

Sub Import()
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim Filename As Variant, Path As Variant, FileToRead As Variant
    Dim Brand() As String, TextString As String
    Dim i As Integer
    '**********************************
    Type_ = Application.Transpose(Evaluate("row(65:91)"))
    For i = 1 To UBound(Type_)
        Type_ = Chr(Type_)
    Next
    '**********************************
    Path = "C:\Users\My\Downloads\"
    Filename = Dir(Path & "Report*")
    For i = LBound(Type_) To UBound(Type_)
        Do While Filename <> ""
            ' Destination & Source Path
            Filename = Dir()
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet_Company_" & Type_(i)
            Set FileToRead = FSO.OpenTextFile(Path & Filename, ForReading)    'add here the path of your text file
            TextString = FileToRead.ReadAll
            ThisWorkbook.Sheets("Sheet_Company_& Type_(i)").Range("A1").Value = TextString
            FileToRead.Close
        Loop
    Next i
End Sub

Hi Mohadin, may I know the intention for this code ?
Type_ = Application.Transpose(Evaluate("row(65:91)"))

 

[mohadin]

Application.Transpose(Evaluate("row(65:91)"))
Crates an sequence Nub array(65,66,....91) related to characters (A,B,........Z)
sorry miss typing
should be

'***************************
  Type_ = Application.Transpose(Evaluate("row(65:91)"))
    For i = 1 To UBound(Type_)
        Type_(i) = Chr(Type_(i))
    Next
'***************************