PDA

View Full Version : [SOLVED] Import rules for text files



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:

Ken Puls
10-23-2004, 08:52 AM
Try changing

Workbooks.Open (.FoundFiles(1))

To:


Workbooks.Opentext filename:=.FoundFiles(1),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))

I haven't tested this, but I don't think you need the extra brackets around the .foundfiles(1) componenent of the code. If it doesn't work, try adding those, and if still not, post back.

HTH,

Airborne
10-23-2004, 09:15 AM
Great, thanks. You are right, I don't need the extra brackets and the code works.:vv

Ken Puls
10-23-2004, 10:03 PM
Glad to help! :)

Not sure if you are aware, but at the top of the post, in the "Thread Tools" menu, you can mark this as solved.

Cheers,

Airborne
10-24-2004, 07:33 AM
I didn't know that, thanks again:vv