How to Copy and paste my data in the selected file when I use EXCEL VBA

Question

I am using Excel VBA and lot of people helped me to overcome challengers as a new VBA user.

I am using the following code to open a txt file using excel VBA command button, but it open as a separate txt file, I need the first sheet of this opened txt file to automatically select to the clip board and paste in the template I am using. And close the txt file as it is not necessary.

The file name of this txt file is not unique and it should record in the program memory as a variable (I believe string)

Private Sub CommandButton1_Click()
With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "I:\Group"
    .Filters.Clear
    .Title = "Your Title"
    If Not .Show Then
        MsgBox "No file selected.": Exit Sub
    End If
    Workbooks.OpenText .SelectedItems(1), Origin:=xlMSDOS, StartRow:=23, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(6, 2), Array(23, 1), Array(30, 2), Array(63, 2), Array(68, 1), _
            Array(77, 4), Array(88, 4), Array(101, 1), Array(117, 1)), TrailingMinusNumbers:= _
            True
End With
End Sub

Opening the file we discussed in http://stackoverflow.com/questions/27957741/import-txt-file-with-open-file-dialog-box-and-break-them-into-cells — Isu, Jan 20 '15 at 03:58

score 0 · Accepted Answer · answered Jan 20 '15 at 06:28

Private Sub CommandButton1_Click()
 With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "I:\Group"
    .Filters.Clear
    .Title = "Your Title"
    If Not .Show Then
        MsgBox "No file selected.": Exit Sub
    End If
    Workbooks.OpenText .SelectedItems(1), Origin:=xlMSDOS, StartRow:=23, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(6, 2), Array(23, 1), Array(30, 2), Array(63, 2), Array(68, 1), _
            Array(77, 4), Array(88, 4), Array(101, 1), Array(117, 1)), TrailingMinusNumbers:= _
            True

'The below is to copy the text file into a new sheet in the workbook and paste those values in sheet 1

    Set myfile = ActiveWorkbook
    ActiveWorkbook.Sheets(1).Copy _
    after:=ThisWorkbook.Sheets(1)
    myfile.Close
    ActiveSheet.Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
End With
End Sub

How to Copy and paste my data in the selected file when I use EXCEL VBA

1 Answers1