Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 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