Hi there
Please please,, can you help me with the import file macro below
The import part works fine, but when it paste the file into excel, it is not pasted side by side, I need each file to be pasted side by side
I hope someone can help me, this is my macro
Please please,, can you help me with the import file macro below
The import part works fine, but when it paste the file into excel, it is not pasted side by side, I need each file to be pasted side by side
I hope someone can help me, this is my macro
Code:
Dim sh As Worksheet, sPath As String, sName As String
Dim r As Range, fName As String
Dim sh1 As Worksheet
With ThisWorkbook
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
Set sh = ActiveSheet
With ThisWorkbook
.Worksheets.Add After:=.Worksheets(.Worksheets.Count)
End With
Set sh1 = ActiveSheet
sPath = "C:\Data\RawData\"
sName = Dir(sPath & "*.dat")
Do While sName <> ""
sh1.Activate
fName = sPath & sName
sh1.Cells.ClearContents
With sh1.QueryTables.Add( _
Connection:="TEXT;" & fName, _
Destination:=sh1.Range("$A$2"))
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True 'False
.TextFileSemicolonDelimiter = False 'True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1) 'Array(3, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Set r = sh.Cells(Rows.Count, 1).End(xlUp)
If r.Value <> "" Then Set r = r(1)
sh1.Range("A1").CurrentRegion.Copy
r.PasteSpecial xlValue
sName = Dir()
Loop
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True