Save Current Sheet as PDF and Link that PDF in Access

Ryandeal92

New Member
Joined
Jun 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,141,605
Messages
5,707,355
Members
421,503
Latest member
Rickys03

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
Top