CODEWITHSUNDEEP
excelvbams-access
jtchase08
jtchase08

Reputation: 660

Offset selection in Excel from Access

I'm creating an Excel file from data in an Access database.

All I'm currently trying to do is offset a selection I've made in Excel from Access, and I keep receiving errors.

ExcelWS.Range(Range("H6"), Range("H6").End(xlDown)).Offset(0, -1).Select

ExcelWS is an Excel. Worksheet object.

My goal is to select from G6 to the end of the workbook. However, not every cell in column G is populated, so a simple End(xlDown) from cell G6 only takes me part way through the file. That being said, all cells in column H are populated, so I'm able to do an End(xlDown) from cell H6. From there, I want to move my selection one to the left, giving me the desired range in column G selected.

The above code is a product of about 2hrs of googling, and trying tons of different combinations of similar code. However, I'm still receiving an error.

Run-time error '1004':

Method 'Range' of object '_Global' failed

The odd thing is that 1/10 times, I'll receive the desire results. The rest of the time I receive the above error.

I feel as though my issue has something to do with running that line of code from Access, however that's currently my only way of completing this task as I have other things that rely on being able to do it from Access.

Any help would be appreciated, and if there's any more info I can give I'll try my best.

Thanks.

Upvotes: 0

Views: 431

Answers (2)

jtchase08
jtchase08

Reputation: 660

Thanks to @cyboashu, I was able to get it working.

I took the examples he gave me and mushed them together to make it do what I wanted.

I realized after posting the question that it was working every other time, consistently.

Anyway, the code I used is

Dim ExcelR1 As Range
Set ExcelR1 = ExcelWS.Range(ExcelWS.Range("H6"), ExcelWS.Range("H6").End(xlDown)).Offset(0, -1)
ExcelR1.Activate

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

To run Select the sheet shoudl be active/ in -focus.

So you ned to set foucus to sheet. 

ExcelWS.Activate
ExcelWS.Range(Range("H6"), Range("H6").End(xlDown)).Offset(0, -1).Select

Simalarly, to make the seet in-focus, your workbook needs to be active/ infocus.

Try to avoid Select as much as possible. Also fully qualify the names.

 Dim rngTest as Range
 Set rngTest= ExcelWS.Range(ExcelWS.Range("H6"), ExcelWS.Range("H6").End(xlDown)).Offset(0, -1)

Upvotes: 1

Related Questions