Passing Value from Access to Excel Range. The following blog will share to you how the File location directory will be passed to a Range in an Excel Spreadsheet. 

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.

Access Data Definition Language - Creating and Altering Tables On The Fly

One of the first things an Access developer learns is how to write SQL Queries. A simple request for data might look something like this:
SELECT FullName, Address, Phone
FROM tblEmployee ORDER BY FullName
This type of query uses Data Manipulation Language (DML). While this example does not perform any great manipulation of the data, some very clever calculations are possible. The SQL language is powerful indeed.
However, once you have gotten a handle on DML, you will want to explore the world of DDL, Data Definition Language. This too is part of the SQL language and can be run from an Access query, or through VBA code, but unlike DML, these commands do not return a result set. DDL is used to create and alter database objects, such as tables. First, let's see how this is done and then consider some scenarios where this technique will save you time and in some cases, a lot of work.

The process to create a DDL query is a little different from what you might be used to. Begin in the normal way, by choosing New Query from the Query window, but when prompted, do not add any tables. You are then presented with the QBE (query by example) grid with no tables. Select Data Definition from the Query | SQL Specific menu to continue.

Selecting Data Definition will take you to a very plain, Notepad-like interface where you can paste or type in your DDL SQL script. Save the query and select Run from the Query menu to execute the script. When you run this kind of query, Microsoft Access displays the following warning:

PROBLEM:An employee table needs to have their salary updated based from an imported excel file containing the Global ID of the Employees with matching Salary changes. When importing the Excel spreadsheet it automatically set the data type of the Global ID field into numbers. This creates an error when doing an update because the Access table to be updated contains a Global ID field that is preset to data type text.

SUGGESTION:Change the Global ID Data type of the imported Excel Spreadsheet into Text on the fly, before executing the update command.

SOLUTION:
1. Invoke a query. From Object Window click Queries, Click New, Design View, OK, Close, Click Query Grid. From the Main Menu click Query, SQL Specific, Data Definition.
2. Inside the query window Type: ALTER TABLE [Source Table] ALTER COLUMN GID Text;
3. Save the query. With my example I saved it as AlterSourceTableQ
4. Call the query inside your procedure:

Private Sub CMDImportEmpFile_Click()
Dim SQL As String
DoCmd.SetWarnings False

SQL = "DELETE [Source Table].* " & _
"FROM [Source Table];"

DoCmd.RunSQL SQL


DoCmd.OpenQuery "AlterSourceTableQ"
DoCmd.OpenQuery "UpdateSourceFromImportedFileQ"
DoCmd.SetWarnings True
End Sub

To find out more....Click here

Opening Excel from MS Access thru VBA

This procedure opens up the excel sheet from a given directory and run a macro from within excel.

Private Sub CmdOpenframedetailedCombiner_Click()
Dim strPath As String
Dim strFileName As String
Dim strSourceDir As String
Dim strSheetName As String
Dim xlApp As Object
Dim xlSheet As Object

strSourceDir = "\\zwnwb080\Groups3\CDMAProd\PRODUCTION SERVICE SHEETS\daily web plan\"
strFileName = "frame and detailed combiner v7 with GSM Update.xls"
strPath = strSourceDir & strFileName
strSheetName = "detailed"

Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open strPath
.Run "UpdateDetailedFromForXlsdetailedQ"
End With
End Sub

Exporting Query to Excel using VBA

Before you perform the export operation you have to follow simple steps to produce the right output you want:

1. IDENTIFY Source Query.
The Query is called : ExpenseSummarySelectQ
We will assign a variable called: stDocName for ExpenseSummarySelectQ

2. IDENTIFY Output File and Directory.
The output Excel file will be called: ExpenseSummarySelect 04-11-05.xls
The directory this file will be exported is: \\zwnwb080\Expense
3. SET VARIABLES and their types.
"ExpenseSummarySelectQ" = stDocName type String
"ExpenseSummary" = FileName type String
"04-11-05" = tDate As String
"\\zwnwb080\Expense\" = FileDir As String

To combine the filename and Directory like this:
\\zwnwb080\Expense\ExpenseSummary 04-11-05.xls
You need to combine variables too:
XFile = FileDir & FileName & tDate & " .xls"

6. Compose your Procedure.
Private Sub CMDRunExpSummarySelectQ_Click()
Dim stDocName As String
Dim FileName As String
Dim XFile As String
Dim tDate As String
Dim FileDir As String

stDocName = "ExpenseSummarySelectQ"
FileName = "ExpenseSummary"
tDate = Format(Now(), "mm-dd-yy")
FileDir = "\\zwnwb080\"
xFile = FileDir & FileName & tDate & " .xls"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "ExpenseSummarySelectQ", _
acFormatXLS, xFile, False
DoCmd.Close acQuery, "ExpenseSummarySelectQ", acSaveNo
MsgBox (xFile & " in " & FileDir & " had been created. Excel Report will now be created.")

End Sub