VBA open text file and save into existing workbook

Hi all,

I'm currently using VBA to do the following tasks:

- open a text file

- select only the relevant data

- paste the data into a defined sheet in my workbook

I managed to get the first two points done, however the data are displayed in a new workbook so I would need to copy and paste manually into my workbook. Is there anyway to paste the data automatically into my existing workbook rather than opening a new one?

I'm trying to make that process automatic but I'm afraid my vba skills are way too limited. Would anyone be able to help me?

Here is my code:

Sub Load_data()


Workbooks.OpenText FileName:=Application.GetOpenFilename, Origin:= _
932, StartRow:=71, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=True, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
Range("A:A,B:B,D:D,E:E,F:F,G:G,H:H,J:J,K:K,L:L").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft


Columns("A:B").Select


Selection.Replace What:="-999.25", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete


Range("A1").Select


End Sub

Thank you!

Luca

Quote from royUK

Does this help?

Code

Sub ImportMacro()
    Dim vbComp As Object
    Dim wbFn As Variant, txtFn As Variant
    Dim ff As Long
    Dim line As String, vbCode As String, fn As String
    
    wbFn = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If TypeName(wbFn) = "Boolean" Then Exit Sub ''///User cancelled
    txtFn = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If TypeName(txtFn) = "Boolean" Then Exit Sub ''///User cancelled
    ff = FreeFile
    Open txtFn For Input As #ff
        While Not EOF(ff)
            Input #ff, line
            vbCode = vbCode & IIf(LenB(vbCode), vbLf, "") & line
        Wend
    Close #ff
    Debug.Print vbCode
    Application.ScreenUpdating = False
    On Error GoTo exit_proc
    With Workbooks.Open(wbFn)
        Set vbComp = .VBProject.VBComponents.Add(1)
        vbComp.CodeModule.AddFromString vbCode
        Set vbComp = Nothing
        Select Case UCase(Right(wbFn, 4))
            Case "XLSB", "XLSM", ".XLS"
                .Close True
            Case Else
                ''///Save as macro enabled workbook
                ff = 0
                fn = Left(wbFn, InStrRev(wbFn, ".")) & "xlsm"
                While LenB(Dir(fn))
                    ff = ff + 1
                    fn = Left(wbFn, InStrRev(wbFn, ".") - 1) & "(" & ff & ").xlsm"
                Wend
                .SaveAs fn, 52
                .Close False
        End Select
    End With
exit_proc:
End Sub

Display More

Yes, that's it!

Thank you for your help