Run time error (Err=9) when saving file using saveas

**welchs101** · 03-23-2012, 10:27 PM

I have the following file designed to save a file. I have NO idea why this occurring but i get a run time err #9.

Any ideas?

Sub save_combined_file(fl_combined As String, ws_combined As String, _
                            pth_save As String, save_error As Boolean)

'Dim New_file_name1 As String
Dim fl_new_combined As String

'create file name with EXTENSION
fl_new_combined = "Combined_Comment_File" + ".xlsx"

'**Start:***save file
'turn display prompts off
Application.DisplayAlerts = False

'note:  pth_save should have "\" as the last character instring
New_file_name1 = pth_save & fl_new_combined

'if file to be saved is already open then close it
'If AlreadyOpen(fl_new_combined) Then Workbooks(fl_new_combined).Close


On Error Resume Next

save_error = False

Workbooks(fl_combined).Worksheets(1).Activate


'save file:  FileFormat = 51 = xlOpenXMLWorkbook which means the extension will be ".xlsx"
'Workbooks(fl_combined).SaveAs FileName:=New_file_name1, FileFormat:=51, CreateBackup:=False
ActiveWorkbook.SaveAs FileName:=New_file_name1, FileFormat:=51, CreateBackup:=False

'if there is an error saving the file then alert user and stop the program
If Err <> 0 Then

    MsgBox (Err.Number)
    
    save_error = True

    If Err.Number = 1004 And Len(New_file_name1) > max_file_name_path Then
        MsgBox ("Error saving file.   The file name + directory-path is larger than " & max_file_name_path & ".  Program will now end.")
    Else
        MsgBox ("There was an error saving the file.  The program will now stop.")
    End If

    Application.DisplayAlerts = False
    Err.Clear
    On Error GoTo 0
    'turn back on display prompts
    Application.DisplayAlerts = True
    Exit Sub

End If

On Error GoTo 0

'turn back on display prompts
Application.DisplayAlerts = True

'the consolidated file has a new file name
fl_combined = fl_new_combined

MsgBox ("jlkjl")

'close the combined file.
If AlreadyOpen(fl_combined) Then Workbooks(fl_combined).Close

End Sub

**welchs101** · 03-23-2012, 11:39 PM

i tried uploading my macro......but it would not load .....its not taht big only about 140k.......any ideas?

**welchs101** · 03-23-2012, 11:44 PM

i zipped the macro and included one input file.....just make a copy of the input file so that you have two input files total. running the macro should be self explanatory but just select the directory you put the two input files in and then select the directory where you want the output saved to.

i would really appreciate some help on this one.......i am really frustrated.

**welchs101** · 03-24-2012, 06:37 AM

does anyone have any ideas?

**welchs101** · 03-24-2012, 10:14 AM

ok, i figured it out and it was by luck that i did......i happen to review ALL the code again because i knew this should work and i noticed that in another procedure i was saving this same file.........so i commented out the following code from the the "process_all_files_in_directory" and it worked.

''After your have finished processing all the files in the directory save the file
''turn off display so you dont get prompts when you save file
'Application.DisplayAlerts = False
''Save file
'Workbooks(fl_combined).Save
''turn on display
'Application.DisplayAlerts = True

i dont have a clue why this matters......but then again there is ALOT that i dont know about vba / excel programming.