guatelize
05-11-2011, 08:24 AM
I found a nice macro which imports my .csv file into excel. But the problem is that only one row is filled with all the references. What I need is that the macro imports the figures the way they are in my csv file in excel starting in sheet3 cell a1 to y1, keeping my formulas in z1
Thanks
Oliver
Sub CopyLogFile(ByVal FilePath As String, ByVal FileName As String, ByVal Wks_Name As String)
Dim Data As Variant
Dim FSO As Object
Dim Matches As Object
Dim NextRow As Long
Dim RegExp As Object
Dim TxtFile As Object
Set Wks = Worksheets(Wks_Name)
NextRow = Wks.Cells(1, "A").End(xlUp).Row
NextRow = IIf(NextRow > 1, NextRow + 1, NextRow)
FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
FileName = FilePath & FileName
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Pattern = "(;)|(\|)|(\t)|(\{)|(@)"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TxtFile = FSO.GetFile(FileName).OpenAsTextStream(1, -2)
Do While Not TxtFile.AtEndOfStream
S = TxtFile.ReadLine
If RegExp.Test(S) = True Then
Set Matches = RegExp.Execute(S)
Data = Split(S, Matches(0))
Wks.Cells(NextRow, 1).Resize(1, UBound(Data) + 1) = Data
NextRow = NextRow + 1
Else
If S <> "" Then
Wks.Cells(NextRow, 1) = S
NextRow = NextRow + 1
End If
End If
Loop
Set FSO = Nothing
Set RegExp = Nothing
Set TxtFile = Nothing
End Sub
Sub TestIt()
FilePath = "H:\Sales\PR"
FileName = "PR_BXL.csv"
CopyLogFile FilePath, FileName, "Sheet3"
End Sub
Thanks
Oliver
Sub CopyLogFile(ByVal FilePath As String, ByVal FileName As String, ByVal Wks_Name As String)
Dim Data As Variant
Dim FSO As Object
Dim Matches As Object
Dim NextRow As Long
Dim RegExp As Object
Dim TxtFile As Object
Set Wks = Worksheets(Wks_Name)
NextRow = Wks.Cells(1, "A").End(xlUp).Row
NextRow = IIf(NextRow > 1, NextRow + 1, NextRow)
FilePath = IIf(Right(FilePath, 1) <> "\", FilePath & "\", FilePath)
FileName = FilePath & FileName
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Pattern = "(;)|(\|)|(\t)|(\{)|(@)"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TxtFile = FSO.GetFile(FileName).OpenAsTextStream(1, -2)
Do While Not TxtFile.AtEndOfStream
S = TxtFile.ReadLine
If RegExp.Test(S) = True Then
Set Matches = RegExp.Execute(S)
Data = Split(S, Matches(0))
Wks.Cells(NextRow, 1).Resize(1, UBound(Data) + 1) = Data
NextRow = NextRow + 1
Else
If S <> "" Then
Wks.Cells(NextRow, 1) = S
NextRow = NextRow + 1
End If
End If
Loop
Set FSO = Nothing
Set RegExp = Nothing
Set TxtFile = Nothing
End Sub
Sub TestIt()
FilePath = "H:\Sales\PR"
FileName = "PR_BXL.csv"
CopyLogFile FilePath, FileName, "Sheet3"
End Sub