JimS
11-01-2011, 06:53 AM
Below is some coded that I use to copy a single column of data from a text file.
Is there away to do this without having to use the “Text Import Wizard”.
I just need Column A of the Text File to be copied/pasted on to a sheet.
The paste should start at Cell A2.
Any ideas?
Thanks…
JimS
Sub Import_Text_File()
Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean
' Turn off screen updating.
Application.ScreenUpdating = False
With Worksheets("Data").Select
Range("A2:A65536").ClearContents
End With
' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell
MsgBox "Please click OK and navigate to your Text File"
' 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
Sheets("Data").Select
Range("A2").Select
DestCell.PasteSpecial Paste:=xlPasteFormats
DestCell.PasteSpecial Paste:=xlValues
'Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
' Close the book containing the text file.
Application.DisplayAlerts = False
SourceBook.Close False
Application.DisplayAlerts = True
' Turn on screen updating.
Application.ScreenUpdating = True
' Run ("Macro1")
End Sub
Is there away to do this without having to use the “Text Import Wizard”.
I just need Column A of the Text File to be copied/pasted on to a sheet.
The paste should start at Cell A2.
Any ideas?
Thanks…
JimS
Sub Import_Text_File()
Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean
' Turn off screen updating.
Application.ScreenUpdating = False
With Worksheets("Data").Select
Range("A2:A65536").ClearContents
End With
' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell
MsgBox "Please click OK and navigate to your Text File"
' 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
Sheets("Data").Select
Range("A2").Select
DestCell.PasteSpecial Paste:=xlPasteFormats
DestCell.PasteSpecial Paste:=xlValues
'Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
' Close the book containing the text file.
Application.DisplayAlerts = False
SourceBook.Close False
Application.DisplayAlerts = True
' Turn on screen updating.
Application.ScreenUpdating = True
' Run ("Macro1")
End Sub