Passing Value from Access to Excel Range

Here is a simple way of passing a value from MS Access to an Excel File that is being opened by MS Access.

Situation: I have an MS Access Database form(A) which exports a query(B) to a certain directory as an excel file. This query is going to be used by an Excel Spreadsheet(C) which is preformatted for calculation and reporting. Now, as I open the preformatted Excel Spreadsheet(C) from Access(A) I would like to pass the directory location where I saved the query as xls (B) to SpreadSheet(C). This directory will be then used by Excel to know where to automatically pick-up the xls query(B) so that it can create pivot tables and other reports from (B).

(A) MS Access Form: This form has fields with directory location assigned as Me.DirLoc
(B) Query exported as XLS
(C) Preformatted XLS with loaded Macro to auto-fetch (B)

Private Sub CmdRunRegenerator_Click()
Dim DistDir
Dim ReportCreator As String

ReportCreator = Me.Regenerator
DestDir = Me.DIRLoc
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open DestDir & "\" & ReportCreator
'Include CARMA in menu, run AutoOpen
.ActiveWorkbook.RunAutoMacros xlAutoOpen
.ActiveWorkbook.Sheets("Sheet1").Range("D9").Value = Me.DIRLoc
x = .Run("RegenerateEmpFile")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
objXL.Quit
'AccountsViewEngine
'Auto_Open
End With
Set objXL = Nothing
UpdateSource
ClearInputOutputFiles
MsgBox ("Update completed and imported xls file cleared.")
End Sub

NOTE: The line highlighted in red is the command to pass the value of the Directory location in the MS Access form to the spreadsheet being opened.