Error 3011 When linking a spreadsheet - Xtreme Visual Basic Talk

01-27-2005, 05:37 AM

Rob81don

Freshman

Join Date: Sep 2004

Posts: 41

Error 3011 When linking a spreadsheet

Hi All,
I'm trying to set up a database which links tables automatically so there are about 30 data sources i have to refresh on a fortnightly basis. The problem is i want to link a spreadsheet where each sheet is a table and when i specify the sheet in the range section of the transferspreadsheet method it tells me that it can't find it or its spelt incorrectly which i know it isn't i've checked dozen times.
I've used a table for the code to get the table name, file name and sheet name.

Below is the code i'm using. Does anyone know what is wrong with this stupid thing because it's doing my head in?

Many Thanks

Code:

Option Compare Database
Option Explicit

Private db As Database

Public Sub MainIS()

    Set db = CurrentDb
    ImportTablesIS "_ImportTables"

End Sub

Private Sub ImportTablesIS(strTableNames As String)

    Dim rs As Recordset

        Set rs = db.OpenRecordset( _
            "SELECT Table_Name, Import_Spec, Location, Presented, Format, Sheet, Last_Completed " & _
            "FROM [" & strTableNames & "] " & _
            "WHERE Skip = False AND (Left([Table_Name], 3) = 'P2P') " & _
            "ORDER BY Seq_No ASC;")
        
        Do Until rs.EOF
        
            With Application.FileSearch
                .NewSearch
                .FileName = rs!Location
            
            If .Execute(msoSortByLastModified, msoSortOrderAscending) > 0 Then
            
            If rs!Format = "Notepad" Then
            
                DoCmd.TransferText acLinkDelim, rs!Import_Spec, rs!Table_Name, .FoundFiles(1), True
            
                rs.Edit
                rs!Last_Completed = Now
                rs.Update
                rs.MoveNext
                
            ElseIf rs!Format = "Excel" Then
                
                DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, _
                rs!Table_Name, rs!Location, True, rs!Sheet
                
                rs.Edit
                rs!Last_Completed = Now
                rs.Update
                rs.MoveNext
                
            End If
            End If
            End With