The number must be between 1 and 2147483647. Try again by entering a number in this range.

The number must be between 1 and 2147483647. Try again by entering a number in this range.

The number must be between 1 and 2147483647. Try again by entering a number in this range. Excel VBA

It is really annoying as you are trying to export your data in pdf using standard object ExportAsFixFormat method offered by Microsoft Excel and getting above error. I tried a lot a straight forward command is not exporting my data into a file!!! finally I managed to bring it in my desired shape.

Code example

Public Function ExportFileExample(xlSheet As Worksheet, rngName As String, showFile As Boolean) As Boolean
	ExportFileAsPDF = False
	On Error GoTo errh
		Dim tmpFolderPath As String
		tmpFolderPath = Environ("APPDATA")
		Dim tmpFileName As String
		tmpFileName = "tmp" & Format(Now, "ddmmhhmmss") & ".pdf"
		xlSheet.Range(rngName).ExportAsFixedFormat Type:=xlTypePDF, Filename:=tmpFolderPath & "\" & tmpFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=showFile
		ExportFileAsPDF = True
	errh:
	If Err.Number <> 0 Then
		ExportFileAsPDF = False
	End If
End Function

Calling Method

Sub CallingMethod()
    If ExportFileAsPDF(ActiveSheet, "A1:G400", True) = False Then
        MsgBox "Fail to export"
    Else
        MsgBox "Export Success"
    End If
End Sub

So what is the difference I have made here? If you give a closer look to my calling method the second parameter I am sending Range as Hard Code string that is the fix:)

Next : Download file from API (Web Service) and Save in Desired Format using VBA

Leave a Reply

Your email address will not be published.