Excel automation help (1 Viewer)

[Acropolis]

I have a system that opens each morning, runs some checks and exports 3 queries to excel files. This all works nicely.

I have tried to add some code to open each of these files, immediately after they are exported to apply some formatting to them, save, close then move onto the next export.

But I am having some problems, it works absolutely fine for the first export, opens, formats, saves closes no problem at all. But it doesn't work for the rest of the files, it opens the file, but then as soon as it comes to do any formatting or anything I get the following error message appear:

"Method 'Cells' of object '_Global' failed"

The message appears for whatever is the first part of the formatting code that is trying to run, I just happened to make note of the above after I had been trying a couple of things, but once it's failed it then fails on all sections you try and run.

Any idea's on where I've gone wrong?

 Sub FormatExport(Filename As String, Qry As String)
 Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range
 Set xlApp = CreateObject("excel.application")
Filename = Filename & ".xlsx"
Set xlWS = xlApp.Workbooks.Open(Filename).Sheets(Qry)
 xlApp.Visible = True
 'Make columns wide and then autofit, change font size.
Range("A1", Range("A1").End(xlToRight)).Select
With Selection
    .ColumnWidth = 150
    .Font.Size = 9
End With
 Cells.Select
Cells.EntireRow.AutoFit
 'Apply filter and set top row to bold
xlWS.Select
xlWS.Rows(1).Font.Bold = True
xlWS.Rows(1).AutoFilter
 'Freeze Panes
xlWS.Activate
Range("A2").Select
xlWS.Activate
xlApp.ActiveWindow.FreezePanes = True
 'Save and close
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
 'Tidy up Excel
Set xlApp = Nothing
Set xlWS = Nothing
Set xlRng = Nothing
 End Sub

Thanks

 

[Trevor G]

I don't see any mention of looping or the other workbook names. Where are the names stored.

What code are you using to export the queries.

 

[Acropolis]

the exports are run from another part of the code, after each export has run this code is called, passing the filename (filename) and the sheet name within the excel file (qry) to this code.

All the exports are working fine, they have been running for months with no problems at all, it's just the excel formatting that is causing the problem, it work fine the first time it is called, but then fails when it's called a second time, don't know about the third time as it never gets that far.

 

[arnelgp]

Sub FormatExport(Filename As String, Qry As String)
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range
Set xlApp = CreateObject("excel.application")
Filename = Filename & ".xlsx"
Set xlWS = xlApp.Workbooks.Open(Filename).Sheets(Qry)
xlApp.Visible = True
'Make columns wide and then autofit, change font size.
Range("A1", Range("A1").End(xlToRight)).Select
With Selection
.ColumnWidth = 150
.Font.Size = 9
End With
Cells.Select
Cells.EntireRow.AutoFit
'Apply filter and set top row to bold
xlWS.Select
xlWS.Rows("1:1").Font.Bold = True 'set rows("1:1")
xlWS.Rows("1:1").AutoFilter
'Freeze Panes
Range("A2").Select
xlApp.ActiveWindow.FreezePanes = True
'Save and close
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
'Tidy up Excel
Set xlApp = Nothing
Set xlWS = Nothing
Set xlRng = Nothing
End Sub

 

[spikepl]

range, selection, cells all need to refer to the specific excel object. Since they don't you have a rogue Excel instance left over after the first round.

 

[Acropolis]

This is where I start struggling a bit, don't quite understand the object part

Which bit needs to be as an object for this to work?

 

[Acropolis]

I have modified the code now, and it's requirements, it now runs for more reports each morning, and a specific one on a Monday morning.

Everything was working fine all last week, until it ran this morning for the extra report, then I had a similar problem with it.

All the normal reports ran just fine, but there are two reports that require conditional formatting to be applied, this is where the "Conditional" comes from, as that indicates which formatting needs to be applied.

All first five reports ran and were updated as they should be no problem, first report that needs conditional formatting ran fine and was formatted as it should be, but the second one hung as per previous.

I am at a loss with it now, could someone more knowledgeable than me have a look and offer up some help please?

It failed on the Case 1 in the select, this was the second time of calling it for conditional formatting. Once it had thrown the error, I ran it a second time from the immediate window pass it the values and it worked beautifully without a hitch.

 Sub FormatExport(Filename As String, Qry As String, Conditional As Integer)
 Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Worksheet
Dim xlRng As Range
Dim i As Integer
Dim Valuex As String
 Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(Filename)
Set xlWS = xlWB.Sheets(Qry)

 xlWS.Rows(1).ColumnWidth = 150
xlWS.Rows(1).AutoFilter
xlWS.Cells.Select
xlWS.Cells.EntireRow.AutoFit
xlWS.Cells.EntireColumn.AutoFit
xlWS.Cells.Font.Size = 9
xlWS.Cells.HorizontalAlignment = xlCenter
xlWS.Range("D2").Select
xlApp.ActiveWindow.FreezePanes = True
 'Conditional formatting
Select Case Conditional
    Case 1 'Audit report
        xlApp.ActiveWindow.Activate
        xlWS2.Activate
        i = xlWS.Range("F1", Range("F1").End(xlDown)).Rows.Count
