We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
2 workbooks but vba routine to keep current workbook open but close saveas copy
Posted on 2014-11-25
I have a vba routine that is not working properly.
I have a current workbook: I need to keep open
I have a second workbook i'm opening...getting additional string for saveas and then I just need to close the saveas workbook.
Current workbook "MyExcel1.xlsx"
In the code below i'm opening another workbook , doing a SaveAs and Then closing it.
but keeping my current workbook open.
THIS CODE IS CLOSING MY CURRENT WORKBOOK ALSO ?
Thanks
fordraiders
I have a current workbook: I need to keep open
I have a second workbook i'm opening...getting additional string for saveas and then I just need to close the saveas workbook.
Current workbook "MyExcel1.xlsx"
In the code below i'm opening another workbook , doing a SaveAs and Then closing it.
but keeping my current workbook open.
THIS CODE IS CLOSING MY CURRENT WORKBOOK ALSO ?
Public Sub CmeSaveAs()
Dim oldwkbk As String
Dim excelwrk As Excel.Application
Dim newdate As String
Dim sInput As String
Set excelwrk = Excel.Application
excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_Open\Export_Cme_Template.xlsx"
excelwrk.Visible = False
sInput = InputBox("Enter File name here")
' saveas and then close please
excelwrk.ActiveWorkbook.SaveAs "C:\Program Files\Cme\Cme_Export\" & sInput & "_" & newdate
excelwrk.ActiveWorkbook.Close
End Sub
Thanks
fordraiders
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
9 Comments
Active today
change the line
also remove the line activeworkbook.clsoe
ActiveWorkbook.SaveAs
ActiveWorkbook.SaveCopyAs
also remove the line activeworkbook.clsoe
Active 4 days ago
You should use these to variables:
Dim currentwb As Workbook
Dim NewBook As Workbook
Assign currentwb to Current workbook "MyExcel1.xlsx"
then open the other one using
Set newbook = excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_O
Now you have these seperated and closing and opening and saving is much more clear!
In this case, NewBook.SaveAs and NewBook.Close
As you can see, you fixed currentwb and newbook. With Activeworkbook, it keeps changing almost out of your control (while possible, it's much harder to keep track of).
Dim currentwb As Workbook
Dim NewBook As Workbook
Assign currentwb to Current workbook "MyExcel1.xlsx"
then open the other one using
Set newbook = excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_O
Now you have these seperated and closing and opening and saving is much more clear!
In this case, NewBook.SaveAs and NewBook.Close
As you can see, you fixed currentwb and newbook. With Activeworkbook, it keeps changing almost out of your control (while possible, it's much harder to keep track of).
kimcomputer,
error on this line ?
Set newbook = excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_O
error on this line ?
Set newbook = excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_O
Active 4 days ago
Set newbook = excelwrk.Workbooks.Open("C
ok,
profesor jim,
I commented out
' excelwrk.Visible = False
'added
excelwrk.ActiveWorkbook.Cl
now it works.?
profesor jim,
I commented out
' excelwrk.Visible = False
'added
excelwrk.ActiveWorkbook.Cl
now it works.?
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Permutacion of 2 numbers COUNT | 8 | 21 | |
| Sort columns by the column header Excel 2016 VBA | 5 | 13 | |
| Looping through files in a folder VBA | 3 | 15 | |
| Need to count the pair | 35 | 23 |
Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook?
David Miller (dlmille)
Intro
Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month3 days, 7 hours left to enroll
751 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.