CODEWITHSUNDEEP
stringvbaexcel
GT.
GT.

Reputation: 792

Unable to assign String from variable as sheet name

Basically, I'm trying to create a new sheet in my current workbook that has the same name as the file I select.

Sub ImportReport()
    '(clickable button)

    'prompt to select file, get file name (of Report)
    Dim reportFullName As String
    reportFullName = PromptGetReportFullName()

    Dim reportName As String
    'Grabs the excel file name from the full directory path
    reportName = Mid(reportFullName, InStrRev(reportFullName, "\") + 1, InStrRev(reportFullName, ".") - (InStrRev(reportFullName, "\") + 1))

    'add Sheet after last Sheet with report name
    Call AddReportSheet(reportName)

End Sub

Private Function PromptGetReportFullName() As String

    Dim startingFileDirectory As String
    startingFileDirectory = Range("A6").Text
    ChDrive FileDirectory

    PromptGetReportFullName = Application.GetOpenFilename(, , "Report File")

End Function

Private Sub AddReportSheet(sheetName As String)
    With ActiveWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).name = sheetName
    End With
End Sub

The weird part is calling MsgBox(reportName) in Sub ImportReport() works fine and gives me the file name I want, but assigning the sheet name to reportName in Private Sub AddReportSheet() fails to create a new sheet named with the reportName string.

If I replace reportName in AddReportSheet() with a random string like "test", the program succeeds in creating a new sheet named "test".

I'm guessing that reportName is just not stored as a string? Although I'm not sure why that would be, and I have no clue how to fix it.

Upvotes: 0

Views: 695

Answers (2)

Carl Colijn
Carl Colijn

Reputation: 1607

Maybe your file name contains characters that are illegal in sheet names, or maybe it is too long? Excel has the following restrictions:

  • no use of ":\/?*[]"
  • not longer than 32 characters

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3784

You have declared sheetName as variable name in your Sub AddReportSheet, but you are trying to use reportName instead of sheetName. Since reportName variable is empty in your Sub AddReportSheet, it is not working. So change your variable name like this: 

Private Sub AddReportSheet(sheetName As String)
    With ActiveWorkbook
        'REPLACED reportName WITH sheetName
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).name = sheetName
    End With
End Sub

Upvotes: 2

Related Questions