Loading Specific File

[AGROTRON]

Hi,

I would like to ask if anyone knows how I can make a makro that reads a text format data value from a cell ( example: B2; B2 = FILE001 ) and uses this info in next line statement:
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\elektra2\My Documents\ALAN\FILE001.FLW", Origin:= _

The apendix *.FLW, *.TXT, *.XLS should be fixed for each macro.

For detailed info you can mail me at mojster29@email.si

bye

[antoka05]

myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open filòe
fullNameToOpen = myPath & fileToOpen

'jere insert your code
Workbooks.OpenText Filename:= _
fullNameToOpen, Origin:= _

fileToOpen = Dir
Loop
Next

[AGROTRON]

thanks this looks promising but i don`t know which form should it have:

1) your program inside makro

Sub Makro2()
'
' Makro2 Makro
' Makro ustvaril: elektra2, dne 18.10.2006
'

'
myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open file
fullNameToOpen = myPath & fileToOpen

'here insert your code
Workbooks.OpenText Filename:= _
fullNameToOpen, Origin:= _

fileToOpen = Dir
Loop
Next

End Sub



2) or

myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open file
fullNameToOpen = myPath & fileToOpen

'here insert your code

Sub Makro2()
'
' Makro2 Makro
' Makro ustvaril: elektra2, dne 18.10.2006
'

'

Workbooks.OpenText Filename:= _
fullNameToOpen, Origin:= _


End Sub

fileToOpen = Dir
Loop
Next


and how does this program know in which file and sheet the program looks for file name in cell B2.

thank`s in advance
Describe a bit with words please.

[AGROTRON]

I have filed in as folloving:

Sub Makro2()
'
' Makro2 Makro
' Makro ustvaril: elektra2, dne 18.10.2006
'

'
myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open file
fullNameToOpen = myPath & fileToOpen

'here insert your code
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\elektra2\My Documents\ALAN\FulNameToOpen", Origin:= _
852, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(17, 1), Array(25, 1), Array(32, 1)), DecimalSeparator:=".", _
fileToOpen = Dir
Loop
Next

End Sub

[AGROTRON]

I got it this far:

Sub Makro2()
'
' Makro2 Makro
' Makro ustvaril: elektra2, dne 18.10.2006
'

'
myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open file
FullNameToOpen = myPath & fileToOpen

'here insert your code
Workbooks.OpenText Filename:= _
FullNameToOpen, Origin:= _
852, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(17, 1), Array(25, 1), Array(32, 1)), DecimalSeparator:=".", _
TrailingMinusNumbers:=True

fileToOpen = Dir
Loop
Next

End Sub

when I run this makro program displays Compile error:

Loop without Do

WHAT TO DO??

[antoka05]

I'm sorry but I didn't remember to put 'end if' before the row with 'loop' statement.

The right code is:
myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b2:b2")
myFilename = myCell.Value

fileToOpen = (Path & myFilename & ".*")
Do While fileToOpen <> ""
If LCase(fileToOpen) Like "*.flw" _
Or LCase(fileToOpen) Like "*.txt" _
Or LCase(fileToOpen) Like "*.xls" Then

'code to open file
FullNameToOpen = myPath & fileToOpen

'here insert your code
Workbooks.OpenText Filename:= _
FullNameToOpen, Origin:= _
852, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(17, 1), Array(25, 1), Array(32, 1)), DecimalSeparator:=".", _
TrailingMinusNumbers:=True

fileToOpen = Dir
End If

Loop
Next

Regards,
Antonio

[AGROTRON]

Thank you for help

I simplified subroutine but it does what it has to. Here`s the code for others to se.

Sub PRETVORBA()
'
' Makro2 Makro
' Makro ustvaril: elektra2, dne 18.10.2006
'

'
myPath = "C:\Documents and Settings\elektra2\My Documents\ALAN\"

For Each myCell In Range("b1:b1")
myFilename = myCell.Value

fileToOpen1 = (myFilename & ".flw")
fileToOpen2 = (myFilename & ".occ")


FullNameToOpen1 = myPath & fileToOpen1
FullNameToOpen2 = myPath & fileToOpen2


Workbooks.OpenText Filename:= _
FullNameToOpen1, Origin:= _
852, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(17, 1), Array(25, 1), Array(32, 1)), DecimalSeparator:=".", _
TrailingMinusNumbers:=True

Next
End Sub