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
383 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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 119

Expert Comment

by:Rey Obrero
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now