Airborne
10-23-2004, 08:22 AM
Hello, A while ago I got a great code for looking for a file in a directory and importing it in Excel
Sub Import()
If Sheets("Sheet1").[A1].Value = "" Then Exit Sub
Dim fName As String
Dim mybook As Workbook
fName = Sheets("Sheet1").[A1].Value & ".txt"
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\Server\submap\ (file://\Serversubmap)"
.SearchSubFolders = True
.Filename = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
Else
MsgBox fName & " not found!"
End
End If
End With
Else
MsgBox fName & " Is already open!"
End If
End Sub
My problem is that the text file I want to import has to come with this line
Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(21, 1), Array(34, 1), Array(46, 1), _
Array(60, 1), Array(63, 1), Array(66, 1))
For a better view of the sheet. Where can I put the line in the import code? I've tried to put the line behind Workbooks.Open (.FoundFiles(1)) but it doesn't work:eek:
Sub Import()
If Sheets("Sheet1").[A1].Value = "" Then Exit Sub
Dim fName As String
Dim mybook As Workbook
fName = Sheets("Sheet1").[A1].Value & ".txt"
On Error Resume Next
Set mybook = Workbooks(fName)
On Error GoTo 0
If mybook Is Nothing Then
With Application.FileSearch
.NewSearch
.LookIn = "\\Server\submap\ (file://\Serversubmap)"
.SearchSubFolders = True
.Filename = fName
If .Execute > 0 Then
Workbooks.Open (.FoundFiles(1))
Else
MsgBox fName & " not found!"
End
End If
End With
Else
MsgBox fName & " Is already open!"
End If
End Sub
My problem is that the text file I want to import has to come with this line
Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(21, 1), Array(34, 1), Array(46, 1), _
Array(60, 1), Array(63, 1), Array(66, 1))
For a better view of the sheet. Where can I put the line in the import code? I've tried to put the line behind Workbooks.Open (.FoundFiles(1)) but it doesn't work:eek: