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, 2022 in Database by Edureka
• 13,630 points
291 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, 2022 by gaurav
• 22,960 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, 2022 in Database by Edureka
• 13,630 points
6,175 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,742 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
1,234 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 53,520 points
153 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,520 points
201 views
0 votes
1 answer

I have a problem with counta function in VBA

When the worksheet name has a space, ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
134 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21 in Others by narikkadan
• 53,520 points
47 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, 2022 in Database by gaurav
• 22,960 points
5,915 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, 2022 in Database by gaurav
• 22,960 points
199 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP