Member Avatar for pingpongplaya

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
  • 258 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by pingpongplaya
Member Avatar for BitBlt

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.

Member Avatar for pingpongplaya

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, networking, learning, and sharing knowledge.