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!
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 today
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).
Active 6 days ago
my current workbook is not visible ? but it is in processes tab? showing as open ?
Active 6 days ago
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 today
Set newbook = excelwrk.Workbooks.Open("C
Active 6 days ago
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
Ready to get started with anonymous questions today? It's easy! Learn more.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month11 days, 13 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.