# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Open a closed workbook with VBA

## ttomlinson

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

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

----------


## anilsolipuram

'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

----------


## Vasant Nanavati

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
>

----------


## ttomlinson

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

----------


## anilsolipuram

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

----------


## ttomlinson

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

----------


## anilsolipuram

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

----------


## Tom Ogilvy

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
>

----------


## VK

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

----------


## davec8723

Is there a way to use VBA to open a closed workbook, perform action on it, and then close it? The catch is that the file will change every month, so can VBA prompt the user to enter the title or select from a browse menu the title of the file, and then once the user selects the file the rest of the VBA will take action? Any help will be apprecaited, thanks!

----------


## BAReese

> Is there a way to use VBA to open a closed workbook, perform action on it, and then close it? The catch is that the file will change every month, so can VBA prompt the user to enter the title or select from a browse menu the title of the file, and then once the user selects the file the rest of the VBA will take action? Any help will be apprecaited, thanks!



So the filename will change every month? Hopefully you've already found your answer, but if not then here's how I would let the user choose the correct file:

Sub OpenProcessCloseFile()
    Dim MonthlyWB As Variant
    Dim FileName As String

    MonthlyWB = Application.GetOpenFilename( _
        FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")

    Workbooks.Open MonthlyWB
    FileName = ActiveWorkbook.Name

    ' INSERT YOUR ADDITIONAL CODE HERE
    ' Note: to run a macro in the other file, use code like this
    ' Application.Run ("'" + FileName + "'!MyMacro")
    ' -- or Applicion.Run ("'My Workbook.xls'!MyMacro")

    Workbooks(FileName).Close
End Sub

----------


## girish1989

Hi thanks everyone,
Also could you pls help me in opening a excel workbook and copy the date and then close it...?

----------


## arlu1201

Girish,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## girish1989

Arlu201,

Oh okie. Apologies
But I thought this thread was related to my topic so had posted here.

----------


## Gard5096

Thanks for the knowledge!

----------


## thegdal

This closes without warnings or saving, works well for me:

Sub CloseBook()
'Closes the spread sheet without saving edits

    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

To save changes, change :
ActiveWorkbook.Close   ---->  ActiveWorkbook.Close savechanges:=True

----------


## kisanvikas2015

Hi all,

I need help in writing a code. 
I have a excel file & cell A1 contains the path of a file. My requirement is to open the workbook from specified path.

Regards,
Vikas

----------


## walruseggman

As demonstrated on page 1, the syntax would be:




```
Please Login or Register  to view this content.
```

----------


## protonLeah

@kisanvikas2015,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

_(link above in the menu bar)_

----------

