Import the text file into same workbook but after active worksheet.

[amittheexcel]

Hi All,

As per below codes i'm able to import the excel, but this codes importing the excel into actives sheet .

Can you please arrange this codes in such a manner so that it will import sheet into next sheet of active sheet.

Sub ImportTextFile()


Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean


' Turn off screen updating.
Application.ScreenUpdating = False


' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell


' 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


' Close the book containing the text file.
SourceBook.Close False


End Sub

 

[wideboydixon]

You can make changes here:

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

If you want to add a new sheet after the active sheet then:

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = DestBook.Sheets.Add(After:=ActiveSheet).Cells(ActiveCell.Row, ActiveCell.Column)

If you want to simply select the next sheet (i.e. don't add a new one) then:

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = DestBook.Sheets(ActiveSheet.Index + 1).Cells(ActiveCell.Row, ActiveCell.Column)

In both cases I'm assuming you want the same cell in the new sheet to be where the data is pasted.

WBD

 

[amittheexcel]

Thanks wideboydixon it's working fine