Merging Macro - GetOpenFilename & Workbooks.OpenText

A

al007

Sub GetImportFileName2()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & "Lotus Files (*.prn),*.prn," &
"Comma Separated Files (*.csv),*.csv," & "ASCII Files (*.asc),*.asc," &
"All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select a File to Import"""
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."""
Exit Sub
End If
' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg


Workbooks.OpenText FileName:="C:\Directory\select.txt",
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
1), Array(31 _
, 4), Array(39, 1), Array(46, 1), Array(69, 1), Array(89, 1),
Array(109, 1), Array(111, 1))
Columns("A:J").Select
Columns("A:J").EntireColumn.AutoFit
Range("A1").Select


End Sub



Can anybody help me fixing the above macro

With what should I replace FileName:="C:\Directory\select.txt", to
make above macro work bearing in mind I can select more than 1 file

Thxs beforehand
 
T

Tom Ogilvy

Sub GetImportFileName2()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Long
Dim Msg As String
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"
' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select Files to Import"""
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."""
Exit Sub
End If
' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)

Workbooks.OpenText FileName:=FileName(i), _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0,1), Array(31, 4), _
Array(39, 1), Array(46, 1), Array(69, 1), _
Array(89, 1), Array(109, 1), Array(111, 1))
Columns("A:J").Select
Columns("A:J").EntireColumn.AutoFit
Range("A1").Select
Next i
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top