Reference needed for Range object (1 Viewer)

[Zaeed]

Hi, I'm trying to use the following code, however access is telling me that the user defined variable is not defined.. Now I'm assuming that I am missing a reference for this, however I've got all the ones I can think of selected.. Any ideas?

Code in red is where the error is flagged

Dim strInputFileName As String
Dim objAccess As Object
Dim objExcel As Object
Dim objWorkbook As Object
Dim colWorksheets As Object
Dim objWorksheet As Object
Dim objRange As Object
Dim strWorksheetName As String
[COLOR="Red"]Dim rUsed As Range[/COLOR]
Dim strUsedRange As String

    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    
    Set objWorkbook = objExcel.Workbooks.Open(strInputFileName)
    Set colWorksheets = objWorkbook.Worksheets
    
    Set rUsed = Intersect(Range("A:D"), ActiveSheet.UsedRange)
    strUsedRange = rUsed.Address
    
    For Each objWorksheet In colWorksheets
        Set objRange = objWorksheet.UsedRange
        strWorksheetName = objWorksheet.Name & "!" & strUsedRange
        objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "Stella", strInputFileName, True, strWorksheetName
    Next

References used so far

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects Library
Microsoft Office 12.0 Object Library
Microsoft ADO ext. 6.0 for DDL and Security

Thanks for your help

 

[boblarson]

1. Don't use
Dim rUsed As Range

2. Use
Dim rUsed As Object
instead (since you have the late binding thing going on and that is good so it doesn't depend on a certain version of Excel.

3. The reference you would need is the Microsoft Excel XX.0 Object Library and that would defeat the purpose of late binding.

Next, for this:
Set rUsed = Intersect(Range("A"), ActiveSheet.UsedRange)

Change to this:
Set rUsed = objExcel.Intersect(Range("A"), ActiveSheet.UsedRange)

 

[Zaeed]

Thanks for your help Bob, however it now falls over at

Set rUsed = objExcel.Intersect([COLOR="Red"]Range[/COLOR]("A:D"), ActiveSheet.UsedRange)

Compile Error:
Sub or Function not defined

 

[boblarson]

Thanks for your help Bob, however it now falls over at

Set rUsed = objExcel.Intersect([COLOR=red]Range[/COLOR]("A:D"), ActiveSheet.UsedRange)

Compile Error:
Sub or Function not defined

Oops, missed that one. (Everything has to be connected to the application object somehow. See here for more about that)

Set rUsed = objExcel.Intersect([COLOR=blue][B]objExcel.ActiveSheet.[/B][/COLOR][COLOR=red]Range[/COLOR]("A:D"), ActiveSheet.UsedRange)

 

[Zaeed]

Awesome.. Thanks for your help Bob..Worked like a charm..

(well I got another error, but that's unrelated.)

Thanks

 

[boblarson]

Awesome.. Thanks for your help Bob..Worked like a charm..

(well I got another error, but that's unrelated.)

Thanks

And I just can't see things straight today. You would still need this too:

Set rUsed = objExcel.Intersect(objExcel.ActiveSheet.Range("A:D"), [B][COLOR=red]objExcel.[/COLOR][/B]ActiveSheet.UsedRange)

I missed that one. If you don't attach everything to the objExcel object (you can do it through use of the spreadsheet and workbook objects too, if they are instantiated off of the objExcel object). Otherwise a phantom instance of Excel will start (you can't see it but it appears in your task manager) and you will have to kill it in order to run your code again. If you don't it will likely error out. That's what my article (which the Access Team from Microsoft posted on their blog a while back) goes into.