CODEWITHSUNDEEP
vbaexcel
paulinhax
paulinhax

Reputation: 602

vba using save as without opening the file

Is there any way to use ActiveWorkbook.SaveAs without opening the destination file?

My main Workbook called TEC ALOC EXT has a code to when it's saved it's generate another file without macros in a different path (using FileFormat:=51) called ALOCACAO TECNICOS. But this type of FileFormat also opens the destination file (ALOCACAO TECNICOS) at the end and closes the TEC ALOC EXT worksheet.

This it not good for me because I want to keep changing things on my TEC ALOC SHEET sheet.

Is there any way to just save the ALOCACAO TECNICOSwithout opening it?

Here is my code:

Sub CopiarNovaPlanilha()

Application.EnableEvents = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "I:\CGP\DEOPEX\01 - Supervisão\10 - Alocação das equipes\Consulta Alocados\ALOCACAO TECNICOS", FileFormat:=51

ActiveWorkbook.Sheets(1).Name = "FUNCIONARIOS"


Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

That is triggered by AfterSave event:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

If Success Then


Call CopiarNovaPlanilha


End If

End Sub

Upvotes: 3

Views: 13979

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

One way is this...

Sub CopiarNovaPlanilha()

Application.EnableEvents = False
Application.DisplayAlerts = False

ActiveWorkbook.Sheets.Copy
ActiveWorkbook.SaveAs "I:\CGP\DEOPEX\01 - Supervisão\10 - Alocação das equipes\Consulta Alocados\ALOCACAO TECNICOS", FileFormat:=51
ActiveWorkbook.Sheets(1).Name = "FUNCIONARIOS"


Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Upvotes: 3

wrslphil
wrslphil

Reputation: 258

Here's a website that should help you to accomplish what you need by using the FileSystemObject library. This will enable you to copy from one location to another without actually opening files as such.

The fist link will show you how to set up excel to use this.

http://www.wiseowl.co.uk/blog/s210/filesystemobject.htm

This link will describe in more detail about the FSO library and tell you a bit about the methods. Should give you enough to get started

http://www.wiseowl.co.uk/blog/s212/files-folders-vba.htm

Upvotes: 2

Related Questions