CODEWITHSUNDEEP
vbaexcel
suzie_q
suzie_q

Reputation: 39

VBA Excel: Specify Path To Import Text File Via Text Box on Userform

Okay I know this is a really stupid way to do this, but I have a userform that allows user input for 3 things: the path in which a text file is located, the name of the text file, and the location in which to import the data from the text file. What I want to do is to follow the path that the user inputs, get the text file via the name the user inputs, and place whatever the text file contains into the range that the user inputs.

I know it can be easily be simplified using Application.GetOpenFilename, but you gotta do what you gotta do. What I need to do is to be able to specify the path, specify the text file, and the location in which to load all of the text from a text file into a range with comma delimiters. Currently the program either breaks on the line ".Refresh BackgroundQuery:=False" or doesn't do anything at all, and I don't know how to move forward. My code is as follows- apologies for the messiness. Please let me know if you can help!

Private Sub OKButton_Click()

Dim filePath As String, destin As String, fileName As Variant

destin = DestinationTextBox.Value
fileName = TextFileNameTextBox.Value

filePath = Dir(FileLocationTextBox.Value)

'filePath = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'oh, if only

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
    filePath, Destination:=Range(destin))
        .Name = fileName & ".txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "" & Chr(10) & ""
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
           1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
           1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
           1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End With
End Sub

Upvotes: 0

Views: 2707

Answers (1)

David Zemens
David Zemens

Reputation: 53623

but I have a userform that allows user input for 3 things: the path in which a text file is located, the name of the text file, and the location in which to import the data from the text file.

I can't think of any good reason to separate the path and the file name, especially when doing so invites lots of opportunity for user error, which means you'll have to add a lot of error-handling logic to deal with fat-fingered or mistyped filenames, or paths, adding or ensuring the consistent use of a path separator at the end of the path (or not) for your subsequent concatenation, etc., etc.

Something like this should set you on the right track. Use one of the TextBox events to invoke the Application.FileDialog(msoFileDialogFilePicker), return the value (being the full path) to the selected item to the TextBox. 

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' this event fires when user clicks in the textbox
' you may need to use other events also, but this is to illustrate
' call the procedure that actually does the work:
TextBox1.Value = getFileToOpen
End Sub
Private Function getFileToOpen()
' this function opens the FileDialog and returns the name of the selected file
Dim fileName As String
Dim fdlg As FileDialog
Dim f As FileDialogFilter
Set fdlg = Application.FileDialog(msoFileDialogFilePicker)
fdlg.Filters.Clear
Set f = fdlg.Filters.Add("Text Files", "*.txt", 1)
fdlg.FilterIndex = 1
If fdlg.Show Then
    fileName = fdlg.SelectedItems(1)
End If
getFileToOpen = fileName

End Function

If you absolutely must maintain the two TextBox approach, then parse the resulting value from the getFileToOpen function and assign to the several text boxes as needed.

Also, your DestinationTextBox.Value should be a RefEdit control, not a TextBox. The RefEdit control is specifically designed for allowing the user to select a range of cell(s).

Assuming a 1004 error with your QueryTable, one likely culprit is that you've provided the path but not the name of the file. When you do this:

filePath = Dir(FileLocationTextBox.Value)

filePath now represents not a path, but a file Name! So the identifier filePath is confusing, because that's not what it contains. 

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
    filePath

And unless this file name exists in the active workbook's directory (unlikely, but possible) a 1004 error is the only possible result of this code, becuase the file you're referring to in the filePath variable doesn't exist in the active directory.

(technically this could resolve to a file in the working directory only, but odds are that no such filename exists

Instead, building upon the solution I offered above (using FileDialog to ensure you have a valid filepath/filename to use, without relying on clumsy user input), do this:

Dim fullFileName as String
fullFileName = TextFileNameTextBox.Value
' Make sure there's a value here
If fullFileName = vbNullString Then Exit Sub
' double-check that it's a valid path:
If Len(Dir(fullFileName)) = 0 Then Exit Sub

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
    fullFileName & , ...

Upvotes: 1

Related Questions