VBA code help needed

[Guest]

Original code is to manual pick the text file to be imported. If I want to
pick 20 files, I have to manual select the 20 files to be import. I cannot
use the Control Key to select all the 20 files and import all at once. How
should I change the VBA code so that I can select a folder and import all the
text files in the folder at once? Thanks.


Sub Import()

Dim myFileName As Variant

Do

myFileName = Application.GetOpenFilename( _
filefilter:="Text Files, *.Txt", Title:="Pick a File")
If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Else
With ActiveSheet.QueryTables.Add( _
Connection:="TEXT;" & myFileName, _
Destination:=Range("A1"))

.Name = "smartscope"
.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 = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If

Loop Until myFileName = False




End Sub

 

[mudraker]

martin

have a look at FileDialog


Extract from help file
Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Su

 

[Guest]

Is FileDialog new to Excel 2003? I have Office 2000 and would have resorted
to using API to browse for the directory

http://www.vba-programmer.com/Snippets/Code_VB/Folder_Selection_API.html

then FileSearch to return all of the text files.

Sub Import()
Dim myFileName As Variant
Dim FullNameOfDir As String

FullNameOfDir = BrowseFolder("What Folder you want to select?")

With Application.FileSearch
.NewSearch
.LookIn = FullNameOfDir
.SearchSubFolders = True
.Filename = "*.txt"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
.Execute
If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
myFileName = .FoundFiles(i)
'rest of your code
Next i
End If
End With
End Sub

 

[Bob Phillips]

It is not new to 2003 but XP?2002, so you don't have it with 2000.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)