Importing error 1

[reta]

Hi
i am trying to import a file into a table i have used this code before and it has been successful, however when i created a new program a tried to import information from an excel spread sheet i get an error.

Error Description: Application defined-error or object- defined error. run-time error '1004'

Code:

Private Sub Import_Click()
DoCmd.SetWarnings (warningsoff)
'declares variables
Dim fso 'File System Object
Dim strTargetFile, strRootDir, strEntirePath
Dim LastRow As Long
Dim xlobj
Dim wsobj
Dim rngobj
Dim FieldNames
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sCount
Dim Tcount As Integer
If IsNull(Me.txtFileToOpen) Or Me.txtFileToOpen = "" Then
strMsg = "Please enter in file name."
strTitle = "File name required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Tcount = Tcount + 1
Cancel = True
End If
If Tcount <= 0 Then
'setup file Path and name
'strRootDir = "G:\PARTS\Hi-Pack\Dealer Stock Assistance Program (DSAP)\DSAP 2004 11"
strRootDir = Me.txtLocation

strTargetFile = txtFileToOpen.Value & ".xls" 'If user enters file to open in txtbox
strEntirePath = strRootDir & strTargetFile
If Dir(strEntirePath) = "" Then
strMsg = "This file doesnot exists."
strTitle = "File required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle

Else

Set xlobj = GetObject(strEntirePath)
'Not needed unless you want to import a template
'-----------------------
Set fso = CreateObject("Scripting.FileSystemObject")
If Not (fso.FileExists(strEntirePath)) Then
Dummy = fso.CopyFile(strRootDir & "Template\BlankTemplate.xls", strEntirePath, vbTrue)
End If
'----------------------
Set wsobj = xlobj.Worksheets("Sheet1")
'LastRow = 125

LastRow = wsobj.Range("A65536").End(xlUp).Row

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "PartInformation", strEntirePath, True, "A1:AY"


strMsg = "Import complete."
strTitle = "Complete "
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
End If
End If
End Sub


thanks for your help.
Reta

 

[VBslammer]

Try:
[tt]
If wsobj Is Nothing Then
MsgBox "The worksheet is nothing"
Else
LastRow = wsobj.UsedRange.Rows.Count
End If
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[reta]

Thanks VBSlammer, it finally worked.
Reta