[AccessD] Linking to an excel spreadsheet

Are you trying to do it with DAO or ADO, John?  I have some ADO code I
wrote way back when to drop and recreate a textlink and it handled XL as
an option.  I've attached it for whatever use it might be.  Don't be
critical, this was early in my use of ADO.

Charlotte Foust

<Code>
Public Function LinkExternalFileADO(ByVal strTextFileName As String, _
                              ByVal strLinkTable As String, _
                              ByVal strLinkSpecRange As String, _
                              Optional blnXL As Boolean) As Boolean
    'Created by Charlotte Foust
    'drops and relinks the temp table
    'and returns a boolean value for
    'the success of the operation
    'last modified 7/5/2001
    'Sample call:  LinkTextFileADO("MyTable.tab","MyTable","MyTable
Spec")
    '
LinkTextFileADO("MySheet.xls","MySheet","MyRange",True)
    
    On Error GoTo Proc_err
    Dim cat As ADOX.Catalog
    Dim cnn As ADODB.Connection
    Dim errsCnn As ADODB.Errors
    Dim errCurr As ADODB.Error
    
    'initialize the return value
    LinkExternalFileADO = True
    
    'initialize the object variables
    Set cnn = CurrentProject.Connection
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = cnn
    Set errsCnn = cnn.Errors
    errsCnn.Clear
    
    'if the file exists, try to link it
    If strLinkTable <> "" Then
    
      'If no path was included, use the current path
      If InStr(strTextFileName, "\") = 0 Then
      
        strTextFileName = CurrentProject.Path & "\" & strTextFileName
      End If 'InStr(strTextFileName, "\") = 0
      
     'if the file exists then ...
      If Dir(strTextFileName) <> "" Then
        On Error Resume Next
        
        'delete the existing table link
        cat.Tables.Delete strLinkTable
        On Error GoTo Proc_err
        
        'link either a text file or an Excel spreadsheet
        If Not blnXL Then
        
          'create a new link to the text file
          DoCmd.TransferText acLinkDelim, strLinkSpecRange, _
            strLinkTable, strTextFileName
            
        Else 'If Not blnXL
        
          'create a new link to the Excel file
          DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
                          strLinkTable, strTextFileName, True,
strLinkSpecRange
        End If 'Not blnXL
        
      Else 'If Dir(strTextFileName) <> ""
      
        'not a valid filename or path
        LinkExternalFileADO = False
      End If 'Dir(strTextFileName) <> ""
    End If
    
Proc_exit:
  'cleanup and exit
  On Error Resume Next
  Set cat = Nothing
  Set errsCnn = Nothing
  Set errCurr = Nothing
  Set cnn = Nothing
  Exit Function
Proc_err:
  If errsCnn.Count > 0 Then
    For Each errCurr In errsCnn
      MsgBox "Error #" & errCurr.Number & "--" & errCurr.Description _
            & vbCrLf & CurrentProject.Name & ".LinkTextFileADO"
    Next errCurr
    errsCnn.Clear
  End If
  If Err <> 0 Then
    MsgBox Err.Number & "--" & Err.Description & vbCr _
            & " in LinkTextFileADO", vbOKOnly
  End If
  LinkExternalFileADO = False
  Resume Proc_exit
End Function 'LinkExternalFileADO(ByVal strTextFileName As String, _
                              ByVal strLinkTable As String, _
                              ByVal strLinkSpecRange As String, _
                              Optional blnXL As Boolean) As Boolean
</Code>


-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Monday, November 22, 2004 9:53 AM
To: 'AccessD at DatabaseAdvisors.com'
Subject: [AccessD] Linking to an excel spreadsheet


Does anyone have code for linking to an excel spreadsheet?

If I manually create a link, the wizard finds all the fields, sets up
with the "first line has field names" etc.  Everything works fine.
However if the table is not linked already, I need to create a tabledef,
set the SourceTableName and Connect properties.  If I then try to append
the tabledef to db.tabledefs I get an error that no fields are defined.
It is obvious that a LOT more is involved here.  It appears that I have
to open the spreadsheet, get the first line, create fields with the data
in the first line, somehow figure out the datatype of each column etc.
Is there a prebuilt code I can call to do all this stuff?  If the wizard
can do it, then the code behind the wizard could do it for me.

Has anyone ever done this?

John W. Colby
The DIS Database Guy

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com