Im trying to open a workbook using a macro. I keep getting an error that the file cant be found
I have two files in the same directory
Questions.xl
Admin.xl
The user opens questions.xls. When they have completed entering data, the macro in questions.xls saves the file and copies one row into admin.xls. I cant specify the path because this application is designed to be saved by different people in different places so I dont know what the path will be. I thought by putting both files in the same directory, the macro would know where to find admin.xls. Heres my code

Sub Complete(

' Save the users results, copies the summary information to the admi
'tool, saves the admin tool and closes it. Then it shows the user thei
'result

ThisWorkbook.sav
Sheets("Name ").Selec
Rows("3:3").Selec
Selection.Cop
Workbooks.Open Filename:="admin.xls
Rows("1000:1000").Selec
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
Cells.Selec
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBotto
ActiveWorkbook.sav
ActiveWindow.Clos

The error message says admin.xls can not be found. Any suggestions

"doug h" wrote in message
...
I'm trying to open a workbook using a macro. I keep getting an error
that the file can't be found.
I have two files in the same directory:
Questions.xls
Admin.xls
The user opens questions.xls. When they have completed entering
data, the macro in questions.xls saves the file and copies one row
into admin.xls. I can't specify the path because this application is
designed to be saved by different people in different places so I don'
t know what the path will be. I thought by putting both files in the
same directory, the macro would know where to find admin.xls. Here's
my code:

Sub Complete()
'
' Save the users results, copies the summary information to the
admin
'tool, saves the admin tool and closes it. Then it shows the user
their
'results
'
ThisWorkbook.save
Sheets("Name ").Select
Rows("3:3").Select
Selection.Copy
Workbooks.Open Filename:="admin.xls"
Rows("1000:1000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWorkbook.save
ActiveWindow.Close

The error message says admin.xls can not be found. Any suggestions?




Doug,

You could try using ThisWorkbook.Path to get the path of Questions.xls
and then tack it onto the "\admin.xls":

Workbooks.Open Filename:=ThisWorkbook.Path & "\admin.xls"


Hope this helps,

Mike

Doug

Use: ThisWorkbook.Path to get the path of the first workbook
(Questions.xls)

Then use that to build the complete file name for the file you want to open

Workbooks.Open Filename:=ThisWorkbook.Path & "\admin.xls"

This obviously won't work if the files are not in the same folder

Regards

Trevor


"doug h" wrote in message
...
I'm trying to open a workbook using a macro. I keep getting an error that
the file can't be found.
I have two files in the same directory:
Questions.xls
Admin.xls
The user opens questions.xls. When they have completed entering data, the
macro in questions.xls saves the file and copies one row into admin.xls. I
can't specify the path because this application is designed to be saved by
different people in different places so I don't know what the path will be.
I thought by putting both files in the same directory, the macro would know
where to find admin.xls. Here's my code:

Sub Complete()
'
' Save the users results, copies the summary information to the admin
'tool, saves the admin tool and closes it. Then it shows the user their
'results
'
ThisWorkbook.save
Sheets("Name ").Select
Rows("3:3").Select
Selection.Copy
Workbooks.Open Filename:="admin.xls"
Rows("1000:1000").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWorkbook.save
ActiveWindow.Close

The error message says admin.xls can not be found. Any suggestions?