Part III: More-Advanced Access Techniques
602
Customers. If you want to connect to a dBASE file named NewEmp.dbf located in the root
directory of C:, you tell Access that the DATABASE is C:\ and the SourceTableName is
NewEmp.dbf.The AttachExcel() function (listed below) shows you how to connect to a named range within
an Excel spreadsheet. To connect to a spreadsheet, you have to specify what kind of spreadsheet it
is, where the spreadsheet file exists, and the range you want to connect to. You can use either a
named range or a range of cells (such as A1:B20). You can also tell Access that the spreadsheet
you’re connecting to contains field names in the first row. The default for this parameter is Yes.To use the AttachExcel() function, the calling procedure must pass the spreadsheet name, the
new name for the Access table, and a valid Excel range name. To attach the range named Names
from the spreadsheet EmpList.xls and give the attached table the name ExcelDemo, use the
following statement:Call AttachExcel(“Emplist.xls”, “ExcelDemo”, “Names”)On the CD-ROM
The following function is located in the basAttachExcel module in Chapter16.accdb on the book’s
companion CD-ROM.
The AttachExcel() function returns a Boolean value reporting whether the Excel file was suc-
cessfully attached (True) or not (False):Function AttachExcel( _
ByVal sFileName As String, _
ByVal sTableName As String, _
ByVal sRangeName As String _
) As Boolean
Const conCannotOpen = 3432
Const conNotRange = 3011
Const conTableExists = 3012
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim sConnect As String
Dim sMsg As String
Dim sFunction As String
On Error GoTo HandleError
AttachExcel = False
sFunction = “AttachExcel”
‘ Check for existence of worksheet:
sFileName = CurDir() & “\” & sFileName
‘ If the file isn’t found, notify
‘ the user and exit the procedure:
If Len(Dir(sFileName)) = 0 Then
MsgBox “The file “ & sFileName _
& “ could not be found”
MsgBox “Please move the file to “ _