CODEWITHSUNDEEP
excelms-accesspdfvba
Thomas Mebane
Thomas Mebane

Reputation: 1

How to convert this MS Excel Visual Basic code to work with MS Access

I need help converting this Visual Basic code to work with MS Access. Right now I use it in MS Excel to fill out PDFs using the data in the form and I want to migrate my data base to MS Access but still be able to fill out PDF from it..

Here is the first VB Module.

Public Sub ListPDF_Fields()

Dim AcroExchAVDoc As CAcroAVDoc
Dim AcroExchApp As CAcroApp
Dim AFORMAUT As AFORMAUTLib.AFormApp
Dim FormField As AFORMAUTLib.Field
Dim FormFields As AFORMAUTLib.Fields
Dim bOK As Boolean
Dim sFields As String
Dim sTypes As String
Dim sFieldName As String

' For this procedure to work, computer must have a full version
' of Adobe Acrobat installed. Also, a reference to the following
' Type Libraries must be made:
'     AFormAut 1.0
'     Adobe Acrobat 7.0 (or newer)

On Error GoTo ErrorHandler

Set AcroExchApp = CreateObject("AcroExch.App")
Set AcroExchAVDoc = CreateObject("AcroExch.AVDoc")
bOK = AcroExchAVDoc.Open(ActiveWorkbook.Path & "\" & PDF_FILE, "")
AcroExchAVDoc.BringToFront
AcroExchApp.Hide

If (bOK) Then
    Set AFORMAUT = CreateObject("AFormAut.App")
    Set FormFields = AFORMAUT.Fields
    For Each FormField In FormFields
        With FormField
            sFieldName = .Name
            If .IsTerminal Then
                If sFields = "" Then
                    sFields = .Name
                    sTypes = .Type
                Else
                    sFields = sFields + "," + .Name
                    sTypes = sTypes + "," + .Type
                End If
            End If
        End With
    Next FormField
    AcroExchAVDoc.Close True
End If
Debug.Print sFields
Debug.Print sTypes

Set AcroExchAVDoc = Nothing
Set AcroExchApp = Nothing
Set AFORMAUT = Nothing
Set Field = Nothing
Exit Sub

ErrorHandler:
MsgBox "FieldList Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source

End Sub

Here is the second VB Module

Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Const SW_NORMAL = 1
Public Const PDF_FILE = "CX.pdf"
Public Sub MakeFDF()

Dim sFileHeader As String
Dim sFileFooter As String
Dim sFileFields As String
Dim sFileName As String
Dim sTmp As String
Dim lngFileNum As Long
Dim vClient As Variant

    ' Builds string for contents of FDF file and then writes file to workbook folder.
On Error GoTo ErrorHandler

sFileHeader = "%FDF-1.2" & vbCrLf & _
          "%âãÏÓ" & vbCrLf & _
          "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
          "endobj" & vbCrLf & _
          "2 0 obj[" & vbCrLf

sFileFooter = "]" & vbCrLf & _
          "endobj" & vbCrLf & _
          "trailer" & vbCrLf & _
          "<</Root 1 0 R>>" & vbCrLf & _
          "%%EO"

sFileFields = "<</T(PCSID)/V(---PCS_ID---)>>" & vbCrLf & "<</T(STATIONSN)/V(---STATION_SN---)>>" & vbCrLf & "<</T(XFMRSN)/V(---XFMR_SN---)>>" & vbCrLf

vClient = Range(Selection.Row & ":" & Selection.Row)

sFileFields = Replace(sFileFields, "---PCS_ID---", vClient(1, 1))
sFileFields = Replace(sFileFields, "---STATION_SN---", vClient(1, 2))
sFileFields = Replace(sFileFields, "---XFMR_SN---", vClient(1, 3))

sTmp = sFileHeader & sFileFields & sFileFooter

' Write FDF file to disk
If Len(vClient(1, 1)) Then sFileName = vClient(1, 1) Else sFileName = "FDF_DEMO"
sFileName = ActiveWorkbook.Path & "\" & sFileName & ".fdf"
lngFileNum = FreeFile
Open sFileName For Output As lngFileNum
Print #lngFileNum, sTmp
Close #lngFileNum
DoEvents

    ' Open FDF file as PDF
ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
Exit Sub

ErrorHandler:
MsgBox "MakeFDF Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source

End Sub

Right now I have a UserForm in Excell that I used to enter data, on that UserForm is a button that is programmed to RunApplication MakeFDF.

Any help you can provide would be much appreciated.

Upvotes: 0

Views: 934

Answers (1)

Andy G
Andy G

Reputation: 19367

Most of this code should run practically unaltered from Access, provided you have the correct References ticked in Tools/References.

An Excel specific item is:

ActiveWorkbook.Path

the equivalent in Access is

Application.CurrentProject.Path

and this:

vClient = Range(Selection.Row & ":" & Selection.Row)

which equivalent values I assume you would read from either an Access table or textboxes on a Form.

Upvotes: 1

Related Questions