Run-time error 91, but not everytime the code runs

[Micron]

Or is my best bet to implement the code that June7 posted

Yes.

so that if the code is run a second time, all instances of Excel are automatically closed?

That's not what it does. If it's not already open, that method returns an error so it opens an instance. If you leave the On Error Resume Next as is, any code in that module which comes after will do exactly that, and may not let you know there was an error. After that example, I would reset the error handling as appropriate.

Not sure posting the entire module will help anyone follow the flow or trouble shoot. I think we're at the point where a zipped copy of the db and the workbook is needed (with instructions on how to replicate the problem) but I will at least take a look at the code later.

P.S. I don't think you need the line. If a variant isn't set to Null (at the beginning like that) it's Null by default. However, if repeated execution of the code could cause its value to be altered (say it was a global variable) then it might be important. That's why global variables can be "dangerous" and you have to know how to ensure they are valid at any given point. Sometimes declaring them as Static is a good idea.

[mcomp72]

Another thing I want to mention: before I added in the section of code to do with sorting the Excel file, I wasn't receiving any errors. Does it seem strange to anyone that it is only the sorting that is bringing out the errors? Why isn't the writing of the information into the cells of the Excel file also creating the same errors?

[mcomp72]

If you leave the On Error Resume Next as is

I'm confused. I don't have that line in my code anywhere. I just did a search of the entire module for the word "Error" and it did not find it. Are you saying I should add it to my code?

[Micron]

We're talking about the example code, not your code.

Does it seem strange to anyone that it is only the sorting that is bringing out the errors? Why isn't the writing of the information into the cells of the Excel file also creating the same errors?

You have a good point that might be very helpful. Will consider it when reviewing the code later. Gotta run.

[mcomp72]

I appreciate you being willing to look at the code. One note: you should ignore the comment lines that say this:

'now i need it to scroll through each record in the query and check to see if this record is already on the log.
'if it's not, it should write it to next empty row.

I changed what the code there did, but I forgot to change the comment lines. The comments lines that should have replaced those are:

'this next section goes through the existing items on the log (if there are any) to see if it's
'the same timecard record as the current one in the Recordset.

[ssanfu]

I would suggest deleting the dot before "Activesheet"

  .ActiveSheet.Sort.SortFields.Clear
  ^

(6 places) in the code (Sub AddPayrollData).
A few months ago, I was trying to automate Excel from Access and used "Activesheet" and "ActiveCell" without a dot at the beginning. Don't remember much about the code, but didn't have errors. (Its worth a try)








-------------------------------

Would it not be easier to sort the data before it leaves access

andy49: technically it might be, but I have no idea how to do it in Access, thus I'm trying to do it in Excel. I know how to do it in Excel, obviously, but can't figure out why it is giving me this error sometimes.

Try setting the sort order in the saved query "Q_PayrollLog".
I don't know how many different "JobID"s are in the query "Q_PayrollLog", but if you could limit the "JobID"s in the query to 1, the code will complete sooner.

-------------------------------
For the file picker, this is the code I use

    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Title = "Please Select the Excel File To Import"
        .AllowMultiSelect = False
        .InitialFileName = "some folder path"
        .InitialView = msoFileDialogViewList
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx", 1

        If .Show Then
            sExcelFileName = .SelectedItems(1)
        Else
            MsgBox "You chose cancel"   'didn't choose anything (clicked on CANCEL)
            Exit Sub
        End If
    End With

Note that there is not a loop to get the selected file name. (MultiSelect is set to FALSE)

[mcomp72]

I just so happened to see in the Similar Threads section of this page a topic that sounded eerily similar to mine.

https://www.accessforums.net/showthread.php?t=37116

Turns out, the person was doing something similar to me, and running into the same error. He discovered that it was the CreateObject part of the command that set the object to the Excel Application [in my case, the line is Set XL = CreateObject("Excel.Application")] that was causing the error the second time because it had already been created the first time the code ran. Correct me if I'm wrong, because he didn't actually list the code that he change it to, but it sounds like instead he used Set XL = "Excel.Application".

If that's correct, then it sounds like if I added something similar to the below, it might fix the problem.

If [object already exists] Then
     Set XL = "Excel.Application"
Else 
     Set XL = CreateObject("Excel.Application")
End If

Anyone have any idea what specifically I would type between the brackets to make this work? I'm not experienced enough yet with VBA to know how I would check to see if an object has already been created.

One note: at the end of my code I do have a line that says Set XL = Nothing (and also Set WB = Nothing, which is the Object for my Workbook), so if it's true that the object still exists between running of the code, anyone know why that would be?

