Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Unable to Export as PDF using Inv Num - CustName as filename

0

Hello All,

I am new to VBA. I have been following the TeachExcel course, and I have to admit that most of the time you may as well be speaking Swahili.

I am using MacOS and I am unable to save an invoice as a PDF using a concatenation of the invoice number and the customer name. I keep getting "Run Time Error 1004 Application - defined or Object - defined error. (this code I entered by hand)

I tried recording a macro and at the prompt I gave the file name as the invoice number and the customer name and it saved perfectly. I then altered the macro by incrementing the invoice number by one and I received the same error.

I am completely lost and would appreciate any help that anyone is able to provide.

Attached is a copy of the file and also a copy of the Save Invoice macro.

Thank you.

Sub SaveInvoice()
'
' SaveInvoice Macro
'
invno = Range("InvNumber")
custname = Range("Customer")
fname = invno & " - " & custname & ".pdf"

MsgBox (fname)

' Check if invoice has been previously saved
'
    ChDir "/Users/Guest/Documents/Projects/Georgina Invoice/"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=fname, _
    Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    opeanafterpublish:=False




'Set SavedFlag
Range("FlagSaved").Value = "Yes"
Range("ItemName").Select


End Sub
excel
MS Office Version: Office 365 Mac
posted by: TallTony (rep: 2) on Sun Feb 4, 2024 at 6:33 am
Answer
Discuss

Discussion

Hello John_Ru

 Thank you for your speedy reply.

 I entered the code as below, (hopefully with no spelling mistakes) and I still get the same error. I also added your suggestions.

 I will email Don as per your suggestion.

 Thank you



Sub SavePDF()
'
' SavePDF Macro
Dim fpath As String
 
fpath = "/Users/Guest/Documents/Projects/Georgina Invoice/"
invno = Range("InvNumber")
custname = Range("Customer")
fname = invno & custname & ".pdf"
 
'MsgBox (custname)
MsgBox (fname)
MsgBox (fpath)
 
 
    Application.Goto Reference:="InvoicePrint"
    ChDir "/Users/Guest/Documents/Projects/Georgina Invoice/"
 
'Check if invoice has been saved
If Dir(fpath & fname) = "" Then
'if not...save
 
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
   FileName:=fpath & fname, _
   Quality:=xlQualityMinimum, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, _
   OpenAfterPublish:=False
 
    MsgBox ("Saved " & fname & " to " & fpath)
    
    Else
    
    MsgBox (fname & " already exists in " & fpath)
   
   End If
   
   Range("ItemName").Select
   
End Sub
TallTony (rep: 2) Feb 4, '24 at 11:09 pm
Hello John_Ru

I managed to get the code to work, finally. 

Thank you for your input and help.
TallTony (rep: 2) Feb 5, '24 at 12:51 am
Glsd that worked (but next time do copy/paste to avoid any chance of typos). Thanks for selecting my Answer
John_Ru (rep: 6377) Feb 5, '24 at 1:08 am
Add to Discussion

Answers

0
Selected Answer

Hi TallTony and welcome to the Forum.

VBA can be confusing at first (and for a while!) but Don's excellent course will help you make sense of it and do some amazing things.

Because your question relates to the course, it ought to be directed to Don (you should have been given a support email address) but I will answer (without attaching a verson of your file).

The error arose because you mis-typed the last used parameter of the .ExportAsFixedFormat method - you had opeanafterpublish rather than  openafterpublish- change it and VBA will recognise it and convert it to OpenAfterPublish.

However, your code didn't really check if the file already exists. That's done using the Dir as follow (changes in bold):

Sub SaveInvoice()
'
' SaveInvoice Macro
'
Dim fpath As String

invno = Range("InvNumber")
custname = Range("Customer")
fname = invno & " - " & custname & ".pdf"

fpath = "/Users/Guest/Documents/Projects/Georgina Invoice/"

' Check if invoice has been previously saved
If Dir(fpath & fname) = "" Then
    ' if not... save
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=fpath & fname, _
        Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    MsgBox ("Saved " & fname & " to " & fpath)

    Else
    MsgBox (fname & " already exists in " & fpath)

End If


'Set SavedFlag
Range("FlagSaved").Value = "Yes"
Range("ItemName").Select


End Sub

(Hint- click the Select All barabove the code, copy then paste that over your existing code and try).

Note that I didn't use ChDir to change directory but defined a full path and filename under the FileName parameter.

Note to PC users: in Mac OS, a drive (like c:\) isn't needed and the filepath uses forward slash / as separators rather than the backslash \ used for Windows.

At some stage you may be tempted to add a date to your filename- take care since filepaths and filenames can't use certain characters, deemed illegal - e.g. / (particulary for Mac!) so check for your OS (and ask a new question if necessary).

Hope this solves your probelm- if so, please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6377) on Sun Feb 4, 2024 at 7:55 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login