2 workbooks but vba routine to keep current workbook open but close saveas copy

Fordraiders
Fordraiders used Ask the Experts™
on
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 ?


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

Open in new window



Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Microsoft Excel Expert
Top Expert 2014
Commented:
change the line 

ActiveWorkbook.SaveAs

Open in new window

 to   
ActiveWorkbook.SaveCopyAs

Open in new window


also remove the line   activeworkbook.clsoe
KimputerIT Manager
Commented:
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_Open\Export_Cme_Template.xlsx"

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).
Fordraiders

Author

Commented:
my current workbook is not visible ?  but it is in processes tab? showing as open ?
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Learn More!
Fordraiders

Author

Commented:
kimcomputer,
error on this line ?
Set newbook = excelwrk.Workbooks.Open "C:\Program Files\Cme\Template_DoNot_Open\Export_Cme_Template.xlsx"
Fordraiders

Author

Commented:
professorjim,
Current workbook. is still closing..
KimputerIT Manager

Commented:
Set newbook = excelwrk.Workbooks.Open("C:\Program Files\Cme\Template_DoNot_Open\Export_Cme_Template.xlsx")
Fordraiders

Author

Commented:
ok,

profesor jim,

I commented out
'  excelwrk.Visible = False

'added
    excelwrk.ActiveWorkbook.Close "C:\Program Files\Cme\Cme_Export\" & sInput & "_" & newdate



now it works.?
Fordraiders

Author

Commented:
Thanks to both !
KimputerIT Manager

Commented:
Thanks for the points.  Though a bit sad you didn't apply the code, as not only do you make less mistakes (using clearly defined workbooks as opposed to activeworkbook), it's also easier to read the code much later when you forgot about it (or someone else reads it).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial