If you turn the warnings off, make sure you switch them back on when your code has
finished. This method has an effect throughout your Access application. For example, you
could be experimenting with a query and find that it does not work correctly. You decide to
close it without saving and reload the original query.
If warnings are set to false, there is no warning message when you close the query (asking
if you want to save changes). The default is save so your incorrect query is saved, messing up
your earlier development work.TransferSpreadsheet
TransferSpreadsheetgives a simple means to transfer data between an Excel spreadsheet
and a table. If exporting to a spreadsheet, then a query can also be used as the source.
The syntax is:
DoCmd.TransferSpreadsheet _
(TransferType,SpreadsheetType,TableName,FileName,HasFieldNames,Range,UseOA)The arguments are all optional: TransferType This is acImport or acExport, depending on whether you are bringing
data in from the spreadsheet or pushing it into the spreadsheet from the database.
SpreadsheetType This is an acSpreadsheetType constant so that older versions of
Excel are still supported. Surprisingly, Lotus 123 format is also included.
TableName This is the table that data will be imported into or exported from. If you
have chosen to export, you can also use a query here.
FileName The full name including the path of the spreadsheet file that you are
importing/exporting to.
HasFieldNames True or False to indicate whether the spreadsheet has field names at
the top.
The default is False.
Range This only applies to importing and specifies a range of cells to import. This
argument must be left blank when exporting.
UseOA This argument is not used.TransferText
TransferTextis a useful command to interface with text files. It is very similar to clicking
External Data in the Access menu and then clicking the Text File icon in the Import or Export
group of the ribbon.
Before using this command, it is a good idea to import or export the file manually as a
“dry run” to see what snags you run up against. If it works correctly, you can save your
import or export specification with a particular name and this makes life far easier when
using theTransferTextmethod.218 Microsoft Access 2010 VBA Macro Programming