Run time error below code

Question

0.00/5 (No votes)

See more:

VB

Sub InvoiceReport()

    Dim myFile As String, lastRow As Long

    myFile = "C:\Users\iaquino\Documents\Invoices\" & _ 
        Sheets("Invoice").Range("A10") & "_" & _
        Sheets("Invoice").Range("F4") & _
        Format(Now(), "yyy-mm-dd") & ".pdf"

    lastRow = Sheets("Invoice File").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

    'Transfer data to Invoice File
    Sheets("Invoice File").Cells(lastRow, 1) = Sheets("Invoice").Range("A10")
    Sheets("Invoice File").Cells(lastRow, 2) = Sheets("Invoice").Range("F4")
    Sheets("Invoice File").Cells(lastRow, 3) = Sheets("Invoice").Range("F28")
    Sheets("Invoice File").Cells(lastRow, 4) = Now
    Sheets("Invoice File").Hyperlinks.Add Anchor:=Sheets("Invoice File").Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile

    'Create invoice in PDF format
    Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile

    Application.DisplayAlerts = False

    'create invoice in XLSX format
    ActiveWorkbook.SaveAs "C:\Users\iaquino\Documents\invoices\" & _
        Sheets("Sheet1").Range("A10") & "_" & _
        Sheets("Invoice").Range("F4") & "_" & _
        Format(Now(), "yyy-mm-dd") & "xlsx", FileFormat:=51

    Application.DisplayAlerts = True

End Sub

Error debug:-
Run time error '5'

Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = False

What I have tried:

Plz give urgent solution this problem.

Posted 14-Sep-17 17:58pm

Member 13410448

Updated 15-Sep-17 0:03am

Graeme_Grant

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Answer 1 · 2017-09-14T18:13:00

A quick Google Search of "Excel error 5" returns this: Invalid procedure call or argument (Error 5)[^]

So you now need to figure out where. To do this, set a breakpoint on the first line and step through until you hit the line with the error. Now you know where the error is and now you are ready to fix it.

If in fact it is the line that you have identified, then you need to check the function requirements: ExportAsFixedFormat Method [Excel 2007 Developer Reference][^]. At a guess, there may be a problem with the path &/or filename. Does the path exist? Is the myFile a valid path+filename?

Patrice T · Answer 2 · 2017-09-14T22:25:00

Solution 2

Not your question, but this line

VB

lastRow = Sheets("Invoice File").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

can be simplified to

VB

lastRow = Sheets("Invoice File").UsedRange.RowS.Count + 1

Posted 14-Sep-17 22:25pm

Patrice T

Jochen Arndt · Answer 3 · 2017-09-15T00:03:00

If it occurs when calling ExportAsFixedFormat, there may be these reasons :

The PDF add-in is not installed
The path C:\Users\iaquino\Documents\Invoices\ does not exist
You have insufficient privileges to write the above the path (not executed as user iaquino)
The created file name (the cells A10 and/or F4) contain reserved characters; see Naming Files, Paths, and Namespaces (Windows)[^]

There may be different error codes for some of the above reasons but you should check them all.

Run time error below code

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0