PDA

View Full Version : Importing text file -



gwpatton
07-12-2010, 10:52 AM
Hi,

I am trying to get a comma delineated text file to import into excel using VBA and have it automatically delineated. The text file data will always be commas and nothing else. I have managed to get the files to import but I still have to go though the delineation wizard.
Below is the code I am using I took it from another template that I am working on. Any help would greatly be appreciated.
Sub ImportTextFile()
Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Dest As Range

' Turn off screen updating.
Application.ScreenUpdating = False
' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = Range("A1")
' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")
' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
' Close the book containing the text file.
SourceBook.Close True
End Sub