'        i = 9
        If Not i = 1048576 Then
            Do Until i = 0
                'Set xlRng = xlWS.Range("F1").Offset(i, 0) '.Select
                'xlWS.Range("F1").Offset(i, 0).Select
                
                xlWS.Range("F" & i).FormatConditions.AddIconSetCondition
                xlWS.Range("F" & i).FormatConditions(xlWS2.Range("F" & i).FormatConditions.Count).SetFirstPriority
                With xlWS.Range("F" & i).FormatConditions(1)
                    .ReverseOrder = True
                    .ShowIconOnly = False
                    .IconSet = xlApp.ActiveWorkbook.IconSets(xl3Triangles)
                End With
                        
                Valuex = "=$N$" & i + 1
                With xlWS.Range("F" & i).FormatConditions(1).IconCriteria(2)
                    .Type = xlConditionValueNumber
                    .Value = Valuex
                    .Operator = 7
                End With
    
                Valuex = "=$O$" & i + 1
                With xlWS.Range("F" & i).FormatConditions(1).IconCriteria(3)
                    .Type = xlConditionValueNumber
                    .Value = Valuex
                    .Operator = 5
                End With
                
                i = i - 1
            Loop
        End If
        
    Case 2 'Weekly meter comparison report
'        xlApp.ActiveWindow.Activate
        xlWS.Activate
        Set xlRng = xlWS.Range("H:H")
        
        xlRng.FormatConditions.AddIconSetCondition
        xlRng.FormatConditions(xlRng.FormatConditions.Count).SetFirstPriority
         With xlRng.FormatConditions(1)
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = xlApp.ActiveWorkbook.IconSets(xl3Triangles)
        End With
         xlRng.FormatConditions(1).IconCriteria(1).Icon = xlIconGreenUpTriangle
        
        With xlRng.FormatConditions(1).IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = -1
            .Operator = 5
            .Icon = xlIconYellowDash
        End With
        
        With xlRng.FormatConditions(1).IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 0
            .Operator = 5
            .Icon = xlIconRedDownTriangle
        End With
    
        Set xlRng = xlWS.Range("I:I")
         xlRng.FormatConditions.AddIconSetCondition
        xlRng.FormatConditions(xlRng.FormatConditions.Count).SetFirstPriority
        
        With xlRng.FormatConditions(1)
            .ReverseOrder = False
            .ShowIconOnly = False
            .IconSet = xlApp.ActiveWorkbook.IconSets(xl3Triangles)
        End With
        
        With xlRng.FormatConditions(1).IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = -5
            .Operator = 7
        End With
        
        With xlRng.FormatConditions(1).IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 5
            .Operator = 5
        End With
        
        xlApp.ActiveWindow.Activate
        xlWS.Activate
        i = xlWS.Range("J1", Range("J1").End(xlDown)).Rows.Count
'        i = 9
        If Not i = 1048576 Then
            Do Until i = 0
                'Set xlRng = xlWS.Range("F1").Offset(i, 0) '.Select
                'xlWS.Range("F1").Offset(i, 0).Select
                
                xlWS.Range("J" & i).FormatConditions.AddIconSetCondition
                xlWS.Range("J" & i).FormatConditions(xlWS.Range("J" & i).FormatConditions.Count).SetFirstPriority
                With xlWS.Range("J" & i).FormatConditions(1)
                    .ReverseOrder = True
                    .ShowIconOnly = False
                    .IconSet = xlApp.ActiveWorkbook.IconSets(xl3Triangles)
                End With
                        
                Valuex = "=$Q$" & i + 1
                With xlWS.Range("J" & i).FormatConditions(1).IconCriteria(2)
                    .Type = xlConditionValueNumber
                    .Value = Valuex
                    .Operator = 7
                End With
    
                Valuex = "=$R$" & i + 1
                With xlWS.Range("J" & i).FormatConditions(1).IconCriteria(3)
                    .Type = xlConditionValueNumber
                    .Value = Valuex
                    .Operator = 5
                End With
                
                i = i - 1
            Loop
        End If
        xlWS.Name = "WC " & Format(TempVars("tmpDateCurrent"), "dd-mm-yy") & " vs WC " & Format(TempVars("tmpDatePrevious"), "dd-mm-yy")
        xlWS.Range("D1") = "Vol WC " & Format(TempVars("tmpDatePrevious"), "dd-mm-yy")
        xlWS.Range("E1") = "Rank WC " & Format(TempVars("tmpDatePrevious"), "dd-mm-yy")
        xlWS.Range("F1") = "Vol WC " & Format(TempVars("tmpDateCurrent"), "dd-mm-yy")
        xlWS.Range("G1") = "Rank WC " & Format(TempVars("tmpDateCurrent"), "dd-mm-yy")
        
End Select
 'Save and close
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
 'Tidy up Excel
Set xlApp = Nothing
Set xlWS = Nothing
Set xlWB = Nothing
Set xlRng = Nothing
Set xlWS2 = Nothing
 End Sub

 

[Cronk]

Try activating the worksheet before doing anything with it.
eg After set objWS=...
Add objWS.activate

 

[Acropolis]

I can throw it in there, but the only place that doesn't have it already is where it is working absolutely fine, it doesn't fail until the Select Case statement, and then only on the second time of calling, and .activate is in there, it goes past the .activate and fails after it.