Cfrtjryk

I've written the following code to take data from the Excel file "Data" and use it for a mailmerge in a prepare Word template named "Template." I'm new to using VBA in word or Access, so any feedback in these areas is very welcome

Sub AccessTransfer()



Dim strPath As String

Dim objAccess As Access.Application

Dim strExcelPath As String

Dim lastRow As Long

lastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).row

Dim rng As Range

Set rng = Sheets("Data").Range("A" & lastRow, "M" & lastRow)

strPath = "L:...MailMergeTEMPDB.accdb"

Set objAccess = CreateObject("Access.Application")

Call objAccess.NewCurrentDatabase(strPath)

objAccess.Visible = False

strExcelPath = Application.ActiveWorkbook.FullName

Set objAccess = New Access.Application

Call objAccess.OpenCurrentDatabase(strPath, False)

objAccess.Visible = False

Call objAccess.DoCmd.TransferSpreadsheet(acImport, _

acSpreadsheetTypeExcel8, "MailMergeTable", strExcelPath, _

True, Range:="Data!")

Access.Application.CloseCurrentDatabase



Dim wdApp As Object, newDoc As Object

    Dim strFile As String

    strFile = "L:...Template.docm"

    Set wdApp = CreateObject("Word.Application")

    wdApp.Documents.Open strFile, ReadOnly:=False

    wdApp.Visible = True

    wdApp.Run "Project.Module1.WordMailMerge"

    Kill "L:....MailMergeTEMPDB.accdb"

End Sub

And then Macro called from within Word is:

Sub WordMailMerge()

Dim strFile As String

strFile = "L:...MailMergeTEMPDB.accdb"

    ActiveDocument.MailMerge.OpenDataSource Name:= _

        strFile _

        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _

        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _

        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _

        Format:=wdOpenFormatAuto, Connection:= _

        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=strFile;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _

        , SQLStatement:="SELECT * FROM `MailMergeTable`", SQLStatement1:="", _

        SubType:=wdMergeSubTypeAccess

    With ActiveDocument.MailMerge

        .Destination = wdSendToNewDocument

        .SuppressBlankLines = True

        With .DataSource

            .FirstRecord = wdDefaultFirstRecord

            .LastRecord = wdDefaultLastRecord

        End With

        .Execute Pause:=False

    End With

    Windows("AH_MacroMailMergeTemplate").Activate

    ActiveDocument.Close

End Sub

I'm also new to using VBA to navigate trough directories, so feedback in this aspect would be appreciated too.

There's no need to Call anything, it's obsolete. Instead just use Sub argument, argument.

What's this

Kill "L:....MailMergeTEMPDB.accdb"

Is this closing the access object? Is this deleting the file? Be explicit - Kill isn't something you generally want to see in any code.

FSO.Deletefile strPath