Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-02
15
Medium Priority
?
438 Views
Last Modified: 2015-01-03
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
Comment
Question by:stephenlecomptejr
  • 8
  • 3
  • 3
15 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40528112
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528158
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40528166
What happens with

For Each cell In xlbook.activesheet.Range(sRange & ":" & sRange2)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40528202
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528243
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40528274
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528297
Rey, this is the same exact code as before.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40528299
no it is not  

 For Each cell In .Range(sRange & ":" & sRange2)
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528309
Sorry Rey, I got that wrong.
It is working.  Still testing though.

Thanks.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528361
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
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528365
Thanks again for the replies on the holidays.  I appreciate it.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40528370
Are you sure you selected the correct answer?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 40528373
No.  That's why I put in a request.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 40529355
Thanks again.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question