Access 2007 VBA Object Defined Error - Excel Spreadsheet Used Range Import 2

[JimLes]

This used to work in Access 2003, but I am getting an object defined error in Access 2007 on this code:
Set objCell = .Cells(.Rows.Count, "A").End(xlUp).Row


What am I doing wrong?

Thx,




Dim strCurrProjPath As String
Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strXlFileName As String 'Excel Workbook name
Dim strWorksheetName As String 'Excel Worksheet name
Dim objCell As Object 'Last used cell in column
Dim strUsedRange As String 'Used range
Dim FileName As String
Dim objDialog, boolResult

'On Error GoTo Err_ImportEmployeeData

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel Files|*.xlsx; *.xls|All Files|*.*"
objDialog.FilterIndex = 1

boolResult = objDialog.ShowOpen

If boolResult = 0 Then
Exit Sub
Else

'Assign Path and filename of XL file to variable
strXlFileName = objDialog.FileName

'Assign Excel application to a variable
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False 'Can be visible or not visible
objExcel.UserControl = True

'Open the Excel Workbook
Set objWorkbook = objExcel.Workbooks.Open(strXlFileName)

'Assign required worksheet to a variable
With objWorkbook
Set objWorksheet = .Worksheets(1)
End With

With objWorksheet
'Assign worksheet name to a string variable
strWorksheetName = .Name

'Find last used cell in Column A
Set objCell = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Assign used range to a string variable.
strUsedRange = objWorksheet.UsedRange.Address(0, 0)
'Turn off/Close in reverse order to setting/opening.
Set objCell = Nothing
Set objWorksheet = Nothing

'SaveChanges = False suppresses save message
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

'Import the worksheet
DoCmd.TransferSpreadsheet acImport, 8, "tblEmployee", _
strXlFileName, True, strWorksheetName & "!" & strUsedRange

End If
MsgBox "Employee data imported successfully!"


Exit_ImportEmployeeData:
Exit Sub

Err_ImportEmployeeData:
MsgBox Err.Description
Resume Exit_ImportEmployeeData

End Sub

 

[kjv1611]

That should NEVER work, regardless of Access version. I think if you were typing this, you made a typo, or basically selected the wrong item from the dropdown when hitting the dot after (xlUp)

It should be this:

'Find last used cell in Column A
Set objCell = .Cells(.Rows.Count, "A").End(xlUp).[blue][b]Range[/b][/blue]
End With

Also, I don't see the need for using the WITH clauses in your code. I think it just makes it a little more challenging to read, and doesn't help much in any way. Of course, it'll run either way.

And please indent your code appropriately - makes it MUCH easier to read - for you, and anyone else looking at it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Oh... the reason why I say it is a Range is because in Excel, when you refer to Cells, you refer to Ranges.... Each Cell is a Range by itself, just like a Table is a Range, and a Named Range is a Range, a given Selection is a Range.

If you wanted to refer to just the Row or Column number, you'd just need a numeric variable to store that - either Long or Integer. Use Long for Rows for certain, in case there are too many rows in the particular table/list for an integer to handle.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Have you set a reference to the Excel Object Library in Tools > References?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Skip,

This one wouldn't require the Excel Reference, b/c it is added via code.. I always forget what that is called, but declaring an Object, and then telling VBA what specific Object it will be, basically by pointing to the actual dll. In-Line something another... I always end up looking it up, b/c I can never remember what it's called..

Set objDialog = CreateObject("UserAccounts.CommonDialog")

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Well, it'd of been better to mention the Excel one specifically:

[GREEN]'Assign Excel application to a variable[/GREEN]
Set objExcel = CreateObject("Excel.Application")

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

xlUp is an Excel constant that will not resolve withou a reference to the Excel Object Library.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Well, yes, that it is. So, seems like the simplest approach is to give in and use the Reference to Excel rather than trying to work around it.

By the way... I thought I'd play around with it...

Any ideas why this wouldn't work in the same scenario?

Sub TestExcel()
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.ActiveWorkbook
[b]    Set xlWs = xlWb.Worksheets(1)[/b]
    
    Debug.Print xlWs.Cells(1, 1).Value
    Debug.Print xlWs.Cells(xlWs.Rows.Count, "A").End(xlUp).Value
    
End Sub

It runs fine until it gets to setting the Worksheet to the ActiveWorksheet, and at that point, it says that the Workbook object doesn't really exist... error = Object variable or With block variable not set

And of course, if I try the xlUp, it gives an error as well in debugging.

On the xlUp piece, would there be any other way of referencing it without tools - references? Such as something like...

xlApp.xlUp?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I tried Worksheets(1) and ActiveSheet, both gave same error, which is the biggest part making me think there is an issue with the Workbook object.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

You must ADD a Workbook.

There is no Workbook for the Excel Application Object yet!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

If you want to get an already opened workbook, then use GetObject instead of CreateObject.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Oh, now I see... I guess the reason why just using ActiveWorkbook in Excel works is b/c it's already the active instance, whereas if you create a new instance of the application in code, no workbooks are open - just the application. Duh. Thanks for clearing that mud out, Skip.

Thanks for the mention on using the different function, PHV! I don't even recall ever using GetObject... or else so seldom that I've forgotten!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

With objWorksheet
    'Assign worksheet name to a string variable
    strWorksheetName = .Name
    
    '[b][highlight]BTW, you NEVER use this object!!![/highlight][/b]
    Set objCell = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Assign used range to a string variable.
strUsedRange = objWorksheet.UsedRange.Address(0, 0)
'Turn off/Close in reverse order to setting/opening.
Set objCell = Nothing
Set objWorksheet = Nothing

'SaveChanges = False suppresses save message
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}