Method cells of object _Global failed

[redrob28]

Hi

I have built a bit of code to e-mail a series of Excel files
which hold data from different queries.I have built the basic
e-mailing into a function and embedded a call to Excel in order
to format the spreadsheet as I am using DoCmd.TransferSpreadsheet
which doesn't retain any formatting.

I have included the function code below with a dummy sub which
calls the function twice, using the same values for each variable.

The first time it runs through it works perfectly. You will see there is
a second call to the function. When it tries the second time I get
an error on the line "Cells.EntireColumn.Autofit". It errors out with
Method Cells of Object _Global failed. I am struggling to work out
why the code works on the first pass through but won't on a second
loop through. Any ideas welcome.

  Sub Test()
 
   Dim DF As String, TM As String, cm As String, sm As String
 
   DF = "TEST Exeter_MH_Email_Data"
   TM = "xxx ' site won't let me put real addresses!
   cm = "yyy" ' site won't let me put real addresses!
   sm = "Test"
 
   Test_Email_data DF, TM, cm, sm
 
   Test_Email_data DF, TM, cm, sm
 
    End Sub
 
Function Test_Email_data(DataFile As String, To_mail As String, CC_mail As String, Subj_mail As String)
 
If FileORDirExists = True Then
   Kill "C:\CAB43\" & DataFile & ".xls"
End If
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
DataFile, "C:\CAB43\" & DataFile & ".xls", True
 
Dim xlApp As Object
Dim xlWkb As Object
Dim xlSht As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWkb = xlApp.Workbooks.Open("C:\CAB43\" & DataFile & ".xls")
Set xlSht = xlApp.ActiveWorkbook.Worksheets(1)
 
 xlApp.DisplayAlerts = False
 xlApp.Interactive = False
 xlApp.ScreenUpdating = False
 xlSht.Activate
 
'code to format spreadsheet before e-mailing 
    With xlSht
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    End With
 
    Set Crng = ActiveCell.CurrentRegion
    RowCount = Crng.Rows.Count
    Colcount = Crng.Columns.Count
 
    Range(Cells(1, 1), Cells(RowCount, Colcount)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
         .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
         .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
         .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
         .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
 
   xlApp.ActiveWorkbook.Save
   xlApp.DisplayAlerts = True
   xlApp.Interactive = True
   xlApp.ScreenUpdating = True
   xlWkb.Close
   xlApp.Quit
 
   Set xlSht = Nothing
   Set xlWkb = Nothing
   Set xlApp = Nothing
 
Set Olk = CreateObject("Outlook.Application")
Set Itm = Olk.CreateItem(olMailItem)
 
 With Itm
      .To = To_mail
      .CC = CC_mail
      .Subject = Subj_mail
      .Attachments.Add ("C:\CAB43\" & DataFile & ".xls")
      .Body = "Please find attached details of new UBRN(s). " _
               & vbCrLf & vbCrLf & "Helpdesk Team"
 
      .Send
 End With
 
Set Itm = Nothing
Set Olk = Nothing
Kill "C:\CAB43\" & DataFile & ".xls"
End Function

 

[pbaldy]

It's only a guess, but if it doesn't fix this it will probably fix a problem you may have had with a hanging Excel process. Note this done correctly:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous

and this not done correctly:

With xlSht
Cells.EntireColumn.AutoFit

See the difference? If not, if you didn't use the With block, how would it look?

 

[redrob28]

Thanks for the reply.

Yes I didn't have that in originally and can see the difference.
I added it in a bit in desperation after getting this error previously.
Taking it out doesn't stop the problem.

 

[pbaldy]

You do the same thing elsewhere, so I'd start with fixing all of them. Here's the issue I'm referring to:

http://blogs.office.com/b/microsoft...-objects-when-referring-to-excel-in-code.aspx

 

[redrob28]

Ah right - thanks for the link.
I suspect that article is spot on.
I am more used to coding in Excel where the reference to the Workbook
or Sheet would be superfluous. I will try putting them all back in to be
more explicit and see if this resolves it.
I did have issues with Excel hanging and having to close it down
via Task Manager to do anything thereafter so this seems to make sense.

Thanks for your help.

 

[pbaldy]

No problem; I hope that resolves the original issue, but post back if not.