How about something like this:
Option Explicit
Sub testme01()
Dim resp As Boolean
Dim myNames As Variant
Dim iCtr As Long
Dim oRow As Long
Dim myStr As String
myNames = Array("C:\my documents\excel\test.txt", _
"C:\my documents\excel\test2.txt")
oRow = 0
For iCtr = LBound(myNames) To UBound(myNames)
myStr = ""
resp = DoTheWork(myFileName:=myNames(iCtr), myContents:=myStr)
If resp = True Then
oRow = oRow + 1
ActiveSheet.Cells(oRow, "A").Value = "'" & myStr
Else
MsgBox "something bad happened with: " & myNames(iCtr)
End If
Next iCtr
End Sub
Function DoTheWork(myFileName As Variant, myContents As String) As Boolean
Dim FSO As Object
Dim RegEx As Object
Dim myFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
DoTheWork = False
If FSO.fileexists(myFileName) = False Then
'Do nothing
Else
Set myFile = FSO.OpenTextFile(myFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = Chr(13)
myContents = .Replace(myContents, "")
End With
If Len(myContents) 32767 Then
'do nothing
Else
DoTheWork = True
End If
End If
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
saybut wrote:
Hi,
does anyone know if this is possible... I need to import some text
files into excel but when I use the DATAImport external dataimport
Excel seperates the data into multiple cells.
I need to get it so that when the text file is imported (they aren't
particualry long either, usually just 10-15 short lines), each file
will be in its entirity in a single cell.
I've attached an image incase that isn't particulary clear. I'm not
sure if this is possible to import multiple files like this so may just
have to do it manually.
Many thanks in advance.
+-------------------------------------------------------------------+
|Filename: Text_Import.gif |
|Download: http://www.excelforum.com/attachment.php?postid=3428 |
+-------------------------------------------------------------------+
--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=374146
--
Dave Peterson