How to create new workbook(blank) in excel and prompt user for name and file location

Question

Suppose I want a create a new excel workbook(blank) using macros and the user should be prompted to enter the file name and location, the new file should not overwrite some existing file with the same name that user has entered

The name of file that user has entered should be saved in some cell of the existing sheet in which I am running the macros

Till now I have been using this

Workbooks.Add
Dim file_name As Variant

ActiveWorkbook.file_name = _
Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xls), *.xls")

I don't know how to copy the name to the cell

I am getting error message: Run-time error'438' with the above code

I don't know how to fix it, I am a beginner, some help please

Pawel Borodan Pawel Borodan · Accepted Answer · 2015-05-05T11:26:21

Option Explicit
Public Function wbNewWB(rngCelltoWriteTheFilenameTo As Range) As Workbook
    Dim wbNew As Workbook
    Dim vFilename As Variant
    Dim bFileexists As Boolean

    Set wbNew = Workbooks.Add
    bFileexists = True
    Do
        vFilename = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel file (*.xls), *.xls")
        bFileexists = Dir(vFilename) <> ""
    Loop Until vFilename <> False And bFileexists = False
    wbNew.SaveAs Filename:=vFilename
    rngCelltoWriteTheFilenameTo.Value = vFilename
    Set wbNewWB = wbNew
End Function

Sub test()
    Dim wbMyWB

    Set wbMyWB = wbNewWB(ThisWorkbook.Worksheets(1).Range("A1"))
    Debug.Print "Finished"
End Sub

As far as I understood your requirements correctly, this might be a solution: the function wbNewWB adds a new workbook, then asks for a file name and checks whether a file with this file name already exists. If a filename is provided and does not already exist, the workbook is saved under the provided file name and the file name is stored as a value in the cell you determine as an input parameter to the function. Finally the new workbook is provided as the return value of the function.

To illustrate the usage of the function I further added a sub, where the function is called.

To use this code, you should simply add a module to your existing project and copy & paste the code.

How to create new workbook(blank) in excel and prompt user for name and file location

1 Answers