Import Data from Text File into Master File (changing target directories)- Excel

Question

I know that this post is going to seem very similar to many other posts to those who understand it. I have learned C++ and C#, both only well enough to do minor work, and I just cannot understand VBA well enough to make this macro happen.

I have just under 100 files that need to be imported to a master file. I cannot modify the source files but the master file needs only select columns.

This macro works almost perfectly I just need the ability to select new file paths for each instance of the macro being used. I have found many posts that seem to use something like this:

https://social.msdn.microsoft.com/Forums/office/en-US/231cbfc5-95ad-4673-a20c-f87355c6bc5e/prompt-user-for-file-name-to-import-as-fixed-width-text-file?forum=exceldev

in order to first make a filepath into a variable and then pass it to the ActiveSheet.QueryTables.Add command. I might just be missing something but there are a lot of variables between all of the examples that I just don't understand. It seems like the msdn page for vba is much less intuitive than those for C#. Either that or I am simply unable to understand them having not taken the babysteps that I need.

Using the macro tool I made the large majority of the code below. About an hour of working allowed me to replace the hardcoded cell with the active cell.

Sub InputDataFromTextFile()
'
' InputDataFromTextFile Macro
'
' Keyboard Shortcut: Ctrl+t
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\Dropbox\College 2016-2017\Research\Buffered Solutions\pH10\With PDADMAC\30.CSV" _
        , Destination:=ActiveCell)
        .Name = "30"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I would appreciate any and all help that could be given. Thank you all so much for your time regardless.

The example you cite seems pretty straightforward. If you haven't, you should also read the MSDN article on the [Application.GetOpenFilename Method](https://msdn.microsoft.com/en-us/library/office/ff834966.aspx). Write it as a separate macro and fiddle with it to see how it works. What specific problem are you running into with it. — Ron Rosenfeld, Feb 21 '17 at 03:32
The code referenced tires to select an open table when I use it. I don't really need this as my code written by the macromaker just opens a file at a target. I just need to make it so the target is selected by the user rather than hardcoded. The issue I'm having is that the variable he uses to re-direct the target to a selected one "FileName" is written into so many code sections that I can't determine how to just get out what I need to. I tried for about another hour just now and most of last night. — hotmaildotcom1, Feb 21 '17 at 19:28
Is this question resolved? Do note: you can answer your own question. Also, do you really want a dialog pop-up file picker for each of the just under 100 files? Consider looping through a directory with user selecting a folder picker only once. — Parfait, Feb 21 '17 at 20:25
@Parfait alright I am now at a point where the looping is going to be needed. Should I continue on this post or move to another? — hotmaildotcom1, Apr 28 '17 at 01:52

score 0 · Answer 1 · edited Nov 14 '17 at 20:53

So immediately after posting, I hadn't figured it out in the comments I tried something and got it to work. Here is the code as it stands.

Sub InputDataFromTextFile()

Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant

' Set up list of file filters
    Filt = "All Files (*.*),*.*"

' Display Text Files by default
    FilterIndex = 1

' Set the dialog box caption
    Title = "Select a File to Import"

' Get the file name
    FileName = Application.GetOpenFilename _
        (FileFilter:=Filt, _
        FilterIndex:=FilterIndex, _
        Title:=Title)

' Exit if dialog box is canceled
    If FileName = False Then
        MsgBox "No file was selected."
        Exit Sub
    End If

'
' InputDataFromTextFile Macro
'
' Keyboard Shortcut: Ctrl+t
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FileName, _
        Destination:=ActiveCell)
        .Name = "30"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

One of my largest issues was honestly that once excel was restarted the Ctrl+T that I had hotkeyed to the command reverted to making a table. I couldn't figure out where it was coming from! Thank you for your help. This saves me a great deal of time.

While the comment made by Parfait about cycling through the files and having them auto import is the next step and a very obviously desired trait in the code, it seems like a lot for me right now. I'm already saving upwards of an hour per mastersheet which is just amazing. Thank you all so much again.

Import Data from Text File into Master File (changing target directories)- Excel

1 Answers1