Reference drive letter that can change

[Rob]

Hi, I'm using the following code to import a file SalesInfo but have run
into an issue where the file used to run this code resides in a different
directory EG c:\work\salesmodel.xls is where the excel file resides and
c:\work\sales\ folder is where the data file is stored.

I think I need to change my first row of code to included the folder Sales

ChDir This Workbook.Path
to
ChDir This Workbook.Path+"/Sales" [this doesn't work]

Sub OpenText()
ChDir ThisWorkbook.Path
Workbooks.OpenText Filename:="SalesInfo.prn", Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(8,
1), Array( _
17, 3), Array(25, 1), Array(36, 1), Array(46, 1), Array(56, 9),
Array(61, 9))
End Sub

Thanks, Rob

 

[Dave Peterson]

If it's always under the \sales folder under the path that contains the workbook
with the macro:

Sub OpenText()
Workbooks.OpenText Filename:=thisworkbook.path & "\sales\SalesInfo.prn",
.....

===
Ps. ThisWorkbook is one word.

ChDir ThisWorkbook.Path
or
ChDir ThisWorkbook.Path & "\Sales"

(+ is usually used for adding numbers. & is used for concatenating text.)

Hi, I'm using the following code to import a file SalesInfo but have run
into an issue where the file used to run this code resides in a different
directory EG c:\work\salesmodel.xls is where the excel file resides and
c:\work\sales\ folder is where the data file is stored.

I think I need to change my first row of code to included the folder Sales

ChDir This Workbook.Path
to
ChDir This Workbook.Path+"/Sales" [this doesn't work]

Sub OpenText()
ChDir ThisWorkbook.Path
Workbooks.OpenText Filename:="SalesInfo.prn", Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(8,
1), Array( _
17, 3), Array(25, 1), Array(36, 1), Array(46, 1), Array(56, 9),
Array(61, 9))
End Sub

Thanks, Rob

 

[Rob]

Thanks Dave, just the job. Rob

If it's always under the \sales folder under the path that contains the
workbook
with the macro:

Sub OpenText()
Workbooks.OpenText Filename:=thisworkbook.path &
"\sales\SalesInfo.prn",
....

===
Ps. ThisWorkbook is one word.

ChDir ThisWorkbook.Path
or
ChDir ThisWorkbook.Path & "\Sales"

(+ is usually used for adding numbers. & is used for concatenating text.)

Hi, I'm using the following code to import a file SalesInfo but have run
into an issue where the file used to run this code resides in a different
directory EG c:\work\salesmodel.xls is where the excel file resides and
c:\work\sales\ folder is where the data file is stored.

I think I need to change my first row of code to included the folder
Sales

ChDir This Workbook.Path
to
ChDir This Workbook.Path+"/Sales" [this doesn't work]

Sub OpenText()
ChDir ThisWorkbook.Path
Workbooks.OpenText Filename:="SalesInfo.prn", Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2),
Array(8,
1), Array( _
17, 3), Array(25, 1), Array(36, 1), Array(46, 1), Array(56, 9),
Array(61, 9))
End Sub

Thanks, Rob