Importing from .txt

[jamieknight989]

Hi there,

I work in an office where a lot of the time we copy and paste data from.txt documents into a database -- for example we'll extract the data after certain terms such as:

Date: 'dd/mm/yyyy'
Term: 99 years from 31st September 2007

Now I'd like to know if it would be possible for us to automate much of this and extract all the relevant data from 1 text file to 1 row in a spreadsheet, then moving on to the next file and importing all of that into a new row. The .txt file isn't formatted in columns at all or tab or comma delimited so I know it would be quite complicated - any help or advice on where to go to learn how to write the coding for such a process would be much appreciated

Cheers

 

[jmthompson]

The best first step is to record a macro while doing the task manually.

 

[webster188]

Does not work for 100%.
After recording the macro and running it again, instead of importing the specified file, a window is opened with the file already preselected.
I still need to press "import" for excel to actually import the data.

 

[jmthompson]

Post the code from the recorded macro

 

[webster188]

Hi,

Code below recorded

Sheets("Import Data").Select
    Range("A1").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;D:\Documenten\Project Files\03 - Tools\P07292T - Maintenance ATP Mammo v5\Final Acceptance Certificate EN.txt"
        .TextFilePlatform = 28591
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ";"
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With