Runtime Error 1004

[Thompyt]

I get a runtime error if I close excel and try to run the same sub again. It opens a blank Excel spreadsheet, the errors on the red bold code.

Runtime Error 1004 Method of 'Sheets' of object'_Global' failed

    Set xlApp = New Excel.Application      xlApp.Visible = True


    Set WkBkA = xlApp.Workbooks.Add                           ' Add a new workbook
    Set WKSht = WkBkA.Sheets(1)                                 
        Sheets(1).Name = "ROM_" & TODA                  ' Name Sheet Tab

I have tried adding Before the above code. Its already at the end of the code.

        Set RU = Nothing        Set WKSht = Nothing
        Set WkBkA = Nothing
        Set xlApp = Nothing

If I run

Dim sKillExcel As String    sKillExcel = "TASKKILL /F /IM Excel.exe"
    Shell sKillExcel, vbHide

killing all Excel in memory.

I get Runtime error 462 The remote machine or server does not exist or is unavailable at the same red bold code.

One would think that Set xlApp = New Excel.Application would open a new Excel allowing the code to run. Albeit it's not a good thing to do if you want other excel sheets open at the same time.

    Set xlApp = New Excel.Application      xlApp.Visible = True


    Set WkBkA = xlApp.Workbooks.Add(strFilePath & "ROM_" & TODA)    ' Add a new workbook
    Set WKSht = WkBkA.Sheets(1)
        WkBkA.Sheets(1).Name = "ROM_" & TODA                        ' Name Sheet Tab
        WkBkA.Sheets("ROM_" & TODA).Activate
        WkBkA.Sheets(1).Columns("A:B").ColumnWidth = 20

Works until I hit

Range("B2:E2").Select
Selection Merge

and requires

WkBkA.Sheets(1).Range("B2:E2").Merge

Then cannot find object variable with

With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .FontStyle = "Bold"
    End With

The more I dig into this the worse its getting. It was fine with the exception of erroring out every other run of the sub with the runtime error 1004.

[Minty]

Access VBA has zero knowledge of what to do with

Range("B2:E2").Select
Selection Merge

You have to tell it that you are referring to the excel app and worksheet object which is why this does work:

WkBkA.Sheets(1).Range("B2:E2").Merge

You can reduce this to

WKSht.Range("B2:E2").Merge

Because you have told Access that WKSht is the excel worksheet object earlier in your code.

You have to be explicit and refer to the worksheet object everytime you reference a Excel object that is part of the worksheet.
It does make it a bit long winded. For instance see how the line below has to constantly refer to the xlWS object:

xlWS.Range(xlWS.Cells(2, 2), xlWS.Cells(2, 2)).HorizontalAlignment = xlCenter

[Thompyt]

This

    Range("O11:O12").Select
    ActiveCell.FormulaR1C1 = "Total EWW Hours3"
    ActiveCell.Characters(Start:=1, Length:=15).Font.FontStyle = "Regular"
    With ActiveCell.Characters(Start:=16, Length:=1).Font
        .FontStyle = "Regular"
        .Superscript = True
    End With

To this

    WKSht.Range("O11:O12") = "Total EWW Hours3"
    WKSht.Range("O11:O12").Characters(Start:=1, Length:=15).Font.FontStyle = "Regular"
    WKSht.Range("O11:O12").Characters(Start:=16, Length:=1).Font.Superscript = True

What a PIA. Thanks I am making all 1 liners with Wksht.

[Micron]

I don't see what post 3 has to do with post 1. I was thinking that this code is in Excel but who knows. It's all sketchy, including TODA, which you don't reveal what that is. Seems like the original problem is based on incorrect concatenation based on that.

[Thompyt]

It's in Access Exporting to Excel since that is what the boss wants. A report to PDF is much simpler.

If I didn't use wksht. it would error out on a

With Selection
.........
End With

At the With Selection.

In a query
TODA: IIf(Format([TDYStart],"mmm")=Format([TDLStop],"mmm"),Format([TDYStart],"dd") & "-" & Format([TDLStop],"ddmmmyyyy"),Format([TDYStart],"ddmmm") & "-" & Format([TDLStop],"ddmmmyyyy"))