asked on 11/25/2014

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

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

Microsoft Excel

Last Comment

Kimputer

11/26/2014

ASKER CERTIFIED SOLUTION

Professor J

11/25/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Kimputer

11/25/2014

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Fordraiders

11/25/2014

ASKER

my current workbook is not visible ? but it is in processes tab? showing as open ?

Fordraiders

11/25/2014

ASKER

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

Fordraiders

11/25/2014

ASKER

professorjim,
Current workbook. is still closing..

Kimputer

11/25/2014

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

Fordraiders

11/25/2014

ASKER

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

11/25/2014

ASKER

Thanks to both !

Kimputer

11/26/2014

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