-
Import CSV to Excel problem, paste only to 1 row
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
[VBA]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[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules