Microsoft Office Tutorials and References
In Depth Information
Exporting an Access Table to an Excel Spreadsheet
WHAT if you WAnT To opEn An AccEss dATABAsE filE only
froM ExcEl?
A common theme you’ll notice with the examples in this lesson is that Excel is
acting upon the Access files by connecting to them, rather than by opening and
closing them as you saw in the lessons for working with Word and Outlook. You will
rarely need Excel to open an Access database just for the sake of opening it.
If the situation should arise where you do need to open an Access database from
Excel, the following example is what I use. It works by incorporating a ShellExecute
command in conjunction with the declaration of the ShellExecute function in the
Microsoft Windows programming language Applications Programming Interface,
or API. The subject of API can easily fill a large book, but in brief, ShellExecute
in API performs an operation on a specified file. In this case, the specified file is
the one you want to open (named “Database1.accdb” in the hypothetical directory
path “C:\YourFilePath”), and the operation is to open that database file, using the
parameters in the declaration statement. This code is placed in a standard Excel
VBA module just as any macro would be, and works with Windows versions from
XP through Windows 7.
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
Sub OpenAccessDB()
Call ShellExecute(0, “Open”, “Database1.accdb”, “”, _
“C:\YourFilePath\”, 1)
End Sub
ExporTing An AccEss TABlE To An ExcEl sprEAdsHEET
As mentioned earlier, you will commonly need to import a table from an Access database into an
Excel worksheet, to take advantage of Excel’s versatile formatting and data manipulation
capabilities. To export the database’s Table1 data, you define the recordset while passing an SQL string to
the connection. In this example, the entire count of records in Table1 will be copied to Sheet2 in
your Excel workbook:
Sub AccessToExcel()
‘Declare variables.
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
 
Search JabSto ::




Custom Search