With all this said, I am still puzzled by the fact that some of the code functions ALL the time, while it seems to only be the sorting that is causing the problem. I wonder if it has something to do with using the term "ActiveSheet"? That's the only thing that seems to be different in these sorting commands -- though the error is happening on the second line that is present in, not the first.

[ssanfu]

I have several "reports" that I export to Excel, then use automation to edit - add rows, add formulas, etc and never have errors, but I close Excel after edits are complete.

If it helps, this is the beginning and ending code I use to open/edit a worksheet:

   On Error GoTo Err_Handler
'   Dim lngColumn As Long
   Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
   Dim blnEXCEL As Boolean
   Dim xlCol As Long
   Dim xlRow As Long
   Dim i As Integer

   blnEXCEL = False

   ' Establish an EXCEL application object
   On Error Resume Next
   Set xlx = GetObject(, "Excel.Application")   '<<< notice the comma before "Excel"
   If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
   End If
   Err.Clear
   On Error GoTo 0


       '    Do things


    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.Close True   ' close the EXCEL file and save the new data
    '     xlw.Save   ' just save the new data - untested!!!!
    DoEvents         ' gives Windows time to save the new data
   ' blnEXCEL = FALSE   ' do not shut down Excel
    Set xlw = Nothing
    If blnEXCEL = True Then
        xlx.Quit
    End If
    Set xlx = Nothing
End Sub

If you didn't want to close Excel, you could add "blnEXCEL = FALSE" after the doevents and Excel would not close. You might also have to change

xlw.Close True   ' close the EXCEL file and save the new data

to

xlw.Save

but I have not tested this.

[Micron]

Do I understand the order of execution?
1- Sub CreatePayrollLog(JobID As Integer) which calls
2 - OpenPayrollLogExcelFile
No clue as to when the function at the end is called - probably not important for now. I have all caps in my code notes so as to see them easier in Notepad and I would like to copy and paste from there, so I'm not yelling, OK? Some of these comments may pinpoint the problem so read all the code before acting on anything. I divided the code via **** so I could compartmentalize it.

After wading through the code, consider the concept of Activeworkbook (or sheet, or whatever). If for any reason the workbook being controlled by this code isn't the active object, you should expect problems. First, sometimes users can interfere, making it not the active object. Or your code can do this (because you didn't close it for example). Or sometimes an object isn't "active" if it hasn't been "activated" via code. Elsewhere in this forum in a recent Excel question, it was pointed out that each necessary object should be explicitly created. You're not doing this, which may be causing you to not reference the hierarchy of the object model for Excel (pointed out in code comments, I think). Very important take away; activeworkbook or activesheet or range are not children of the application object AFAIK. I also agree that declaring as Object isn't good enough. Should be Excel.Application - see https://www.accessforums.net/showthread.php?t=69106 posts 3 and 7. Apparently what I pointed out re individual objects was covered by the link in post 3, which I didn't visit. Obviously, correct hierarchy is important regardless.

Lastly, I am not a big fan of so much code nested in a With block. You have the most I've ever seen. I recommend trying to minimize (not eliminate) a whole lot of If's, Do's, For's and more With's like you have. Makes it very hard to follow. When you get this working, months from now if you have to look at this again, it won't be so fresh. I spent about 2 hours sifting through the maze. Not really any corrections by me; mostly comments. Now for the code...

Option Compare Database
Option Explicit

Dim XL As Object
Dim WB As Object
Dim FileToOpen As String
'***************************************************************************************************************************
Sub CreatePayrollLog(JobID As Integer) 'WHAT PROVIDES JOBID?
Dim HighlightYellow As Boolean
Dim Answer As Variant

HighlightYellow = False
Answer = MsgBox("Would you like any existing entries on the log to be highlighted in yellow?", vbYesNo + vbQuestion + vbDefaultButton2, "Highlight Existing Entries")
    'If Answer = vbNo Then
    '    HighlightYellow = False
    'ElseIf Answer = vbYes Then
        'HighlightYellow = True
    'End If
'IF YOU HAD 3 CHOICES, 2 CHECKS WOULD BE REQ'D. YOU ONLY HAVE 2, SO ONLY 1 CHECK IS REQ'D IN THIS CASE (IF NOT YES, THEN HAS TO BE NO)
If Answer = vbYes Then HighlightYellow = True

Call OpenPayrollLogExcelFile 'CALL KEYWORD NOT REQ'D BUT IS OK
Call AddPayrollData(JobID, HighlightYellow)
DoCmd.Beep
MsgBox "The file is open and the payroll data has been added.  Don't forget to save the file." 'HOW DO YOU KNOW THIS FOR SURE? 

