Help with defining range when importing workbooks

[Wrathchild]

I used code from post thread705-1006240 and modified for my needs. I keep receiving an error on the TransferSpreadsheet command relating to the range. Error is "Error Encountered - 3011: The Microsoft Jet database engine could not find the object ''Basic_Action'$A1:N19'. Make sure the object exists..." If I remove the range, it actually imports, so something must be screwy with the range. I spent several hours yesterday trying different syntax with the range to no avail. Any thoughts?

Function ImportData()
On Error GoTo Errorhandler
Dim dbs As DAO.Database
Dim i As Integer
Dim oExcel As Excel.Application
Dim oBook As Excel.workbook
Dim oSheet As Excel.Worksheet
Set oExcel = CreateObject("Excel.Application")
Dim counter As Integer
Dim x As Integer
Dim folder As String
Dim strPath As String
Dim tststring As String
Set dbs = CurrentDb

DoCmd.SetWarnings False

With Application.FileSearch
    .NewSearch
    .LookIn = "C:\My Data\"
    .FileName = "PAL-CMS-TP-0002-NewEventWizard_GL_R1v1.05.xls"
    If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
            Set oBook = oExcel.Workbooks.Open("C:\My Data\PAL-CMS-TP-0002-NewEventWizard_GL_R1v1.05.xls")
            counter = oBook.Worksheets.Count
            For x = 1 To counter
                Set oSheet = oBook.Worksheets(x)
                oSheet.Activate
                If InStr(1, oSheet.Name, "Action") > 0 Then
                    Set oSheet = oBook.Worksheets(x)
                    tststring = oExcel.ActiveSheet.Name
                    DoCmd.TransferSpreadsheet , , "test", "C:\My Data\PAL-CMS-TP-0002-NewEventWizard_GL_R1v1.05.xls", -1, "'" & tststring & "'!" & oSheet.UsedRange.Address(False, False)
                End If
            Next x
        Next i
    End If
End With

ExitHere:
DoCmd.SetWarnings True
oBook.Close
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
Set dbs = Nothing
Exit Function

Errorhandler:
DoCmd.Hourglass (False)
MsgBox "Error Encountered - " & Err.Number & ": " & Err.Description, vbCritical, "Error Encountered"
GoTo ExitHere
End Function

 

[lewds]

HI.....

Is the Range defined in the workbook?

Define the Range, use the Range name in the Docmd.TranferSpreadsheet

 

[Wrathchild]

no, shouldn't "UsedRange" use just that? when I check the UsedRange value, it's "A1:N19" which appears ok in the worksheet. First cell isn't blank, so that wouldn't be the issue (found another post to that affect)

 

[Wrathchild]

I assume the $ in the error message is just formatting and isn't actually replacing the ! in the code?

 

[Wrathchild]

found the answer here: thread705-858503

Correct code needs to be:

DoCmd.TransferSpreadsheet , , "test", "C:\My Data\PAL-CMS-TP-0002-NewEventWizard_GL_R1v1.05.xls", -1, [b]range:=[/b]tststring & "!" & oSheet.UsedRange.Address(False, False)