Can We save Excel WOrkbook Report also as PDFReport using vba

[baba]

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

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.

 

[pbaldy]

A common trick is to record a macro in Excel doing what you want, and then adapt the resulting code into your automation code.

 

[baba]

Thanks. Let me ask my question better- I have Book1.xlsx in C Drive. I am able to open Book1.xlsx and save it as Book1.pdf manually and the format and contents are preserved in the pdf. Can I do the same by opening the Book1.xlsx and save it as Book1.pdf and close both Book1.xlsx and Book1.pdf using vba through MsAccess VBA?

 

[pbaldy]

Yes, you can.

 

[baba]

Can you please let me know the syntax? Is it through docmd transfer or how? Thanks for the help ! Assume my Excel report is in this location- (CurrentProject.Path & "\Book1.xlsx")

 

[pbaldy]

Was post 2 not clear? I'm trying to teach you how to fish. You already know how to open it, so that technique will get you started on the code to export.

 

[baba]

Thanks for the details . Let me give it a shot. I also have a sample code !

 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

 

[pbaldy]

Looks similar to what the macro recording would have given you. It just needs to be adapted to use your automation objects.