Create a copy of workbook with VBA

[andrewb90]

Hi all,

Basically, I need VBA to create a copy of my workbook (equivalent to press F12). The name would be the same with the added (view only) in the name, and the file path would be the same.

When I used the recorder I got this:

    ChDir "C:\Users\AB\Desktop"    ActiveWorkbook.SaveAs Filename:="C:\Users\AB\Desktop\Q_Stats(viewonly).xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I just need help making the filename match whatever computer is being used, and the filename to just adjust.

Any help would be greatly appreciated.

 

[MisterProzilla]

When I want to save a copy of a doc via VBA, I usually have a cell in that doc that retrieves the doc's folder path:

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

So let's say you've got that formula in A1, you can use the .value of that cell to build you target filepath for saving. Something like:

ChDir "C:\Users\AB\Desktop"    ActiveWorkbook.SaveAs Filename:= Sheet1.Range("A1").value & "Q_Stats(viewonly).xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[Fluff]

How about

   Dim Pth As String
   
   Pth = "C:\Users\" & Environ("Username") & "\Desktop\"
   ChDir Pth
   ActiveWorkbook.SaveAs fileName:=Pth & ActiveWorkbook.Name & " (viewonly).xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[Jon Peltier]

You don't need ChDir.

You can easily get the current path with ThisWorkbook.Path, and the whole path and filename with ThisWorkbook.FullName. A simple Replace statement can be used to modify the filename.

You can do SaveAs, which keeps the copy of the file in memory (the "viewonly" version), or SaveCopyAs, which keeps the original in memory. Which I choose depends on what I'm doing.

Dim sNewName As String
sNewname = Replace(ThisWorkbook.FullName, ".xlsm", "(viewonly).xlsm")
ThisWorkbook.SaveAs sNewName, xlOpenXMLWorkbookMacroEnabled

 

[andrewb90]

works beautifully. thank you!

 

[VBABEGINER]

Hi Jon Peltier,
How do i achieve this in my code.. What is Thisworkbook you are refering..?

Sub newWBcreate()
Dim Uname As String, path As String, NewName As String
Uname = Application.UserName
If myFileExists("\\inmum-i-fs4\group$\WNA Skyline\Common\" & Uname & ".xlsx") Then
Workbooks.Open Filename:=Uname & "." & "xlsx", UpdateLinks:=False
Worksheets(Uname).Select


Else


Workbooks.Open Filename:="Workbook1.xlsx", UpdateLinks:=False
NewName = Replace(ThisWorkbook.FullName, ".xlsm", "(viewonly).xlsx")
ThisWorkbook.SaveAs NewName, xlOpenXMLWorkbookMacroEnabled


Sheets("Sheet1").Select
ActiveSheet.Name = Uname
End If
End Sub

You don't need ChDir.

You can easily get the current path with ThisWorkbook.Path, and the whole path and filename with ThisWorkbook.FullName. A simple Replace statement can be used to modify the filename.

You can do SaveAs, which keeps the copy of the file in memory (the "viewonly" version), or SaveCopyAs, which keeps the original in memory. Which I choose depends on what I'm doing.

Dim sNewName As String
sNewname = Replace(ThisWorkbook.FullName, ".xlsm", "(viewonly).xlsm")
ThisWorkbook.SaveAs sNewName, xlOpenXMLWorkbookMacroEnabled

 

[VBABEGINER]

Just a small note - Other workbook copy Save as "Aplication.UserName"

 

[Jon Peltier]

ThisWorkbook is a reference to the workbook that contains the code which is running.

Select carefully how you refer t oyour workbooks:

To reference the workbook containing the code, use ThisWorkbook.
To reference the active workbook, use ActiveWorkbook.
Te reference an open workbook by name, use Workbooks("Workbook Name.xlsx").