[SOLVED]Run-time Error'1004'

**Rober** · 02-09-2012, 07:15 PM

Hello,
I have this script to save a file from a sheet.
I have a button and when I press it I get the filename correctly but when I press "SAVE" everything stops with this message:
Run-Time error '1004':
Method 'SaveAs' of object '_Workboob' failed.
I have no idea what to do!!!!

Any advice?

Thanks
R

In debug I get yellow at:

               wb.SaveAs Filename:=FileSaveName, _
 ---->                                  FileFormat:=NewFileFormat


Sub SaveAsNewFile()
    Dim wb As Workbook
    Dim NewFileName As String
    Dim NewFileFilter As String
    Dim myTitle As String
    Dim FileSaveName As Variant
    Dim NewFileFormat As Long
    
Set wb = ThisWorkbook
 NewFileName = wb.Sheets("Invoice").Range("B8").Value & "_" & wb.Sheets("Invoice").Range("A16").Value & "#" & wb.Sheets("Invoice").Range("b13").Value & wb.Sheets("Invoice").Range("C13").Value & "_" & wb.Sheets("Invoice").Range("f16").Value & ".xlsm"
      NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
myTitle = "Navigate to the required folder"
FileSaveName = Application.GetSaveAsFilename _
            (InitialFileName:=NewFileName, _
             FileFilter:=NewFileFilter, _
             Title:=myTitle)
    If Not FileSaveName = False Then
      wb.SaveAs Filename:=FileSaveName, _
                    FileFormat:=NewFileFormat
    Else
      MsgBox "File NOT Saved. User cancelled the Save."
    End If

End Sub

**Richard Buttrey** · 02-09-2012, 07:20 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

With 15 posts you should by now realise this requirement.

Regards

**rscsmith** · 02-09-2012, 07:28 PM

Rober

Please edit the tags (add [ code ] and [ / code ] no spaces).

To answer your question - you haven't set NewFileFormat to anything (i.e. it is Empty). Either delete the statement (so Excel defaults to xlsx) or set it to something.

Cheers, Rob.

**Rober** · 02-09-2012, 07:31 PM

sorry sorry sorry sorry...my ignorance...I hope I did it right

**Rober** · 02-09-2012, 07:33 PM

Yes, I just cecked and I was missing that.

However how can I specify that I want to make it as pdf? I have pdf acrobat as 'printer', but the default printer is my normal printer.....
instead of xl?
R

Originally Posted by rscsmith

Rober

Please edit the tags (add [ code ] and [ / code ] no spaces).

To answer your question - you haven't set NewFileFormat to anything (i.e. it is Empty). Either delete the statement (so Excel defaults to xlsx) or set it to something.

Cheers, Rob.

**rscsmith** · 02-10-2012, 12:04 AM

After a bit of Googling...


Sub Print_PDF()
     
    'Check Reference to Acrobat Distiller in the Visual Basic Editor, Tools, References.
            
    Dim PSFile As String, PDFFile As String, pdPDF As PdfDistiller, logFile As String
    PSFile = "c:\Test.ps"
    PDFFile = "c:\Test.pdf"
    logFile = "c:\Test.log"
    Set pdPDF = New PdfDistiller

'Create PS File.
    ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
    PrintToFile:=True, Collate:=True, PrToFilename:=PSFile

    'Convert the postscript file to .pdf
    pdPDF.FileToPDF PSFile, PDFFile, ""

'Delete PS File
Kill PSFile
Kill logFile
End Sub

Note the requirement to reference Adobe Distiller in the VBE, under Tools, References.

Cheers, Rob.