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