WB.Close
'SHOULD YOU BE CLOSING THIS INSTANCE OF THE APPLICATION AS WELL?
Set WB = Nothing
Set XL = Nothing

End Sub
'************************************************************************************************************************************
Sub AddPayrollData(JobID As Integer, Optional HighlightYellow As Boolean) 'HIGHLIGHT DOESN'T LOOK OPTIONAL. SEEMS IT WOULD ALWAYS BE T OR F

Dim rstQ_PayrollLog As Recordset 'SHOULD DECLARE THE RS TYPE: DAO?
Dim FoundEmptyRow As Boolean, FoundMatchingEntry As Boolean
Dim RowNumber As Integer, EmptyRow As Integer, LCounter As Integer
Dim PositionAndOCC As String

FoundEmptyRow = False
FoundMatchingEntry = False
RowNumber = 8
EmptyRow = 0

Set rstQ_PayrollLog = CurrentDb.OpenRecordset(Name:="Q_PayrollLog", Type:=RecordsetTypeEnum.dbOpenSnapshot)
'SET Set rstQ_PayrollLog = CURRENTDB.OPENRECORDSET ("Q_PayrollLog", dbOpenSnapshot) IS SHORTER

'IF NO RECORDS, YOU WILL GENERATE AN ERROR. FIRST USE: IF NOT (rstQ_PayrollLog.BOF AND rstQ_PayrollLog.EOF) THEN... 
'OR TEST RS COUNT: IF rstQ_PayrollLog.RECORDCOUNT <> 0 THEN... (DON'T USE >0)
'IF NO RECORDS, THEN EXIT
rstQ_PayrollLog.MoveLast
rstQ_PayrollLog.MoveFirst

With XL 'XL IS THE APPLICATION. YOU ARE REFERENCING EXCEL OBJECTS WHICH ARE NOT A CHILD OF THE APPLICATION OBJECT. THE HIERARCHY IS
'APPLICATION>WORKBOOK>WORKSHEET>RANGE. SEE ALSO ??? BELOW.
    .Sheets("PayrollLog").Activate
    Do While Not rstQ_PayrollLog.EOF
        FoundMatchingEntry = False
        If rstQ_PayrollLog.Fields("JobID") = JobID Then
            If .Range("A1") = "[company name here]" Then .Range("A1") = rstQ_PayrollLog.Fields("CompanyName")
            If .Range("A2") = "[job title here]" Then .Range("A2") = rstQ_PayrollLog.Fields("JobTitle")
            Do While FoundEmptyRow = False
                If IsEmpty(.Range("A" & RowNumber)) Then 
  'ISEMPTY IS AN ACCESS and EXCEL FUNCTION. COULD BE A PROBLEM HERE AS THEY MEAN DIFFERENT THINGS TO EACH.
  'IN ACCESS, A VARIABLE NOT INITIALIZED (MADE = TO SOMETHING) WOULD RETURN TRUE. XL.RANGE ISN'T A VARIABLE. 
  'NOT CERTAIN WHICH WOULD TAKE PRECEDENCE.
                    FoundEmptyRow = True
                Else
                    If HighlightYellow = True Then .Range("A" & RowNumber & ":M" & RowNumber).Interior.ColorIndex = 6 'Yellow
                    RowNumber = RowNumber + 1
                End If
            Loop
 
       If EmptyRow = 0 Then EmptyRow = RowNumber 
           ''''now i need it to scroll through each record in the query and check to see if this record is already on the log.
           ''''if it's not, it should write it to next empty row.
