CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 15061

Excel Macro not outputting (save as) file "Method 'SaveAs' of object '_Workbook' failed"

I have a small macro that outputs the information from a tab, however it is not saving the outputted text file. when it runs the debug it highlights a line but i cant see anything wrong with it.

This is the error line in debug:

ActiveWorkbook.SaveAs Filename:=pathname, FileFormat:= _
    xlTextMSDOS, CreateBackup:=False

The Error Message:

Method 'SaveAs' of object '_Workbook' failed

This is the whole code

Sub Savetxt(pathname, sheetname)
    Sheets("Output").Range("A1:Z99999").ClearContents
    Sheets(sheetname).Select
    Range(ActiveSheet.ListObjects(1) & "[Final output for text file]").Copy
    Sheets("Output").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Rows("1:1").Select
    'Application.CutCopyMode = False
    'Selection.Delete Shift:=xlUp
    BName = ActiveWorkbook.FullName
    ActiveWorkbook.VBProject.VBComponents("module1").Export ("' & BName.Value &'")
    Sheets(Array("Output")).Copy

    Application.VBE.ActiveVBProject.VBComponents.Import ("' & BName.Value &'")
 ActiveWorkbook.SaveAs Filename:=pathname, FileFormat:= _
        xlTextMSDOS, CreateBackup:=False

    ' ActiveWorkbook.SaveAs Filename:=Range("savefile").Value _
        , FileFormat:=xlTextMSDOS, CreateBackup:=False
'
    ActiveWindow.Close

End Sub

Upvotes: 0

Views: 652

Answers (1)

Matt
Matt

Reputation: 15061

pathname wasn't defined correctly in the tab so it was trying to save the document in a location that doesn't exist.

Upvotes: 1

Related Questions