I am trying to populate a spreadsheet using Access data, however I am having trouble with the line noted below, which is giving a run-time error. I am not familiar with the Excel Object Model, so not sure how the "selection, range, etc" should be used. Any help and guidance would be appreciated:

Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWBold As Excel.Workbook
        Set xlApp = New Excel.Application
        With xlApp
            .Visible = True
            Set xlWB = .Workbooks.Open("C:\Users\Public\ABC.xls", , False)
            Set xlWBold = .Workbooks.Open("C:\Users\Public\DEF.xls", , False)
            '***Run-time error '91': Object variable or With block variable not set***
            xlApp.Range("B4", Selection.End(xlDown)).Select

            xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
                :=False, Transpose:=False
       End With


Recommended Answers

All 2 Replies

The line after your run-time error should read:

xlApp.Range("B4", xlApp.Selection.End(xlDown)).Select

Either that, or just a simple period...the "Selection" object has to be in a context, whether that be the object in the "With" clause from above, or some other Excel.Application object.

Adding xlApp. worked! Thank you so much for the help!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.