CSV files are text files, not Excel workbook structure files (to check, open the CSV file in Notepad). The Excel application only cleverly converts these files to the structure of the workbook (basically the data is imported into the empty sheet).
For this reason, I suggest handling them as text files, not as workbooks. It's faster.
I don't understand what you want to achieve in question # 4, so I skipped them in the code.
Sub open_csv_file3()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim lasersn As String
Dim Masterwb As Workbook
Dim rng As Range
Dim varData As Variant
Dim blnIsFirst As Boolean
Dim lFrstRow As Long
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
Set Masterwb = ThisWorkbook
sPath = "E:\Cash Flow\"
For Each rng In Masterwb.Worksheets("Name Mapping").Range("Name").Cells
blnIsFirst = True
lasersn = rng.Value
sFil = Dir(sPath & lasersn & "*.csv")
Do While sFil <> ""
strName = sPath & sFil
If blnIsFirst Then
varData = ReadLineFromFile(strName)
blnIsFirst = False
lFrstRow = 1
Else
varData = ReadLineFromFile(strName, 1)
End If
With Masterwb.Worksheets(lasersn).Range("AF" & lFrstRow).Resize(UBound(varData))
.Value = Application.Transpose(varData)
.TextToColumns DataType:=xlDelimited, Comma:=True
End With
lFrstRow = lFrstRow + UBound(varData)
sFil = Dir
Loop
Next rng
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Function ReadLineFromFile(strFilename As String, _
Optional lFrom As Long = -1, _
Optional lTo As Long = -1) As Variant
Dim hf As Integer
Dim sArrLines() As String, i As Long
Dim sLines As String
Dim sTmp As String
hf = FreeFile
Open strFilename For Input As #hf
sLines = Input$(LOF(hf), #hf)
Close #hf
sArrLines = Split(sLines, vbNewLine)
If lTo = -1 Then
lTo = UBound(sArrLines)
End If
If lFrom > -1 Then
If lTo > -1 Then
'Lines From...To
On Error Resume Next
For i = lFrom To lTo
sTmp = sTmp & sArrLines(i) & vbNewLine
Next i
sTmp = Left(sTmp, Len(sTmp) - Len(vbNewLine))
On Error GoTo 0
sArrLines = Split(sTmp, vbNewLine)
Else
'one line
On Error Resume Next
ReadLineFromFile = sArrLines(lFrom)
On Error GoTo 0
End If
Else
'all lines
End If
ReadLineFromFile = sArrLines
End Function
Artik