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
392 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 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
 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

831 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