Save Current Sheet as PDF and Link that PDF in Access

[Ryandeal92]

Hello! I am new to this forum and somewhat new to Excel/Access VBA. Have been able to find solutions to most my problems in this forum already except for this one.

I have an excel workbook used for doing quotes. It's more like a form than anything. I want to use Access to store the quote information and then handle projects from there.

So I have setup an "export" sheet/table that stores all the variables and pertinent information, which I have a VBA code setup to export those values into my access table.

The next step I want to do is save my "Quote" worksheet as a .pdf and then attach that .pdf to the access table. Preferably I would like to save the whole work book and attach that as additional attachment, in case of any revisions needed. I could alternatively setup so that access could export the selected quote data to the excel workbook, but saving the original would be better in case any updates come to the workbook and we want to use the data we had at the time for the revisions rather than the updated info. I do not have the workbook store information, it is reset after a quote is finished.

Below is my code as it is now. Currently I have my export as a separate module. Figuring I will have to bring this all into one right? Not sure where to go from here. I know it is possible to attach a file in access using VBA. Is there a way that I can do it following this sequence though? Want to save the step of having to find the file and attach it after saving.

Sub Button1_Click()
    Dim newFile As String, fName As String
    fName = Range("I1").Value
    newFile = fName & " " & Format$(Date, "mm-dd-yyyy")

    ChDir _
    "C:\Users\XXXXs"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newFile, OpenAfterPublish:=True
    Range("H2").Value = Range("H2").Value + 1
'adds +1 to quote number.
 Call ADOFromExcelToAccess
    MsgBox "Export and Backup Complete"
end Sub

Sub ADOFromExcelToAccess()
Sheets("Export").Select
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Users\xxxxx;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Quotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("B" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("XXXX") = Range("B" & r).Value
.Fields("XXX") = Range("AE" & r).Value

.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub