vbe excel - WkBk SaveAs Error 1004 cannot access file

[devofish]

I get an error running a SaveAs routine where the message says it cannot access the file 'C:\Users\<user>\Documents\Root\Reports\057B2600'

I don't understand where the 057B2600 number is coming from at the end of the path name. Reports directory is where the file is to be saved as in. The directory path is correct and all the variables are declared.

Sub SaveReport()


     Dim user$, pName$, sPath$


    Set wb = Workbooks(1)
    wb.Activate
    Set ws = ThisWorkbook.Worksheets(1)
    ws.Activate
    
    user = Environ("username")
    pName = Range("F2").value
    sPath = "C:\Users\" & user & "\Documents\Tester\Reports\" & pName & ".xlsm"
        
    With ws
        Application.DisplayAlerts = False
        .SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        MsgBox "The " & pName & " project has been saved to your local Directory." & vbNewLine & "A shortcut to" & _
        "the report and the well folder can be accessed on your desktop.", vbInformation, pName & "Well File"
        Exit Sub
        Application.DisplayAlerts = True
    End With
End Sub

As a sidenote, the directory structure was created using a shell cmd routine, but that shouldn't make a difference.</user>

 

[Logit]

.
Not certain where the issue is arising from. Just created a version here, with modified path, and it functioned perfectly.

Option Explicit


Sub SaveReport()




     Dim user$, pName$, sPath$




    Set wb = Workbooks(1)
    wb.Activate
    Set ws = ThisWorkbook.Worksheets(1)
    ws.Activate
    
    user = Environ("username")
    pName = Range("F2").Value
    sPath = "C:\Users\" & user & "\Documents\" & pName & ".xlsm"
        
    With ws
        Application.DisplayAlerts = False
        .SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        MsgBox "The " & pName & " project has been saved to your local Directory." & vbNewLine & "A shortcut to" & _
        "the report and the well folder can be accessed on your desktop.", vbInformation, pName & "Well File"
        Exit Sub
        Application.DisplayAlerts = True
    End With
End Sub

 

[devofish]

Thank you Logit. After working on this all evening I see where my process was flawed. You're correct, this should work and it does once I was able to isolate my problem. I found that my WorkBook declaration was the problem.

Since I think I've discovered where the flaw in logic is, I'm not sure to propose another post or continue with this post.

pName is the WorkBook that is created (this is a template.xlsm), when the macro runs, so the original WorkBook name changes after the macro runs. My employees will be working with multiple workbooks open at any given time, so in my testing phase, I was having out of range errors pop up when I would run any given macro with a couple wbs open - even when I declared and set the worksheets. The only thing that seemed to solve the errors was declaring, setting and activating the workbook before any macro runs. It seems to run fine with ThisWorkbook.

Is it correct to say that going with declaring and setting ThisWorkbook.Worksheets("whatever name") is safe since if the macro is executed within the userform, the form intrinsically activates ThisWorkbook that said macro resides in? I did a google search and couldn't find a lot on using a range to define a workbook name, only the other way around, so I'm assuming this is the case.

 

[Logit]

.
If I correctly understand your explanation, it seems placing

pName = Range("F2").value

directly under your DIM statements should take care of the issue without
having to change anything else ?

 

[devofish]

Thanks for that. With that I've seemed to fix the issue.

 

[Logit]

.
Great ! Glad you have it working.

Cheers.