Open a closed workbook with VBA

Just use:

Workbooks("B.xls").Close

--

Vasant

"ttomlinson" <ttomlinson.1psweb_1117382705.0387@excelforum-nospam.com> wrote
in message news:ttomlinson.1psweb_1117382705.0387@excelforum-nospam.com...
>
> 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 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

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

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!!

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

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

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
>
>

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

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

--
Regards,
Tom Ogilvy


"ttomlinson" <ttomlinson.1pt4qc_1117393503.1484@excelforum-nospam.com> wrote
in message news:ttomlinson.1pt4qc_1117393503.1484@excelforum-nospam.com...
>
> 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
>