run time error '1004'

  • Thread starter Thread starter Grant
  • Start date Start date
G

Grant

I use the piece of code below in 3 macros in three different workbooks to
open files. In one of the macros it will not work. I get the following
error:
run time error '1004'
pobcinv.xls could not be found.....

It's looking for my filename with an xls entension. Can it I make it not
look for the file name with an extension. It must be something unique to
this one workbook since this is the only one that errors. If there is a
solution I'll add it to the othere macro's.

Thanks


Sub Open_File()

Workbooks.OpenText Filename:= _
"\\123.456.78.9\windows\inventory\DailyPackingLists\Vendor1\pobcinv" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1),
_
TrailingMinusNumbers:=True
 
Perhaps something like:

Sub Open_File()
Dim sPath as String, sName as String, sFile as String

sPath = "\\123.456.78.9\windows\inventory\DailyPackingLists\Vendor1\"
sName = "pobcinv"

sFile = Dir(sPath & sName & ".*")
if sFile <> "" then

Workbooks.OpenText Filename:= _
spath & sFile, _
Origin:=437, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
 
Back
Top