Save .xlsx files via excel vba, without opening saved file

3
votes

I'm making excel vba code that makes outlook message and attach excel file itself(xlsx) for the message

below line is from my code.

ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Date & " " & "Position Report Ver.2.xlsx", FileFormat:=51

This code saves xlsx file to specific folder.

I want just saving copied xlsx file.

but my code automatically opens saved file(xlsx file.) right after saving. and original file is closed.

macro should run even after generating xlsx files because it should be attached to outlook message by vba macro.

but, because xlsx file is opened right after saving, I can't run vba code after saving.

How can I save from xlsm to xlsx without opening?

I searched stackoverflow but I can't find the solution. code won't work or can't save to xlsx(just xlsm copy)... I couldn't find perfect answer...

2
excelvba

2 Answers

5
votes

Please give this a try...

Remember to check the filename as I used the Format function to remove the invalid character from the filename.

ThisWorkbook.Sheets.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Date, "mm.dd.yyyy") & " " & "Position Report Ver.2.xlsx", FileFormat:=51
ActiveWorkbook.Close
0
votes

@sktneer, this worked better. ThisWorkbook did not copy the sheets with data, but only opened a blank workbook. But ActiveWorkbook did the job.

ActiveWorkbook.Sheets.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Date, "mm.dd.yyyy") & " 
" & "Position Report Ver.2.xlsx", FileFormat:=51
ActiveWorkbook.Close