I need some assistance with my programm below . I am
getting run time error 9 at the indicated position


Public f_name as variant

Public Sub Main()

f_name = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If f_name < False Then
MsgBox "Open " & f_name
End If


Workbooks.OpenText Filename:=f_name, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
2), Array(17, 2), Array(30, 2), Array(39, 2))
..
..

Workbooks.Open
Filename:="c:\switch_makros\Switch_Temp.xls"
Sheets("Sheet1").Select
..
..
..
Call Temp

End sub


Public Sub Temp()
..
..
Windows(f_name).Activate ' I get run time error 9 here
..
..
end sub

Hi Nathaniel,

The root problem is that the string returned by the GetOpenFilename
method is not the same as the Window name of that file once it has been
opened. Here's one way of rewriting your code that will solve this problem.

Public Sub Main()

Dim f_name As Variant
Dim wkbBook As Workbook

f_name = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If f_name < False Then MsgBox "Open " & f_name

Workbooks.OpenText Filename:=f_name, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
2), Array(17, 2), Array(30, 2), Array(39, 2))

Set wkbBook = ActiveWorkbook
Workbooks.Open Filename:="c:\switch_makros\Switch_Temp.xls"
Sheets("Sheet1").Select
Temp wkbBook

End Sub

Public Sub Temp(ByRef wkbBook As Workbook)
wkbBook.Activate
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Nathaniel Tigere" wrote in message
...
I need some assistance with my programm below . I am
getting run time error 9 at the indicated position


Public f_name as variant

Public Sub Main()

f_name = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If f_name < False Then
MsgBox "Open " & f_name
End If


Workbooks.OpenText Filename:=f_name, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
2), Array(17, 2), Array(30, 2), Array(39, 2))
.
.

Workbooks.Open
Filename:="c:\switch_makros\Switch_Temp.xls"
Sheets("Sheet1").Select
.
.
.
Call Temp

End sub


Public Sub Temp()
.
.
Windows(f_name).Activate ' I get run time error 9 here
.
.
end sub

f_name contains the whole path and filename, not just the
filename. You need to strip the path using something like
this:

Do While InStr(f_name, "\")
f_name = Right(f_name, Len(f_name) - InStr
(f_name, "\"))
Loop


-----Original Message-----
I need some assistance with my programm below . I am
getting run time error 9 at the indicated position


Public f_name as variant

Public Sub Main()

f_name = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If f_name < False Then
MsgBox "Open " & f_name
End If


Workbooks.OpenText Filename:=f_name, Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
2), Array(17, 2), Array(30, 2), Array(39, 2))
..
..

Workbooks.Open
Filename:="c:\switch_makros\Switch_Temp.xls"
Sheets("Sheet1").Select
..
..
..
Call Temp

End sub


Public Sub Temp()
..
..
Windows(f_name).Activate ' I get run time error 9 here
..
..
end sub
.