'this next section goes through the existing items on the log (if there are any) to see if it's
'the same timecard record as the current one in the Recordset.
            If RowNumber > 8 Then
                For LCounter = 8 To (RowNumber - 1)
                    If rstQ_PayrollLog.Fields("TimecardID") = .Range("N" & LCounter) Then
                        FoundMatchingEntry = True
                        Exit For
                    End If
                Next
            End If
            
     If FoundMatchingEntry = True Then
          If IsNull(rstQ_PayrollLog.Fields("OCCCode")) Or rstQ_PayrollLog.Fields("OCCCode") = "" Then
               PositionAndOCC = rstQ_PayrollLog.Fields("PositionTitle")
          Else
               PositionAndOCC = rstQ_PayrollLog.Fields("PositionTitle") & " / " & rstQ_PayrollLog.Fields("OCCCode")
          End If
            
        .Range("A" & LCounter) = rstQ_PayrollLog.Fields("FullName")
        .Range("B" & LCounter) = PositionAndOCC
        .Range("C" & LCounter) = Val(rstQ_PayrollLog.Fields("PositionAcctCode"))
        .Range("D" & LCounter) = rstQ_PayrollLog.Fields("WeekEnding")
        .Range("E" & LCounter) = rstQ_PayrollLog.Fields("NumberDaysWorked")
        .Range("F" & LCounter) = rstQ_PayrollLog.Fields("Rate")
        .Range("G" & LCounter) = rstQ_PayrollLog.Fields("1XTotalAmt")
        .Range("H" & LCounter) = rstQ_PayrollLog.Fields("1point5XTotalAmt")
        .Range("I" & LCounter) = rstQ_PayrollLog.Fields("2X3X")
        .Range("J" & LCounter) = rstQ_PayrollLog.Fields("MPTotalAmt")
        .Range("K" & LCounter) = rstQ_PayrollLog.Fields("TOTALAMT")
        .Range("L" & LCounter) = rstQ_PayrollLog.Fields("BoxRentalTotalAmt")
        .Range("M" & LCounter) = rstQ_PayrollLog.Fields("MileageTotalAmt")
        .Range("N" & LCounter) = rstQ_PayrollLog.Fields("TimecardID")       
        .Range("A" & LCounter & ":M" & LCounter).ShrinkToFit = True
                If LCounter > 8 Then .Range("A" & LCounter & ":M" & LCounter).Borders.LineStyle = xlContinuous
                    .Range("C" & LCounter).HorizontalAlignment = xlLeft
                    .Range("D" & LCounter).NumberFormat = "mm/dd/yy"
                    .Range("D" & LCounter).HorizontalAlignment = xlLeft
                    .Range("E" & LCounter).HorizontalAlignment = xlCenter
                    .Range("F" & LCounter).NumberFormat = "0.0000"
                    .Range("F" & LCounter).HorizontalAlignment = xlCenter    
                            If .Range("I" & LCounter) = "" Or IsEmpty(.Range("I" & LCounter)) Then
                                .Range("I" & LCounter) = .Range("I" & LCounter)
                            Else
                                .Range("I" & LCounter) = RemoveFirstChar(.Range("I" & LCounter))
                            End If
                    .Range("G" & LCounter & ":M" & LCounter).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                    .Range("K" & LCounter).Font.Bold = True
                    .Range("K" & LCounter).Font.Size = 12    
                Else
                           If IsNull(rstQ_PayrollLog.Fields("OCCCode")) Or rstQ_PayrollLog.Fields("OCCCode") = "" Then
                                PositionAndOCC = rstQ_PayrollLog.Fields("PositionTitle")
                           Else
                                PositionAndOCC = rstQ_PayrollLog.Fields("PositionTitle") & " / " & rstQ_PayrollLog.Fields("OCCCode")
                           End If
            
                  .Range("A" & EmptyRow) = rstQ_PayrollLog.Fields("FullName")
                  .Range("B" & EmptyRow) = PositionAndOCC
                  .Range("C" & EmptyRow) = Val(rstQ_PayrollLog.Fields("PositionAcctCode"))
                  .Range("D" & EmptyRow) = rstQ_PayrollLog.Fields("WeekEnding")
                  .Range("E" & EmptyRow) = rstQ_PayrollLog.Fields("NumberDaysWorked")
                  .Range("F" & EmptyRow) = rstQ_PayrollLog.Fields("Rate")
                  .Range("G" & EmptyRow) = rstQ_PayrollLog.Fields("1XTotalAmt")
                  .Range("H" & EmptyRow) = rstQ_PayrollLog.Fields("1point5XTotalAmt")
                  .Range("I" & EmptyRow) = rstQ_PayrollLog.Fields("2X3X")
                  .Range("J" & EmptyRow) = rstQ_PayrollLog.Fields("MPTotalAmt")
                  .Range("K" & EmptyRow) = rstQ_PayrollLog.Fields("TOTALAMT")
                  .Range("L" & EmptyRow) = rstQ_PayrollLog.Fields("BoxRentalTotalAmt")
                  .Range("M" & EmptyRow) = rstQ_PayrollLog.Fields("MileageTotalAmt")
                  .Range("N" & EmptyRow) = rstQ_PayrollLog.Fields("TimecardID")
                  .Range("A" & EmptyRow & ":M" & EmptyRow).ShrinkToFit = True
                      If EmptyRow > 8 Then .Range("A" & EmptyRow & ":M" & EmptyRow).Borders.LineStyle = xlContinuous
                         .Range("C" & EmptyRow).HorizontalAlignment = xlLeft
                         .Range("D" & EmptyRow).NumberFormat = "mm/dd/yy"
                         .Range("D" & EmptyRow).HorizontalAlignment = xlLeft
                         .Range("E" & EmptyRow).HorizontalAlignment = xlCenter
                         .Range("F" & EmptyRow).NumberFormat = "0.0000"
                         .Range("F" & EmptyRow).HorizontalAlignment = xlCenter
                    If .Range("I" & EmptyRow) = "" Or IsEmpty(.Range("I" & EmptyRow)) Then
                            .Range("I" & EmptyRow) = .Range("I" & EmptyRow)
                               Else
                                    .Range("I" & EmptyRow) = RemoveFirstChar(.Range("I" & EmptyRow))
                               End If
                        .Range("G" & EmptyRow & ":M" & EmptyRow).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                        .Range("K" & EmptyRow).Font.Bold = True
                        .Range("K" & EmptyRow).Font.Size = 12
                         EmptyRow = EmptyRow + 1
                      End If
        End If  
        rstQ_PayrollLog.MoveNext
    Loop
    
    .Range("A" & EmptyRow - 1 & ":M" & EmptyRow - 1).Borders(xlEdgeBottom).LineStyle = xlContinuous

    'the below will sort the Payroll Log
    .Range("A7:N" & EmptyRow - 1).Select
    .ActiveSheet.Sort.SortFields.Clear '??? BESIDES RANGE AS NOTED ABOVE, ANOTHER OBJECT WHICH ISN'T A CHILD OF APPLICATION (XL VARIABLE)
    .ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "C8:C" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "A8:A" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "D8:D" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With .ActiveSheet.Sort
        .SetRange Range("A7:N" & EmptyRow - 1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    .Range("B6").Select
    
    'this sets the Print Area so the TimecardID column will not print
    .ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & EmptyRow - 1
    
End With

rstQ_PayrollLog.Close
Set rstQ_PayrollLog = Nothing

End Sub
'***********************************************************************************************************************
Sub OpenPayrollLogExcelFile()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

''Set varFile = Nothing 'REM THIS OUT?? CANNOT SET A BOOLEAN VARIABLE TO "NOTHING"
' Set up the File Dialog. 
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
        ' Allow user to make multiple selections in dialog box '
    .AllowMultiSelect = False
        ' Set the title of the dialog box. '
    .Title = "Please select the Payroll Log file you wish to open."
        ' Clear out the current filters, and add our own.'
    .Filters.Clear
    .Filters.Add "Excel", "*.XLSX"
    .Filters.Add "All Files", "*.*"
        ' Show dialog. If the .Show returns True, user picked a file. If the .Show returns False, user clicked Cancel. '
    If .Show = True Then 'TRUE CAN BE ASCERTAINED AS "If .Show Then..."
        For Each varFile In .SelectedItems
            FileToOpen = varFile 'IF YOU ALLOWED MULTISELECT, THIS WOULD CREATE AN INVALID STRING OF FILE NAMES.
        Next
    Else
        MsgBox "Operation cancelled."
        Exit Sub
    End If
End With

'this will actually open the Excel file
Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open(FileToOpen)
XL.Visible = True

End Sub
'*********************************************************************************************************************
Public Function RemoveFirstChar(RemFstChar As String) As String
If Left(RemFstChar, 1) = "$" Then
  RemFstChar = Replace(RemFstChar, "$", "")
End If
RemoveFirstChar = RemFstChar
End Function

[mcomp72]

@ssanfu - thank you very much for sharing your code! I will take a look in more detail and see if I can figure out how to make it work without it closing the Excel document from within the code. I would prefer to not close it, as I want the user to be able to do things in the Excel file on their own, and then close/save whenever they are ready.

@Micron - a HUGE thank you to you for taking the time to go through my code. I will respond to your comments in my next reply.

[Micron]

As long as we're sharing code, I thought I'd post one that worked well for me. The added advantage for anyone may be that it illustrates how to check a workbook out of a SharePoint site (as long as you know the path of the file). It uses the various objects as I and others seem to be saying, and invokes them according to their hierarchy. That is, a sheet reference is preceded by the workbook reference, which is a variable. The range reference is preceded by the sheet reference. One could string them all together if need be, such as xlx.xls.xlc (book, sheet, cell). Declaring them this way should eliminate the necessity of specifying Active anything. The function called by this code contains similar setup, but when actually writing the recordset values, I use the .Offset method rather than a lot of verbiage to refer to a Range object. The Excel file uses named ranges so I can simply refer to them by their name (one reason is because the entire data set is cleared and over-written due to its volatility).
Note: some parts, such as message strings, have been shortened to ... to protect the innocent (me, from the db owner).

Private Sub cmdChrtData_Click()

Dim xlx As Object, xlw As Object
Dim svChartPath As String, msg As String
Dim result As Integer
Dim blnCanCheckout As Boolean, blnWeCheckedOut As Boolean

On Error GoTo errHandler
svChartPath = DLookup("[ParamValue]", "[tblDBParams]", "DBParam='Chart'") 'table for paths & other parameters

'open form before query runs because query may take some time
DoCmd.OpenForm "frmWait"

With Forms!frmWait
   .lblmsga.Caption = "GETTING..."
   'size & position form (using db tabs option makes this form expand to full screen)
   .Move Left:=4500, Top:=2000, Width:=5300, Height:=3000
   .Repaint
End With

Pause (1) 'give chance for form to display 'calls a module level function to create a delay (1 is one second)

'open an Excel application...
Set xlx = CreateObject("Excel.Application")
xlx.Visible = False
''xlx.Visible = True '****** useful when testing

'now test to see if we can check out the workbook (exlusive use) from SharePoint
'switch next 2 lines when testing on non-Share Point drive
blnCanCheckout = xlx.Workbooks.CanCheckOut(svChartPath)
'**********blnCanCheckout = True

If blnCanCheckout = False Then
   msg = "Cannot push data; another user has checked out the file or has it open." & vbCrLf
   msg = msg & "Operation canceled."
   MsgBox msg, vbOKOnly, "CHART FILE NOT AVAILABLE"
   DoCmd.Close acForm, "frmWait"
   Set xlx = Nothing
   Exit Sub
End If

If blnCanCheckout Then
   Set xlw = xlx.Workbooks.Open(svChartPath)
   xlx.Workbooks.CheckOut svChartPath 'check out the workbook from SharePoint
   blnWeCheckedOut = True 'and set the flag that it has been checked out
   Pause (2) 'allow time for server to check out workbook before writing data
   xlx.ScreenUpdating = False 'make true when testing to see workbook changes
   ''xlx.ScreenUpdating = True
   xlx.DisplayAlerts = False

'PushChartData is the function to loop the recordset and write the values. 
'The workbook itself is passed along with the flag as to which data set is being written
   If PushChartData(xlw, "Daily Count") = False Then 
      msg = "...." & vbCrLf
      msg = msg & "...?"
      result = MsgBox(msg, vbYesNo, "DATA PUSH ERROR")
      If result = 7 Then GoTo exitHere 'one of the RARE times I've used a GoTo other than an error handler
   End If

   With Forms!frmWait
      .lblmsga.Caption = "GETTING ..."
      .Repaint
   End With
        
  If PushChartData(xlw, "Weekly Summary") = False Then
      msg = "...." & vbCrLf
      MsgBox msg, vbOKOnly, "DATA PUSH ERROR"
  Else
      With Forms!frmWait
          .lblmsga.Caption = ""
          .lblMsg.Caption = "Workbook will now be saved, checked in and closed."
          .Repaint
      End With
      Pause 3
      xlw.Save 'save the changes
      xlw.CheckIn svChartPath 'check in the workbook
      blnWeCheckedOut = False 'record that the workbook is no longer checked out
      If CurrentProject.AllForms("frmWait").IsLoaded Then DoCmd.Close acForm, "frmWait", acSaveNo
      MsgBox "All done!"
  End If
End If

exitHere:

'may not be req'd to reverse settings (done as a precaution anyway)
If blnWeCheckedOut = True Then 'error could route to here after check out
    xlw.CheckIn svChartPath 'check in the workbook
    blnWeCheckedOut = False
End If

If CurrentProject.AllForms("frmWait").IsLoaded Then DoCmd.Close acForm, "frmWait", acSaveNo
xlx.ScreenUpdating = True
xlx.DisplayAlerts = True
xlx.Quit 'if checkout unsucessful, file not opened; no need to close or check in
    
exitByPass:
Set xlw = Nothing
Set xlx = Nothing
Exit Sub

errHandler:
Select Case Err.Number
    Case 70, 1004, -2147417848, -2147417851 'automation errors of one sort or another
        msg = "Cannot push data to chart file; another user has file open." & vbCrLf
        msg = msg & "or it cannot be checked out. Operation canceled."
        MsgBox msg
        Resume exitHere
    Case 462
        'in rare case, you could close Excel while debugging code & before reaching the
        'xlx handling after exitHere, causing a continuous loop
        Resume exitByPass 
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume exitHere
End Select

End Sub

[mcomp72]

Do I understand the order of execution?

Yes.

If for any reason the workbook being controlled by this code isn't the active object, you should expect problems.

In Sub AddPayrollData, right after "With XL", I have the line:

.Sheets("PayrollLog").Activate

If I was to change that to

WB.Sheets("PayrollLog").Activate

do you think that would solve this potential issue?

Elsewhere in this forum in a recent Excel question, it was pointed out that each necessary object should be explicitly created. You're not doing this, which may be causing you to not reference the hierarchy of the object model for Excel (pointed out in code comments, I think)

I am not sure I am following you, but I think maybe I am. In Sub OpenPayrollLogExcelFile, I have these lines of code:

Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open(FileToOpen)

You are saying there are even more objects that I need to create? From your comment inside the code, it sounds like I need to create one for Worksheet as well, correct? After that, am I okay, or is there even more that I need to create?

I looked at Message #3 in that other forum post you referenced, and the paged linked by June7 showed some code (very similar to what ssnafu just posted) which might help eleviate the problem I am having. Part of the code is this:

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

If I implement this, do you think it would solve my problem? I can always test it out once I am finished typing this reply.

Lastly, I am not a big fan of so much code nested in a With block. You have the most I've ever seen. I recommend trying to minimize (not eliminate) a whole lot of If's, Do's, For's and more With's like you have. Makes it very hard to follow. When you get this working, months from now if you have to look at this again, it won't be so fresh. I spent about 2 hours sifting through the maze.

Ah. This is just my inexperience as a programmer. In the future I will try not to use these as much.

WHAT PROVIDES JOBID?

There is a form that has a button on it. That form is where I enter the information about the specific Job, so it is pulling the JobID off of that form and sending it to Sub CreatePayrollLog when the button is clicked.

WB.Close 'SHOULD YOU BE CLOSING THIS INSTANCE OF THE APPLICATION AS WELL?

I had put that line in there just as a test to see if that would solve the problem I was having. I actually meant to take that line out before posting my code here. I don't want the Excel file to be closed by my code, as I want the user to be able to access the file, make changes, etc and then close/save it on their own.

HIGHLIGHT DOESN'T LOOK OPTIONAL.

You are correct. I think when I was first creating it I was thinking I might need it to be optional, but based on the way I have written it now, it isn't. I will eliminate the word Optional.

Dim rstQ_PayrollLog As Recordset 'SHOULD DECLARE THE RS TYPE: DAO?

More inexperience here. When I was first searching around the web, learning how to access information in Tables & Queries from within VBA, I found some example code that showed how to do it. The way I typed the line is exactly the way they did it in their code. I'm not sure what DAO even is, though I have seen it somewhere before. I'll add this to my list of things to research!

With XL 'XL IS THE APPLICATION. YOU ARE REFERENCING EXCEL OBJECTS WHICH ARE NOT A CHILD OF THE APPLICATION OBJECT. THE HIERARCHY IS
'APPLICATION>WORKBOOK>WORKSHEET>RANGE. SEE ALSO ??? BELOW.

I think I understand here. Though, I am curious... why is it working at all if I have not specified the Workbook and Worksheet in my lines of code? In this particular Excel file, there is only one worksheet. Could that be why it is working, regardless of my code not specifying the hierarchy correctly?

ISEMPTY IS AN ACCESS and EXCEL FUNCTION. COULD BE A PROBLEM HERE AS THEY MEAN DIFFERENT THINGS TO EACH.
IN ACCESS, A VARIABLE NOT INITIALIZED (MADE = TO SOMETHING) WOULD RETURN TRUE. XL.RANGE ISN'T A VARIABLE.
NOT CERTAIN WHICH WOULD TAKE PRECEDENCE.

Any suggestion of what I would use instead of IsEmpty? I am trying to check to see if the cell is empty -- that way I know that that particular row of the Excel file does not have data in it already, and is thus available to have data written to it. Can I use 'If [cell] = ""'? I wasn't sure if the "" would automatically mean the same thing as a cell being empty. I feel like I remember reading somewhere that is wasn't always the same thing.

.ActiveSheet.Sort.SortFields.Clear '??? BESIDES RANGE AS NOTED ABOVE, ANOTHER OBJECT WHICH ISN'T A CHILD OF APPLICATION (XL VARIABLE)

So maybe my WITH block should begin like this?

With XL.WB.Worksheet("PayrollLog")

Would that then give me the correct hierachy that I need?

Set varFile = Nothing 'REM THIS OUT?? CANNOT SET A BOOLEAN VARIABLE TO "NOTHING"

Do you think I need to change it to something else, like varFile = ""? Or just remove completely?

Also... I would love to get your comment to my post #22. I don't know if that might be a solution to my problem, and if so, I'm not sure how exactly to write the code. But now that I see that other code from the site June7 referenced in his answer to the other forum post, maybe THAT code is what I need to solve my problem. Do you have a suggestion of which I should try first?

Lastly, again I want to say a BIG thank you to you spending a couple of hours to look at my code!! I really, really appreciate it! It's people like you that have helped me get this far in my coding adventures. (I know I still have a long way to go, though!)

[mcomp72]

I added another object for the Worksheet, so added these lines:

Dim WS As Object    'this is a global variable across the module

And then this:

Set WS = WB.Worksheets("PayrollLog")

I tried changing my WITH statement to start like this:

With XL.WB.WS

But received Run-time error 438: Object doesn't support this property or method.

Any ideas what's wrong? I thought I was understanding the hierarchy correctly, but it doesn't look like it.

[mcomp72]

I decided to switch my WITH statement back the way I had it before; just as With XL.

I inserted this section of code that ssanfu mentioned, and June7 had referenced in a different forum post.

On Error Resume Next
Set XL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Set XL = CreateObject("Excel.Application")
End If

I changed the code a little bit that calls for sorting of the Excel file, to this:

With WS
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range( _
        "C8:C" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .Sort.SortFields.Add Key:=Range( _
        "A8:A" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .Sort.SortFields.Add Key:=Range( _
        "D8:D" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    .Sort.SetRange Range("A7:N" & EmptyRow - 1)
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlTopToBottom
    .Sort.SortMethod = xlPinYin
    .Sort.Apply
End With

I ran the code four times in a row, and I did not received any errors! So, this seems to have solved it!

What I find really strange is that the proper hierarchy doesn't seem to be required with the WITH statement for writing all the information into the various cells of the worksheet. However, when it comes to the sorting, I had to add that new WITH statement to specify the Worksheet. I also had to add WS in front of the PrintArea line, like so:

WS.PageSetup.PrintArea = "$A$1:$M$" & EmptyRow - 1

Anyone know why the proper hierarchy is not required for all the .Range("A1") = some value type of lines I have in there?

[Micron]

Set WS = WB.Worksheets("PayrollLog")
With WS
.etc
.etc
End With

not

With XL.WB.WS
If you set a worksheet variable and say it belongs to a certain workbook (WB) variable, you've already told Access that WS is a child of WB, so you don't repeat the entire reference. I believe otherwise, what you have/had is being interpreted as
XL.WB.(WB.WS) because the two in parentheses were identified as WS being a child of WB. Hope that makes some sense.

What you had is like double-referencing (I don't know of any other way of describing it). Not sure I said you had to use every level of object reference, just that you needed to understand which ones are parents and which are the children of a parent. In your sorting segment, you were basically saying that a grandchild (sheet) was the child of a grandparent (application) by trying to apply a sort to the application object. More on that below.

Not sure what you mean by this.
Anyone know why the proper hierarchy is not required for all the .Range("A1") = some value type of lines I have in there?
If I'm not mistaken, I pointed out previously that a) you had not declared a worksheet object for easier referencing but more importantly b) you were trying to apply the sort method to the wrong object - XL, which was the application, not the worksheet. You had:

With XL <-- the application
   lotsastuff
   .ActiveSheet.Sort.SortFields.Clear
   morestuff
End With

Pretty sure ActiveSheet isn't a child of the application as opposed to the workbook. So in fact, the proper hierarchical reference is needed, but the syntax is also important.

On the notion of With blocks: basically, if you have an application object your block would use it when you want to affect that object (XL):

With XL
   XL.Visible = False
   XL.ScreenUpdating = True
   XL.DisplayAlerts = True
End With

If you declared xlw as a workbook, you'd do the same with the relevant properties and methods for it if need be, BUT since it's a child of the application, only your application variable need come first in the set statement:

Set xlw = XL.Workbooks.Open("SomeNetworkPath")

Now you want to drill down to the worksheet. If xls (the worksheet) is declared as an Object, the set statement need only refer back to its parent,the workbook and not include the application object (because you've already defined that workbook is a child of application):

Set xls = xlw.Sheets("MySheetName")

Hope that helps. Unfortunately, I think your thread has gotten a bit overwhelming for me. If you try to compartmentalize your With blocks so they are more related, don't nest methods, multiple If blocks, etc. inside them so much, understand the hierarchy of an object model (Google will be your best friend) and how to declare the needed objects and how to set the references, you should be OK. Glad you seem to have gotten it solved. If there are any more major issues I might have to require a zipped copy of your files (you can remove sensitive data or use Find & Replace to alter "Joe Smith" to anything at all) in order to see the flow and clean things up easier.

Good Luck!