CODEWITHSUNDEEP
vbasave
Thompson Ho
Thompson Ho

Reputation: 71

prompt user open a workbook and save as a new workbook

try to prompt user to open a workbook, and then make change on it and save as a new workbook under the same path of user open workbook. At the same time, keep user open workbook not save/no change and close it.

Below is my code but fail - the new workbook is empty and the user open workbook are not closed.

Sub saveas()

R = Application.GetOpenFilename _
(Title:="Please choose file", _
FileFilter:="Excel Files *.xls*; *.csv (*.xls*; *.csv),")
Set extwbk = Workbooks.Open(R)
If R = False Then
MsgBox "No file selected. Please click run again and select file.", 
vbExclamation, "Sorry!"
Exit Sub
Else
End If

'a = extwbk.Worksheets.Count
'For i = 1 To a
'will input code there that make change on the open workbook 
'Next i
'After change process complete,  save the changed workbook as a new workbook in the 
same path of the  openworkbook and then keep the origin openworkbook 
unchange and then close it



Set extwbk = ActiveWorkbook
ActiveWorkbook.Sheets.Copy
ActiveWorkbook.saveas Filename:=extwbk.Path & "\log.xlsx"
   ActiveWorkbook.Close savechanges:=True
extwbk.Close savechanges:=False


End Sub

Upvotes: 0

Views: 763

Answers (1)

dbmitch
dbmitch

Reputation: 5386

I think you need to change your last line from 

ActiveWorkbook.Close savechanges:=False

To 

ActiveWorkbook.Close savechanges:=True
extwbk.Close savechanges:=False

Otherwise I think when you save the new workbook using ActiveWorkbook.saveas - it immediately becomes the active workbook

Upvotes: 1

Related Questions