Error 400 (1004?) Saving in VBA

[LuminaryXion]

Good day!

I an not really a coder, but somewhat of a power user. I hope to be able to accomplish some fancy coding with a little help from generous geniuses like yourselves.

I'm coding a macro in VBA to save a copy of the Active Workbook with a new name based on some variables within the spreadsheet itself. I seem to be grabbing the variables correctly but when it gets to the saveas event, I get The above described Error 400 (Error 1004 in Debug mode).

Please find the problem code, with a bit of the surrounding code for reference below:

Public Sub SaveLessonRecordFileName()
' // Specifying my variables
  Dim NewFileName As Variant
    
'    On Error GoTo ErrorHandler
' // Grabbing some variables from the workbook to populate my file name
    NewFileName = ActiveWorkbook.Path & Application.PathSeparator & Range("E5").Value & " " & Range("E4").Value & " Attendance"


    ' // "Cancel" button case loop
    If NewFileName = False Then
    MsgBox "The save have been canceled."
    Exit Sub
    End If
' // Time to save the world! Or just my new workbook...
With ActiveWorkbook
    .SaveAs Filename:=NewFileName & ".xls", FileFormat:=xlNormal
End With
End Sub

Please note that I have commented out my error handler specifically so as to be able to see the error in debug mode. That's why that's there.

I've been wracking my brain all day and I can't figure out why it's not working. I'd appreciate any help you can provide.

Thank you.

 

[Firefly2012]

Hi

What's the value of NewFileName when you get this error?

What version of Excel are you using?

 

[LuminaryXion]

Thank you very much for volunteering to help with my issue!

In attempting to reproduce this error in a location where I would not be giving you any company proprietary information, I have found that it works perfectly fine in almost every instance, up until the moment it is in the actual folder I want it in. Is there a character limit? Here is a proprietary-blanked reproduction of the value giving me trouble:

\\xxxxx##\xxx##\XXX\★講師派遣★\★手配関連Instructor&Job Administration★\4. Evaluations and Records\6. は行\XXXX XXXXX XXXXXXXX XXXXXXXXX\2012.07\XXXXXXX XXXX\E - 24072501\Instructor Name Class Name - E Attendance.xls

If I put the file in the "2012.07\XXXXXXX XXXX\" folder instead of the "2012.07\XXXXXXX XXXX\E - 24072501\" folder, it works...

What is the limitation I am facing here? I have full read/write access to every folder from "xxx##" on.

Finally, I am using MS Excel 2000 (9.0.8961 SP-3). Thank you!

 

[Firefly2012]

I believe that Windows imposes around a 255 character limit on the filename&path so potentially if you are going over that then that could be causing your problem. If so, then you may need to rethink your file/folder names to reduce the length.

 

[LuminaryXion]

Thank you for your help. I fear that may be the problem as while an actual character count is only 205, I am willing to bet that all of the ***anese characters are in fact being converted to +3digit character codes, which triples the theoretical length of the path.

I recall that before I specified the path, any time I ran the macro it would save into "my documents". Is there some way to indicate the current folder without being so specific? If I didn't have to specify the whole network path, then this wouldn't be an issue.

Thank you again for your patient assistance.

 

[dandiemanne]

Hello! I recently ran into the same issue, I found a way to get around it is to add a ChDir line before the save as. This makes it so that you only have to put in the title name of the workbook.

ChDir ("C:Users\Desktop\Test")
ActiveWorkbook.SaveAs Filename:= "Test.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False