0

Hello,

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)
            xlWBold.Activate
            xlApp.Range("B4").Select
            
            '***Run-time error '91': Object variable or With block variable not set***
            xlApp.Range("B4", Selection.End(xlDown)).Select

            xlApp.Selection.Copy
            xlWB.Activate
            xlApp.Range("D8").Select
            xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
                :=False, Transpose:=False
       End With

Thanks.

2
Contributors
2
Replies
4
Views
5 Years
Discussion Span
Last Post by pingpongplaya
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.