Hello,
I am trying to incorporate two separate VB buttons in my Excel VB project in Excel 2002. One is to be used to import delimited ASCII files, one is to be used to import Excel files, both to a specific table. They are meant to be used repeatedly, so I don't want to leave defined Query Tables.
Both work very nicely, except that the filters.add command does not appear to work. If I manually set the file type to Text Files when the browser opens, then I can of course view Text files only, but when I click on the other button for Excel Files, the filter displays Text only, even though the code has a command to add the Excel Files filter. If I manually change the file type to Excel files, then when I later click on the text import button, with the code below, I still see Excel Files only in the browser.
I am in learning mode. Can anyone recommend the VBA Express training and certification? I just passed my MOS Master Certification and ready to become reasonably proficient in VBA.
Yelling in Yukon!, (Paul)
Private Sub ImportTextFileMacroButton_Click()
On Error GoTo ImportTextFileError
Dim sFile As String
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Text files", "*.prn; *.txt; *.csv", 1
If .Show = -1 Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & .SelectedItems(1), Destination:=ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0))
.Name = "TEE-ACCOUNTER"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
'.RefreshStyle = xlInsertDeleteCells
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 12
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 9, 9, 9, 9, 9, 9, 9, 9, _
9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
ActiveSheet.Unprotect ("ABC123")
.Refresh BackgroundQuery:=False
ActiveSheet.Protect Password:="ABC123", AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowSorting:=True
.Delete
End With
End If
End With
Columns("B:AY").EntireColumn.AutoFit
Range("InputCell").Select
MsgBox "The selected JE Text File was successfully inported and column widths adjusted. Remember to modify your Input Table Headings to match your new import file columns, then click the T-Accounter ICON/Button to T-Account your JEs.", vbOKOnly, "Notice"
Exit Sub
ImportTextFileError:
MsgBox "File import aborted or unsuccessful."
End Sub
Reply With Quote
Originally Posted by PWhatley
