Can We save Excel Workbook Report also as PDF Report using vba

[baba]

Set Wkb1 = xlApp.Workbooks.Open(CurrentProject.Path & "\Book1.xlsx")
Wkb1.Save // To save the Excel workbook after updating and writing contents.

I use the above vba code snippet to save contents in an excel workbook and save it in my Current Folder.

Is there a way to save the same Excel workbook Book1.xlsx as a pdf through MsAccess vba preserving the same
positioning and formatting of different columns and contents in the Excel Workbook.

[June7]

Although never tried, inclined to say yes. I have code behind a workbook that saves worksheet as PDF. This should be adaptable to run from Access to act on the Excel object that Access opened in VBA.

Here is my workbook code:

Private Sub btnPDF_Click()
Dim FName As Variant
If ActiveWindow.SelectedSheets.Count > 1 Then
    MsgBox "More than the GEOReport sheet is selected, printing canceled."
Else
    Call PrintSetup
    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") = "" Then
         MsgBox "Error: MS Add-in not found. Contact Administrator"
    Else
        ChDrive (Left(ActiveWorkbook.Path, 1))
        ChDir (Left(ActiveWorkbook.Path, 1) & ":\")
        'Open the GetSaveAsFilename dialog to enter a file name for the pdf
        FName = Application.GetSaveAsFilename(gstrStateNum & "GeoReport.pdf", _
                                                FileFilter:="PDF Files (*.pdf), *.pdf", _
                                                Title:="Create PDF")
        'If you cancel this dialog Exit the function
        If FName <> False Then
            'test if PDF already exists in the folder
            If Dir(IIf(Not FName Like "*.pdf", FName = FName & ".pdf", FName) & "") <> "" Then
                If MsgBox("Overwrite existing file?", vbYesNo) = vbNo Then
                    FName = InputBox("Enter file name.")
                End If
            End If
            If Not FName Like "*.pdf" Then FName = FName & ".pdf"
            'Now the file name is correct we Publish to PDF
            On Error Resume Next
            ActiveSheet.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    FileName:=FName, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=True
            On Error GoTo 0
        End If
    End If
End If

Private Sub PrintSetup()
Dim intPages As Integer
Dim intRows As Integer
Dim strCell As String
Dim i As Integer
'number of records extracted
intRows = Worksheets("Samples").Range("C4").Value
'Calculate number of pages based on number of records extracted
If intRows / 6 - Int(intRows / 6) <> 0 Then
    intPages = Int(intRows / 6) + 1
Else
    intPages = Int(intRows / 6)
End If
With Worksheets("GEOReport").PageSetup
'    .PrintTitleRows = "$1:$4"
'    .PrintTitleColumns = ""
    .PrintArea = "$A2:$H" & intPages * 40 + 4
'    .LeftHeader = ""
'    .CenterHeader = ""
'    .RightHeader = ""
'    .CenterFooter = _
'    "Gradation tests based on minus 3"" material." & IIf(optAASHTO, "  AASHTO Class may be inappropriate if Organic Content > 5%.", "") _
'    & vbLf & vbLf & "Page &P of " & intPages
    .LeftFooter = "Gradation tests based on minus 3"" material."
    .RightFooter = "Page &P of " & intPages
'    .LeftMargin = Application.InchesToPoints(0.75)
'    .RightMargin = Application.InchesToPoints(0.25)
'    .TopMargin = Application.InchesToPoints(1)
'    .BottomMargin = Application.InchesToPoints(1)
'    .HeaderMargin = Application.InchesToPoints(0.5)
'    .FooterMargin = Application.InchesToPoints(0.5)
'    .PrintHeadings = False
'    .PrintGridlines = False
'    .PrintComments = xlPrintNoComments
'    .CenterHorizontally = False
'    .CenterVertically = False
'    .Orientation = xlPortrait
'    .Draft = False
'    .PaperSize = xlPaperLetter
'    .FirstPageNumber = xlAutomatic
'    .Order = xlDownThenOver
'    .BlackAndWhite = False
'    .Zoom = 100
'    .PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

[baba]

Thanks. HOwever, I am trying to do it from MsAccess vba and the code resides in MsAccess. I found one more ...

  Dim WBName, FilePath As String 
    WBName = ActiveWorkbook.Name 
    FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & WBName & ".pdf" 
     
    Sheets.Select 
    ActiveSheet.ExportAsFixedFormat _ 
    Type:=xlTypePDF, _ 
    Filename:=FilePath, _ 
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _ 
    IgnorePrintAreas:=False, OpenAfterPublish:=True

[ItsMe]

Thanks. HOwever, I am trying to do it from MsAccess vba and the code resides in MsAccess...

June already provided you with an example.

            ActiveSheet.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    FileName:=FName, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=True

You said you have this

Set Wkb1 = xlApp.Workbooks.Open(CurrentProject.Path & "\Book1.xlsx")

So get a hold of the worksheet and activate it. Then use the code provided earlier.