• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Need help with late binding on an Access VBA script manipulating Excel's to delete columns and unmerge cells!

Please note the code that I have late binding and trying to unmerge cells and then delete them.
I have an error that states:  Run-time error '91':  Object variable or With block variable not set.
on line:  For Each cell In Range(sRange & ":" & sRange2)    <--- obviously I'm aware that Dim cell as Object is not going to work and that I may have to set it to something.

I'm not sure the syntax that I need to change it though so that it will work.
The only code I could find to unmerge without late binding I could find was this one:

For Each Cell In Range("A1:Z10")
        If Cell.MergeCells = True Then
            Cell.UnMerge
            Cell.ClearContents
        End If
    Next

But this is not done in Excel vba but Access VBA...

Please help!


'sExcel is the full path of the Excel file.

Public Sub FixExcel(sExcel as String)

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet  As Object
Dim cell As Object
Dim Range As Object

Set xlApp = CreateObject("Excel.Application")
'Opens Excel TEMPlate always from the C:\ to reduce traffic
Set xlBook = xlApp.Workbooks.Open(sExcel)

Dim k as Long
Dim sRange, sRange2 as String

k = 8

sRange = Col_Letter(k)
sRange2 = Col_Letter(16)

With xlApp

  ' deletes columns 8 through 16
  For Each cell In Range(sRange & ":" & sRange2)
     If cell.MergeCells = True Then
        cell.UnMerge
        cell.EntireColumn.Delete Shift:=-4159 'xlToLeft
     End If
  Next
End With
xlBook.Save
xlBook.Close
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

Function Col_Letter(ByVal ColumnNumber As Long) As String

  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    Col_Letter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    Col_Letter = Chr(ColumnNumber + 64)
  End If

End Function

Open in new window

0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 8
  • 3
  • 3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try
Dim xlApp As Excel.Application
  Dim xlBook As Workbook
  Dim xlSheet  As Worksheet
  Dim cell As Range
  Dim Range As Range

and

For Each cell In xlbook.activesheet.Range(sRange & ":" & sRange2)
0
 
stephenlecomptejrAuthor Commented:
Syed,

I'm not able to set the variables this way cause I'm using late binding.
I deal with various machines with different versions of Microsoft Excel.
0
 
Saqib Husain, SyedEngineerCommented:
What happens with

For Each cell In xlbook.activesheet.Range(sRange & ":" & sRange2)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
try this revision


With xlApp

  ' deletes columns 8 through 16
  For Each cell In .Range(sRange & ":" & sRange2)
     If .cell.MergeCells = True Then
        .cell.UnMerge
        .cell.EntireColumn.Delete Shift:=-4159 'xlToLeft
     End If
  Next
End With
0
 
stephenlecomptejrAuthor Commented:
Syed, it never makes it to that line.
It dies at Dim xlApp As Excel.Application
again I do not have any references to Excel cause there are differing version of Excel - I have to use late binding.

Rey, That did not work as well.
I get a yellow highlight on :

If .cell.MergeCells = True Then

Please note image attachment.
error.png
0
 
Rey Obrero (Capricorn1)Commented:
try this

With xlApp

   ' deletes columns 8 through 16
   For Each cell In .Range(sRange & ":" & sRange2)
      If cell.MergeCells = True Then
         cell.UnMerge
         cell.EntireColumn.Delete Shift:=-4159 'xlToLeft
      End If
   Next
 End With
0
 
stephenlecomptejrAuthor Commented:
Rey, this is the same exact code as before.
0
 
Rey Obrero (Capricorn1)Commented:
no it is not  

 For Each cell In .Range(sRange & ":" & sRange2)
0
 
stephenlecomptejrAuthor Commented:
Sorry Rey, I got that wrong.
It is working.  Still testing though.

Thanks.
0
 
stephenlecomptejrAuthor Commented:
Ok rey,

So my problem is  that it will delete the merged cells but not delete the columns.

But I think you've answered my question anyway.
I've overcome the late binding.

I thought that I needed to delete the merged cell before deleting column but I was wrong.

This is the code I used to delete the columns:

If k < 8 Then k = 8
            
              sRange = Col_Letter(n)
              sRange2 = Col_Letter(16)
              sRangeNew = sRange & ":" & sRange2
              xlSheet.Columns(sRangeNew).Delete
              

Open in new window

0
 
stephenlecomptejrAuthor Commented:
Thanks again for the replies on the holidays.  I appreciate it.
0
 
Saqib Husain, SyedEngineerCommented:
Are you sure you selected the correct answer?
0
 
stephenlecomptejrAuthor Commented:
No.  That's why I put in a request.
0
 
stephenlecomptejrAuthor Commented:
Thanks again.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now