I would like to know if it is possible to open an excel workbook from
VBA. It would also be useful to close it too.

If anyone can help that is great. The path will always be the same.

Trev.


--
ttomlinson
------------------------------------------------------------------------
ttomlinson's Profile: http://www.excelforum.com/member.php...o&userid=23839
View this thread: http://www.excelforum.com/showthread...hreadid=374909

'opening workbook
Workbooks.Open Filename:="c:\book1.xls"

'your code

'your code


'below code for saving and closing the workbook
Workbooks("book1.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=374909

Just use:

Workbooks("B.xls").Close

--

Vasant

"ttomlinson" wrote
in message ...

I have managed to open the workbook but not close it

to open I am using the code

Application.Workbooks.Open ("E:\Documents and
Settings\Trevor\Desktop\B.xls")

This works fine. How do I close though I have tried

Application.Workbooks("E:\Documents and
Settings\Trevor\Desktop\B.xls").Close

and

Application.Workbooks.Close("E:\Documents and
Settings\Trevor\Desktop\B.xls")

neither work.

Where have I gone wrong there?

Thanks
Trev


--
ttomlinson
------------------------------------------------------------------------
ttomlinson's Profile:
http://www.excelforum.com/member.php...o&userid=23839
View this thread: http://www.excelforum.com/showthread...hreadid=374909

I have changed the code since I last posted. I am using a concatenate
filename and it works in the open code which I have printed below.

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks.Open (Path & Name & "Archive.xls")

What I need to be able to do is to close the book and thats what I can
do.

Thanks
Tre

--
ttomlinso
-----------------------------------------------------------------------
ttomlinson's Profile: http://www.excelforum.com/member.php...fo&userid=2383
View this thread: http://www.excelforum.com/showthread.php?threadid=37490

Workbooks("Archive.xls").save
Workbooks("Archive.xls").Close


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=374909

Thanks analsolipuram,

I have tried using the full name of the workbook and it does work a
you have said in your example.

I am trying to specify the path by specifying the same path as th
current workbook is that of the other open workbook to close too.
Which it will be in the way I have my workbook set up.

I have tried the code below

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks(Path & Name & "Archive.xls").Save
Application.Workbooks(Path & Name & "Archive.xls").Close

and it still doesnt work!!

It works to open the workbook but not to close.

Just so you can compare this is what the code that works I am using t
open the workbook looks like.

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks.Open (Path & Name & "Archive.xls")

Thanks again I appreciate your input. I have been down every route t
test it as I can It workis in the open code but not the close!

Ro

--
ttomlinso
-----------------------------------------------------------------------
ttomlinson's Profile: http://www.excelforum.com/member.php...fo&userid=2383
View this thread: http://www.excelforum.com/showthread.php?threadid=37490

try this code

dim file_name as variant

Application.Workbooks.Open ("E:\Documents and
Settings\Trevor\Desktop\B.xls")
file_name=activeworkbook.name

'
'
'
'
workbooks(file_name).save
workbooks(file_name).close


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=374909

Application.Workbooks.Open (Path & Name & "Archive.xls")

Workbooks(Name & "Archive.xls").Close SaveChanges:=True

--
Regards,
Tom Ogilvy


"ttomlinson" wrote
in message ...

I have changed the code since I last posted. I am using a concatenated
filename and it works in the open code which I have printed below.

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks.Open (Path & Name & "Archive.xls")

What I need to be able to do is to close the book and thats what I cant
do.

Thanks
Trev


--
ttomlinson
------------------------------------------------------------------------
ttomlinson's Profile:
http://www.excelforum.com/member.php...o&userid=23839
View this thread: http://www.excelforum.com/showthread...hreadid=374909

ttomlinson wrote:
Thanks analsolipuram,

I have tried using the full name of the workbook and it does work as
you have said in your example.

I am trying to specify the path by specifying the same path as the
current workbook is that of the other open workbook to close too.
Which it will be in the way I have my workbook set up.

I have tried the code below

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks(Path & Name & "Archive.xls").Save
Application.Workbooks(Path & Name & "Archive.xls").Close

and it still doesnt work!!


You must activate workbook and then use .save .close
Workbooks("book1.xls").Activate

It works to open the workbook but not to close.

Just so you can compare this is what the code that works I am using to
open the workbook looks like.

Path = ThisWorkbook.Path & "\"
Name = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

Application.Workbooks.Open (Path & Name & "Archive.xls")

Thanks again I appreciate your input. I have been down every route to
test it as I can It workis in the open code but not the close!

Rob