VBA to save newly created workbook failing - creates a "file" document

[LawsH]

Hi all

I have been using this VBA for a few months now to create a new folder (if needed) and save my new file however today it's saving the document as "file" and not an Excel file. I have tried to tweak the FileFormat but to no avail. Can anyone help please?

thanks

Dim strGenericFilePath As String: strGenericFilePath = "HIDDEN FOR PRIVACY"
Dim strYear As String: strYear = Year(Date) & "\"
Dim strMonth As String: strMonth = Format(Date, "mm.Mmmm") & "\"
DateStamp = Format(Date, "yy.mm.dd")
Dim strFileName As String: strFileName = (DateStamp & " - " & Range("A4").Value & " - " & ws.Name)

' Check for year folder and create if needed
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
' Check for month folder and create if needed
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
' Save File
ActiveWorkbook.SaveAs Filename:= _
strGenericFilePath & strYear & strMonth & strDay & strFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=True

 

[Sunjinsak]

Hi LawsH,

Without running your code to check, I can already see a potential issue - you're using strDay as part your SaveAs filename, but you've not declared or assigned it anywhere. I'd say that's the most likely cause of your issue, though I note you mention the code has previously been working so I'm not sure what's going on there.

 

[Sunjinsak]

In fact, looking at it further, you haven't declared DateStamp (though you've assigned it) and you haven't created the WorkSheet Object you're invoking with ws.Name. Given you said it has previously been working I'm guessing we're missing some of the code here. Post all of the code because it's going to be hard to help if we've only got a snippet of it.