LinkBack URL
About LinkBacks
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.
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.
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
'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
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
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
>
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
>
>
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:Originally Posted by davec8723
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
Last edited by BAReese; 10-19-2010 at 04:29 PM.
Hi thanks everyone,
Also could you pls help me in opening a excel workbook and copy the date and then close it...?
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.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Arlu201,
Oh okie. Apologies
But I thought this thread was related to my topic so had posted here.
Thanks for the knowledge!
"The only real restraint that exists is not believing that it can be done."
-Nick Gardone
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
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
As demonstrated on page 1, the syntax would be:
Please Login or Register to view this content.
@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)
Last edited by protonLeah; 06-05-2015 at 12:28 AM.
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks