Saving active sheet in PDF all auto. But...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
Hi all.

Bellow is the code I've been tinkering and googling away and it's working to an extent.

Having said this, it's driving me crazy that this is adding number 31 in two cells, not shown on excel but once pdf file is saved.

What on earth is happening here and were is this number 31 coming from?

Also, the code is calling for list of printers with doPDF already selected by user has to accept. This should happen automatically.

I also have set a printing area smaller than where the number 31 are showing when saved meaning the print to pdf is printing more than the printing area.

VBA Code:
Private Sub CBGenDocument_Click()

    If Range("F3") = Empty Then 'Checks if next document value is empty.
        MsgBox "Please select your document type!", vbInformation, "Document..."
        Range("E3").Select
        Selection.ClearContents
        Exit Sub
    End If
    
    If MsgBox("You have selected """ & Range("E3") & """ document." & vbNewLine & "Is this the document you wish to generate?", vbQuestion + vbYesNo, "Document type...") = vbNo Then
        Range("F3").Clear
        Range("E3").Select
        Selection.ClearContents
        Exit Sub
    End If
    
    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            
            Dim Data(1 To 4) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:D1")
            Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
            Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
       
            With Worksheets("Create")
                Data(1) = .Range("F3")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
            End With
            
            DstRng = Data


                    'save invoice pdf code below
               
            Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice but its asking user to select it. want it all auto.
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "D:\Software\Invoices\INV" & Range("F3").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
                :=True, OpenAfterPublish:=False
            If Application.Dialogs(xlDialogPrinterSetup).Show Then
            End If
               
                'save invoice code above
                
                    'bellow clears invoice number after saved in pdf
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
                Selection.ClearContents
                'Selection.NumberFormat = "00000"
                    'above clears invoice number after saved in pdf
                
                
                'Proforma code bellow...
                
        Case Is = "proforma"
            Set DstRng = Worksheets("Proformas").Range("A1:D1")
            Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
            Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 4))
       
            With Worksheets("Create")
                Data(1) = .Range("F3")  'Proforma number
                Data(2) = .Range("C12")  'Proforma total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
            End With
            
            DstRng = Data

                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
                Selection.ClearContents
            
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
                Selection.ClearContents
                
      End Select
      
End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
        
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("E3")) Is Nothing Then
      If Target.Value <> "" Then
         With Sheets(Target.Value & "s") 'The "s" is the last letter in the sheets Invoices and Proformas.
            Target.Offset(, 1).Value = Application.Max(.Range("A:A")) + 1
         End With
         
         Range("F3").Select
            With Selection
            .HorizontalAlignment = xlCenter
            .NumberFormat = "00000"
            End With
     
      End If
   End If
   
End Sub

Any help on the above is trully appreciated as I am running out of ideas especially as to where is the number 31 coming from.

Thanks.
 

Attachments

  • Screenshot_11.png
    Screenshot_11.png
    23.2 KB · Views: 7

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try setting IgnorePrintAreas to false

VBA Code:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "D:\Software\Invoices\INV" & Range("F3").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
 
Upvote 0
It was false to start with and I changed it to true. The issue still occurs either way.
Can't figure it out why the number 31 is being added to the pdf although it's not on the sheet itself.
 
Upvote 0
you have a *on_change* event set up anytime someone enters a new value into cell *e3*. when i entered the value "invoice" into E3, a number was automatically populated in cell F2. after every run of the code, those 2 cells are emptied. my guess is that, either you've been coding too long today and there really isn't a problem, or you've tried to run the code on invoice #31 one too many times. what do you think?
 
Upvote 0
you have a *on_change* event set up anytime someone enters a new value into cell *e3*. when i entered the value "invoice" into E3, a number was automatically populated in cell F2. after every run of the code, those 2 cells are emptied. my guess is that, either you've been coding too long today and there really isn't a problem, or you've tried to run the code on invoice #31 one too many times. what do you think?
Hi.
Cell E3 value is inserted with that on change event. It picks up last value and adds 1 or counts rows and adds 1 as in next invoice or proforma number.
I'm rubbish at coding but always find it amazing. At 42 I don't think I'll ever amount to much in terms of coding. Miss setting up Cisco routers and switches using the command line.
I'm cracking my head open with this. It's a challenge and Google helps. But if it wasn't for the group I'd never have achieved the invoice excel system I have produced.
Now just tinkering aside to then move and implement the code to it.
Many thanks for your help. It's truly appreciated.
I'll open the sheet and run code once and see if the 31 number is again inserted.
 
Upvote 0
OMG!!!

How to feel stupid. Very stupid.

The numbers 31 were in fact on the sheet, behind the command button.

Having said this they shouldn't be on the pdf as they are not part of the print area.

At least now I know where the number 31 was coming from.

Any idea on how to just save the pdf without calling the Printer Setup?

Many thanks.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top