PDA

View Full Version : Import CSV to Excel problem, paste only to 1 row



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