CODEWITHSUNDEEP
excelvba
orange123
orange123

Reputation: 75

VBA - Creating a variable workbook name & saving it to the desktop

in my code I want to prompt the user for a custom workbook name and then saving said workbook on the desktop. For some reason I am getting a Run time error 9 - Subscript out of range when I try to set my workbook to the FilePath. I did some sleuthing around on other posts and I am unsure why I am still getting the error. Is there a better way to do it than mine/ where is my mistake?

    Dim WB As Workbook
    Dim WS As Worksheet
    Dim WorkbookName As String
    Dim FilePath As String
    WorkbookName = InputBox("What Do you Want to Name the New Workbook?")
    FilePath = "C:\Users\JoeK\Desktop\" & WorkbookName & ".xlsx"

'error is at the line below

    Set WB = Workbooks(FilePath)
    Set WS = Sheets("Sheet1")

Upvotes: 0

Views: 791

Answers (1)

Vityata
Vityata

Reputation: 43575

Sub CreateEmptyWorkbook()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim workbookName As String
    Dim filePath As String
    workbookName = "test"
    filePath = GetDesktopPath & workbookName & ".xlsx"
    
    Set wb = Workbooks.Add
    wb.SaveAs filePath
    wb.Close False

End Sub

Public Function GetDesktopPath() As String
    GetDesktopPath = CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
End Function
  • Set wb = Workbooks.Add - adds the workbook, thus it is empty;
  • wb.SaveAs filePath - saves it to the filePath, which is the one on the Desktop;
  • wb.Close False - closing it is needed as well, the False argument is for saving changes. As far as nothing is done in the workbook, this arg it could be True as well;

Upvotes: 1

Related Questions