No Such Interface supported when using Access VBA to Merge Excel Cells

[Zedster]

I have an access database which produces monthly management reports by creating an Excel Workbook from Access using VBA. This has been running hunky dory every month for years. Now all of a sudden I am getting the Error:

The standard VBA error code: 1004
The standard VBA error description: No such interface supported

The error occurs when I attempt to merge some cells on line 2730 below:

2720                                        If intColumnCounter < 16 Or intColumnCounter > 19 Then
2725                                            Debug.Print "wsht.Range(Cells(" & intRangeR1 & ", " & intRangeC1 & "), Cells(" & intRangeR2 & ", " & intRangeC2 & ")).Merge"
2730                                            wsht.Range(Cells(intRangeR1, intRangeC1), Cells(intRangeR2, intRangeC2)).Merge
2740                                        End If

Debug.Print has been added to resolve the values of the variables and this translates to:

wsht.Range(Cells(8, 1), Cells(10, 1)).Merge

If I comment out line 2730 the routine runs fine and produces the required workbook, but naturally the cells I wanted merging don't merge.

I am at a loss as to why this is happening all of a sudden and hence equally at a loss as to how to correct it.

Any help appreciated.

 

[Gasman]

Check for Updates, Office and Windows.

 

[bastanu]

Try to fully qualify the wsht variable:

Applying code to active workbook from another workbook error: No such a interface supported

I want below code to open a closed workbook and copy the values from the range StartRow and EndRow to active workbook. I get error 1004 "No such interface supported". on line "xlBook.Sheets(Sh...

stackoverflow.com

Cheers,

 

[Zedster]

I confess to being quite inexperienced in Excel VBA, the methods I use have been created by studying what others have done online, so I may have picked up bad habits.

Earlier in the code I had qualified the wsht vaiable (or so I think!) using

Set wsht = wbk.Sheets(1)

However the link provided by Vlastanu did lead me to a solution.

I created a range object and set the range to the worksheet range I was trying to merge, then merged the range object. That appears to work, but i don't really know why. The new code became:

1810                                        Debug.Print "wsht.Range(Cells(" & intRangeR1 & ", " & intRangeC1 & "), Cells(" & intRangeR2 & ", " & intRangeC2 & ")).Merge"
1815                                        Set rng = wsht.Range(Cells(intRangeR1, intRangeC1), Cells(intRangeR2, intRangeC2))
1820                                        rng.Merge
1825                                        Set rng = Nothing

 

[The_Doc_Man]

The only way to really know what was going on would be in the original (failing) code, put a breakpoint on the code and when it stops, hover the mouse-cursor over various variables to see if any had been dereferenced (i.e. =null). Because that 1004 error in Excel is usually a sign of something not being a valid data reference.

 

[Zedster]

The only way to really know what was going on would be in the original (failing) code, put a breakpoint on the code and when it stops, hover the mouse-cursor over various variables to see if any had been dereferenced (i.e. =null). Because that 1004 error in Excel is usually a sign of something not being a valid data reference.

Yeh I figured that, pretty tedious though in the MS VBA coding environment as there are so many object properties and it can be difficult to see the "wood for the trees".

 

[Gasman]

You are only looking at the trees in your little garden, not the surrounding countryside?
Just what is in your code.