Automatically importing data into Excel

[RJSIGKITS]

Hi guys.

Okay, so I have a potentially really tricky one here...

I have a design software, in which I can export a design to an item list in either a standard .txt Text document, or an overly detailed XML Document. Whilst the software allows me to specify what information to export to the .txt document, I can't figure out how to control what is exported to the XML, where it exports all sorts of useless information that it seems would take an age to sort...

I then have an excel quoting document, in which I would like to achieve the ability to 'import a design' with the click of a VBA button, which will import either of the two options, and correctly provide a quotation to suit, based on the design.

I need Excel to read the data from one of the exported files, and to separate the information into the correct columns.
I can open the .txt doc and copy all, then paste it into excel, which works, but I need the process to be automated with the click of a button, if possible.

Anyone fancy having a stab at where to start? I appreciate you may require more information...

 

[RJSIGKITS]

Would I be over complicating it to try to get EXCEL VBA to do the following:
- Button click = VBA for 'Get External Data' - from TEXT,
- File browser - User selects design's .TXT file,
- (Step 1) VBA to specify 'Delimited' - Start import at row = '1' - Next,
- (Step 2) Delimeters = 'Tab' - Next
- (Step 3) Finish
- (Step 4) - put the data into worksheet specified as 'Design import' at cell =$A$1

 

[John_w]

Just record all those steps (starting with the Button click) with the macro recorder and Excel will generate the VBA code in a module in the VB editor. This macro will always import the specific file you choose in the file browser, but it can be easily modified to include a file browser in the code, allowing you to choose a different file each time you run it.

 

[RJSIGKITS]

Thanks for the reply. I've managed to get this, and it functions While I'm setting it up, but then if I try to run it again, it Highlights the 1st three lines in yellow on the debugger.
Then also, I can't figure out how to change it to work as a file browser for the user to select the txt file they want, as it will be a different one every time.

Sub DesignImport()
'
' DesignImport Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\Data\Docs\Tonido\1. Kitchens\2 CLIENTS\Chapple755078\Chapple755078 Kitchen V1.txt" _
        , Destination:=Range("DesignImport!$A$1"))
        .Name = "Chapple755078 Kitchen V1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[John_w]

This macro includes the file browser and clears the sheet before importing the selected file.

Public Sub DesignImport()
    
    Dim selectedFile As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select text file"
        .InitialFileName = "D:\Data\Docs\Tonido\1. Kitchens\2 CLIENTS\Chapple755078\"
        If .Show = False Then
            'Cancel clicked
            Exit Sub
        End If
        selectedFile = .SelectedItems(1)
    End With
    
    With Worksheets("DesignImport")
        .Cells.Clear
        With .QueryTables.Add(Connection:="TEXT;" & selectedFile, Destination:=.Range("A1"))
            .Name = "text file"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 65001
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        .QueryTables(1).Delete
    End With
    
End Sub