Ken's Code Examples for Importing / Exporting With EXCEL Workbooks
Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet
Write Data From a Recordset into an EXCEL Worksheet using Automation
Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
Read Data from EXCEL File via Query (SQL Statement)
Write Data From an EXCEL Worksheet into a Recordset using Automation
Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File
Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet
Generic code to generate "on the fly" a query that uses one or more controls on an open form as parameters, and then export that query to an EXCEL file. This example concatenates the parameter values into the generated SQL statement and then saves the query so that it can be exported. The query then is deleted after the export is completed.
Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery " & _
"WHERE NameOfTableOrQuery.FieldName >= " & _
Format(Forms!NameOfForm!ADateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & _
" And NameOfTableOrQuery.FieldName <=" & _
Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & "';"strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing
Create a Query and Export multiple "filtered" versions of the Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet
Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate EXCEL files, one file for each manager.
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
Create a Query and Export multiple "filtered" versions of the Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet
Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate worksheets within the same EXCEL file, one worksheet for each manager.
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
' filename without the .xls extension
' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)
Const strFileName As String = "PutEXCELFileNameHereWithoutdotxls"Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If
rstMgr.Close
Set rstMgr = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
Write Data From a Recordset into an EXCEL Worksheet using Automation
Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file must already exist, and the worksheet must already exist in the EXCEL file), and then to loop through the recordset and write each field's value into a cell in the worksheet, with each record being written into a separate row in the worksheet. The starting cell for the EXCEL worksheet is specified in the code; after that, the data are written into contiguous cells and rows. This code example uses "late binding" for the EXCEL automation.
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("WorksheetName")
' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Thenrst.MoveFirst
' *** OMIT THESE CODE STEPS IF YOU DO NOT WANT TO WRITE THE FIELD
' NAMES INTO THE FIRST ROW OF DATA IN THE WORKSHEET
' if you want to write field names in the first row:
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
' *** END OF CODE STEPS TO BE OMITTED
' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
LoopEnd If
rst.Close
Set rst = Nothingdbs.Close
Set dbs = NothingSet xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
Generic code to import the data from the first (or only) worksheet in all EXCEL files that are located within a single folder. All of the EXCEL files' worksheets must have the data in the same layout and format.
Dim strPathFile as String, strFile as String, strPath as String
Dim strTable as String
Dim blnHasFieldNames as Boolean' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
Loop
Read Data from EXCEL File via Query (SQL Statement)
Generic SQL statement that reads data from an EXCEL file. Replace C:\MyFolder\MyFile.xls with the real path and filename of the EXCEL file. Replace WorksheetName with the real name of the worksheet -- NOTE that the name cannot be longer than 30 characters (one less than EXCEL's limit for a worksheet name) or else ACCESS / Jet will give you an error stating that the file cannot be found. In this SQL statement, HDR=YES means that the first row of data are header names (change to NO if the first row does not contain header names); IMEX=1 alllows "mixed formatting" within a column (alpha characters and numbers, for example) so that errors will not be raised when importing mixed formats; the $ character must be immediately after the worksheet name; and A2:U66536 is the range of data to be imported (these cell references can be changed to any contiguous range of cells in the worksheet).
SELECT T1.*, 1 AS SheetSource
FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFolder\MyFile.xls].[WorksheetName$A2:U65536] as T1;
Write Data From an EXCEL Worksheet into a Recordset using Automation
Generic code to open a recordset (based on an existing table) for the data that are to be imported from a worksheet in an EXCEL file, and then to loop through the recordset and write each cell's value into a field in the recordset, with each row in the worksheet being written into a separate record. The starting cell for the EXCEL worksheet is specified in the code; after that, the data are read from contiguous cells and rows. This code example uses "late binding" for the EXCEL automation, and this code assumes that the EXCEL worksheet DOES NOT contain header information in the first row of data being read.
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As BooleanblnEXCEL = False
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in read-only mode
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")
' Replace A1 with the cell reference from which the first data value
' (non-header information) is to be read
Set xlc = xls.Range("A1") ' this is the first cell that contains data
Set dbs = CurrentDb()
' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)
' write data to the recordset
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False ' close the EXCEL file without saving any changes
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File
When importing data from an EXCEL spreadsheet into an ACCESS table via the TransferSpreadsheet action, or when linking to an EXCEL spreadsheet as a linked ACCESS table, often you will see the "#Num!" error code for the value in a field in the ACCESS table; or you will see that text strings longer than 255 characters are truncated in a field in the ACCESS table.
The "#Num!" error code that you see is because Jet (ACCESS) sees only numeric values in the first 8 - 25 rows of data in the EXCEL sheet, even though you have formatted the EXCEL column as "Text". In EXCEL, if you change the format from "General" or a numeric format to "Text", the previous numeric format for a cell will "stick" to numeric values. What ACCESS and Jet are doing is assuming that the "text" data actually are numeric data, and thus all your non-numeric text strings are "not matching" to a numeric data type. One way to fix this problem is to insert a ' (apostrophe) character at the beginning of each cell's value for that column in the EXCEL file -- that should let Jet (ACCESS) treat that column's values as text and not numeric. A second way is to insert a dummy row of data as the first row, where the dummy row contains nonnumeric characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as text and not numeric. A third way is to double-click into that EXCEL cell that has , then click on any other cell -- that will "update" the cell to the "Text" format.
The truncated text string that you see is because Jet (ACCESS) sees only "short text" (text strings no longer than 255 characters) values in the first 8 - 25 rows of data in the EXCEL sheet, even though you have longer text farther down the rows. What ACCESS and Jet are doing is assuming that the "text" data actually are Text data type, not Memo data type. One way to fix this problem is to insert a dummy row of data as the first row, where the dummy row contains a text string longer than 255 characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as memo and not text.
It's possible to force Jet to scan all the rows and not guess the data type based on just the first few rows. See this article for information about the registry key (see TypeGuessRows and MaxScanRows information): http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/ [ NOTE: There are some reports by others that this registry key may not work as expected when using Windows XP SP3 or when using ACCESS 2007.]
Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File
After I read many posts in the newgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing to figure out what actually works and what doesn't work when using this argument (NOTE that the use of the Range argument for exports is an UNDOCUMENTED feature in ACCESS).
Here are the results of my tests for your information and entertainment.EXCEL FILE DOES NOT ALREADY EXIST
-------------------------------------------------
If the EXCEL file will be created by TransferSpreadsheet, the Range argument
can be used to create a range in the new file that describes the cells that
contain the exported data on the worksheet. This Range argument also is used
to name the worksheet onto which the exported data are written. This
overrides the normal operation of TransferSpreadsheet, which is to name the
worksheet using the name of the table or query being exported. For example,
this action:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "MyName"
will create the file "C:\Test.xls" and the data will be written onto the
first worksheet, which will be named MyName (the Worksheet.Name property,
not the Worksheet.CodeName property); and the cells into which the data are
written will be a Range named MyName in the new file. This range will
include the field names that are exported as the first row of data, and the
range will begin in cell A1.
EXCEL FILE ALREADY EXISTS
-------------------------------------
The Range argument can be used to identify the actual Range into which the
exported data are written. TransferSpreadsheet ignores worksheet names when
looking for the Range in the workbook file. It looks specifically for a
defined Range of cells.
However, it is in this situation where I believe many posters have run into
problems with this undocumented feature.
1) If the Range exists (cell range, that is) AND if that range encompasses
more than a single cell (at least two cells), the data are exported to that
range of cells. If the number of records and/or fields are more or fewer
than the "size" of the range (number of rows and columns), the data are
correctly exported and the Range is redefined to match the size of the
exported data in terms of width and depth of the range (number of rows and
number of columns). Note that any formatting in the cells within this range
is retained (e.g., Bold, Highlight color, font color, etc.).
2) If the Range does not exist in the workbook file, TransferSpreadsheet
creates a new worksheet, names it with the Range argument value, writes the
data onto that worksheet, and creates a new Range (also named with the Range
argument value)to define the cells that contain the exported data. If a
worksheet with the same name as what is in the Range argument already exists
in the workbook file, the new worksheet that is created is named using
standard EXCEL process, namely, the Range argument name followed by a 1.
Thus, if I use MyName as the Range argument and export to an existing file,
I can get one of the following results:
a) File already contains a worksheet named MyName but does not
contain a Range named MyName: A new worksheet named MyName1 is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.
b) File does not contain a worksheet named MyName and does not
contain a Range named MyName: A new worksheet named MyName is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.
3) If the Range exists (cell range, that is) AND if the Range consists of
a single cell (e.g., A1), then strange things happen -- note that it doesn't
matter if the Range starts in cell A1 or not. And because of these strange
things, this is where the feature is unusable for exporting. I haven't
defined exact "rules" to describe what happens (although it appears that how
far the range is moved appears to be "the original row number plus 93"
columns (if the Range was originally in column A), but here are my
observations in this situation (I won't guarantee that you won't see
different behaviors):
a) If the worksheet name is the same name as the Range name, and
the Range begins in cell A1, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell A1 (with the field names row) -- BUT the existing
range is moved to cell CQ1 (94 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.
b) If the worksheet name is the same name as the Range name, and
the Range begins in cell A5, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell E5 (with the field names row) -- BUT the existing
range is moved to cell CU5 (98 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.
c) If the worksheet name is not the same as the Range name, and
the Range begins in cell A1, the exported data are written to a new
worksheet that is named the same as the Range argument value, and the
existing Range is then moved to cell IV1 (the last column in the sheet) on
that new worksheet, and there is no Range created for the cells that contain
the exported data.