edit word document in VBA Access

[ilse20]

hello,

i'm programming with access,

i want to generate labels to a pdf file, but in a pdf file i can't change the margins. now i wanted to do this through a word file.
i can change the normal.dot file with the correct margins, but then it applies to all word documents.

i have a code to make a new word file in VBA Access.

Dim word 'the Word application
    Dim doc 'the Word document
    Dim selection 'text selection
    Dim fs 'the File system
    Dim work_dir 'the directory for storing files
    Dim folder, folders 'used when creating the new file
    Dim i, j 'indexes for arrays
    Dim h_Filepath As String

    work_dir = "C:\Users\Piper\Desktop\"
    h_filpath = "work_dir" & "labels.doc"
    
    Set word = CreateObject("word.application")
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    Set doc = word.Documents.Add
    Set selection = word.selection
    selection.typetext "An Automated Word Report"
    selection.typeparagraph
    
    If Not fs.folderexists(work_dir) Then
        folders = Split(work_dir, "\")
            For i = 0 To UBound(folders) - 1
                folder = ""
            For j = 0 To i
                folder = folder & folders(j) & "\"
            Next
                If Not fs.folderexists(folder) Then
                    fs.createfolder folder
                End If
            Next
    End If

    doc.SaveAs (work_dir & "labels.doc")
    word.Visible = True
    
    Set fs = Nothing
    Set word = Nothing

this code creates the word file and there are the correct margins, because if change them in normal.dot

i also have the code to generate the pdf file:

Dim h_qdfnew As DAO.QueryDef
    Dim h_strSQL As String
    Dim ChosenName As String
    Dim h_stdocname As String
    Dim h_Filepath As String
    Dim h_Queryname As String
    Dim h_qdf As DAO.QueryDef
    Dim h_amountlabels As String
    Dim h_table As String
    Dim h_TemplateFile As String
    Dim h_LabelFile As String
        
    h_Filepath = "C:\Users\Piper\Desktop\labels.doc"
    h_TemplateFile = "C:\Users\Piper\AppData\Roaming\Microsoft\Templates\Normal.dot"
    h_LabelFile = "C:\Users\Piper\Desktop\labels.pdf"
    ChosenName = "ilse van broekhoven"
    h_strSQL = "SELECT * FROM Generate_labels WHERE ChangeUserName = '" & ChosenName & "';"
    h_Queryname = "Query_labels"
    h_stdocname = "Adresetiketten Generate_labels_Query"
    h_table = "Generate_labels"
    
     For Each h_qdf In CurrentDb.QueryDefs
            If h_qdf.Name = h_Queryname Then
                CurrentDb.QueryDefs.Delete (h_Queryname)
            End If
        Next h_qdf
        
    Set h_qdfnew = CurrentDb.CreateQueryDef(h_Queryname, h_strSQL)
    h_amountlabels = DCount("ChangeUserName", h_table, "ChangeUserName = '" & ChosenName & "'")
        
        If h_amountlabels < 1 Then
            MsgBox "No labels saved for current user" & vbNewLine & "Please save a label first"
        Else
            With DoCmd
                MsgBox "Don't forget to press the CLEAR LABELS button when you have printed the labels!"
                .OutputTo acOutputReport, h_stdocname, acFormatRTF, h_Filepath, False, h_TemplateFile, , acExportQualityScreen
                .OutputTo acOutputReport, h_stdocname, acFormatPDF, h_LabelFile, True, , , acExportQualityScreen
            End With
        End If

this code creates the word file and generates a pdf file, but with this the margins ain't correct and because of that the labels aren't displayed correctly.

now my question is if it is possibly to set the margins this way.
or that it's possibly to create a template file that is only being used by this file.

 

[HiTechCoach]

hello,

i'm programming with access,

i want to generate labels to a pdf file, but in a pdf file i can't change the margins. now i wanted to do this through a word file.
i can change the normal.dot file with the correct margins, but then it applies to all word documents.

i have a code to make a new word file in VBA Access.

Dim word 'the Word application
    Dim doc 'the Word document
    Dim selection 'text selection
    Dim fs 'the File system
    Dim work_dir 'the directory for storing files
    Dim folder, folders 'used when creating the new file
    Dim i, j 'indexes for arrays
    Dim h_Filepath As String

    work_dir = "C:\Users\Piper\Desktop\"
    h_filpath = "work_dir" & "labels.doc"
    
    Set word = CreateObject("word.application")
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    Set doc = word.Documents.Add
    Set selection = word.selection
    selection.typetext "An Automated Word Report"
    selection.typeparagraph
    
    If Not fs.folderexists(work_dir) Then
        folders = Split(work_dir, "\")
            For i = 0 To UBound(folders) - 1
                folder = ""
            For j = 0 To i
                folder = folder & folders(j) & "\"
            Next
                If Not fs.folderexists(folder) Then
                    fs.createfolder folder
                End If
            Next
    End If

    doc.SaveAs (work_dir & "labels.doc")
    word.Visible = True
    
    Set fs = Nothing
    Set word = Nothing

this code creates the word file and there are the correct margins, because if change them in normal.dot

i also have the code to generate the pdf file:

Dim h_qdfnew As DAO.QueryDef
    Dim h_strSQL As String
    Dim ChosenName As String
    Dim h_stdocname As String
    Dim h_Filepath As String
    Dim h_Queryname As String
    Dim h_qdf As DAO.QueryDef
    Dim h_amountlabels As String
    Dim h_table As String
    Dim h_TemplateFile As String
    Dim h_LabelFile As String
        
    h_Filepath = "C:\Users\Piper\Desktop\labels.doc"
    h_TemplateFile = "C:\Users\Piper\AppData\Roaming\Microsoft\Templates\Normal.dot"
    h_LabelFile = "C:\Users\Piper\Desktop\labels.pdf"
    ChosenName = "ilse van broekhoven"
    h_strSQL = "SELECT * FROM Generate_labels WHERE ChangeUserName = '" & ChosenName & "';"
    h_Queryname = "Query_labels"
    h_stdocname = "Adresetiketten Generate_labels_Query"
    h_table = "Generate_labels"
    
     For Each h_qdf In CurrentDb.QueryDefs
            If h_qdf.Name = h_Queryname Then
                CurrentDb.QueryDefs.Delete (h_Queryname)
            End If
        Next h_qdf
        
    Set h_qdfnew = CurrentDb.CreateQueryDef(h_Queryname, h_strSQL)
    h_amountlabels = DCount("ChangeUserName", h_table, "ChangeUserName = '" & ChosenName & "'")
        
        If h_amountlabels < 1 Then
            MsgBox "No labels saved for current user" & vbNewLine & "Please save a label first"
        Else
            With DoCmd
                MsgBox "Don't forget to press the CLEAR LABELS button when you have printed the labels!"
                .OutputTo acOutputReport, h_stdocname, acFormatRTF, h_Filepath, False, h_TemplateFile, , acExportQualityScreen
                .OutputTo acOutputReport, h_stdocname, acFormatPDF, h_LabelFile, True, , , acExportQualityScreen
            End With
        End If

this code creates the word file and generates a pdf file, but with this the margins ain't correct and because of that the labels aren't displayed correctly.

now my question is if it is possibly to set the margins this way.
or that it's possibly to create a template file that is only being used by this file.

Yes, you can create custom template for you labels

create a label document with the desired settings and the save as a template (labels.dot)

whn you create a new document for label, use this template

h_TemplateFile = "C:\Users\Piper\AppData\Roaming\Microsoft\Templates\labels.dot"

 

[ilse20]

yes i already tried that. i put that labels.dot in the template file in the outputto but that doesn't work

 

[HiTechCoach]

the template file is not the output but it is the input file used for creating the new document.

You add a new document based on the template.

can you post your code using the template?

 

[ilse20]

domcd.OutputTo acOutputReport, h_stdocname, acFormatRTF, h_Filepath, False, h_TemplateFile, , acExportQualityScreen

 

[ilse20]

i'm not going to work with a word file any more. i am trying to do it now through the report. set the margins there and generate that to a pdf file. but it doesn't quite work yet. i need to get 16 labels on a single page. but i only get 14 because the margins at the bottom are to huge.