I have a Run Time Error 91 for an Excel Add In

0 votes

I spent hours trying to debug this. This is a a macro to generate PDF from my selected cells. This code works on my personal workbook but when I export it as an add-in, add it in the developer tab and review the code again in the add-in workbook I keep getting an run time error 91: object variable or with block variable not set. Any help will be appreciated!

Sub Save_Selection_As_PDF_sheet()

Dim my_file As String


With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            '.Orientation = xlLandscape
            .Orientation = xlPortrait
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .PrintArea = Selection.Address
    Debug.Print I
    End With

FileName = ActiveWorkbook.Name
    If InStr(FileName, ".") > 0 Then
       FileName = Left(FileName, InStr(FileName, ".") - 1)
    End If
    
    my_file = "H:\data\Desktop\" & FileName & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=my_file, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
End Sub
Apr 5 in Database by Edureka
• 13,640 points
58 views

1 answer to this question.

0 votes

When there was no unhidden workbook open and the ActiveSheet was a chart sheet, the error Run-time error '91': Object variable or With block variable not set occurred.
When the folder didn't exist, different issues occurred (Run-time error '1004': Document not saved. It's possible that the document is open, or that an issue occurred during the saving process.) if the selection was not a range (Run-time error '438': This property or method is not supported by this object).

Option Explicit

Sub ExportSelectionToPDF()
    Const ProcName As String = "ExportSelectionToPDF"
    On Error GoTo ClearError

    Const dFolderPath As String = "H:\data\Desktop\"
    Const dFileExtension As String = ".pdf"
        
    If Len(Dir(dFolderPath, vbDirectory)) = 0 Then
        MsgBox "The path '" & dFolderPath & "' doesn't exist.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    Dim sh As Object: Set sh = ActiveSheet
    
    If sh Is Nothing Then ' to test, close all workbooks
        MsgBox "No active sheet ('Nothing').", vbCritical, ProcName
        Exit Sub
    End If
    
    If sh.Type <> xlWorksheet Then ' to test, activate a chart sheet
        MsgBox "No worksheet ('" & sh.Name & "') active.", vbCritical, ProcName
        Exit Sub
    End If
    
    If TypeName(Selection) <> "Range" Then ' to test, select a shape
        MsgBox "No range ('" & TypeName(Selection) & "') selected.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    Dim paAddress As String: paAddress = Selection.Address
    
    Dim BaseName As String: BaseName = sh.Parent.Name
    If InStr(BaseName, ".") > 0 Then
        BaseName = Left(BaseName, InStrRev(BaseName, ".") - 1)
    End If
    
    Dim dFilePath As String: dFilePath = dFolderPath & BaseName & dFileExtension
    
    With sh.PageSetup
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        '.Orientation = xlLandscape
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintArea = paAddress
    End With

    sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=dFilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    
ProcExit:
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub
answered Apr 6 by gaurav
• 13,460 points

Related Questions In Database

0 votes
1 answer

I have to convert utc time to ist in excel

There are no built-in functions for determining ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
798 views
0 votes
1 answer

Can I have multiple primary keys in a single table?

A Table can have a Composite Primary Key which ...READ MORE

answered Oct 25, 2018 in Database by Frankie
• 9,830 points
2,385 views
0 votes
1 answer

showing an error in a MySQL trigger

use this code: CREATE TRIGGER sample_trigger_msg ...READ MORE

answered Sep 27, 2019 in Database by Omaiz
• 560 points
667 views
0 votes
1 answer

I have to build a school website How can i connect databases to the instance (ec2) and what is the best AMI for my webserver INAWS ???

Hi@Trinu, I don't think your database is depends ...READ MORE

answered May 4, 2020 in Database by MD
• 95,360 points
288 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
338 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
1,968 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 13,460 points
28 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
77 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30 in Database by gaurav
• 13,460 points
211 views
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 13,460 points
44 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP