Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92

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


    Hi everyone. I have written some VBA code to open an Excel file and output some data from Access into that Excel file. I am now trying to do a sort of what I have output. The strange thing is, sometime my code executes perfectly, and other times I am getting Run-time error 91. It would make sense to me if I was getting that error every time, but why the code would work sometimes but not others is beyond me.

    My sub with the code is rather long, so I'm going to show you the relevant parts. (At least this is what I THINK are the relevant parts.)

    Code:
    With XL
    
        .Range("A7:N" & EmptyRow - 1).Select
        ActiveWorkbook.Worksheets("PayrollLog").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("PayrollLog").Sort.SortFields.Add Key:=Range( _
            "C8:C" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("PayrollLog").Sort.SortFields.Add Key:=Range( _
            "A8:A" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("PayrollLog").Sort.SortFields.Add Key:=Range( _
            "D8:D" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("PayrollLog").Sort
            .SetRange Range("A7:N" & EmptyRow - 1)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("B6").Select
    
    End With
    The line where it is giving me the run-time error is:
    Code:
    ActiveWorkbook.Worksheets("PayrollLog").Sort.SortFields.Clear
    I have tried removing this line, and then it will give me the error on the next line down. So it doesn't seem to be an issue with this line in particular.

    Note: I wasn't sure how to write the VBA code to do the sort, so I recorded a macro in Excel, and then copied the code out of that. The only thing I changed from the macro code was the variable for the row number. In the macro code, it was an actual number. I replaced it with a variable that I am using earlier in the code, which determines the first empty row on the sheet. Thus, when I minus 1 from that variable, it gives me the last row where there is data present.

    As best I can tell, it gives the error every other time I run the code.

  2. #2
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    Here are the References I have turned on in the DB file.

    Click image for larger version. 

Name:	2017-11-18.png 
Views:	34 
Size:	23.6 KB 
ID:	31325

  3. #3
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    As far as the XL variable, here is how that is set. This is in a different sub than the above code, but XL and WB are global variables inside this Module.

    Code:
    Sub OpenPayrollLogExcelFile()
    
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    Set varFile = 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 the dialog box. If the .Show method returns True, the '
        ' user picked at least one file. If the .Show method returns '
        ' False, the user clicked Cancel. '
        If .Show = True Then
            For Each varFile In .SelectedItems
                FileToOpen = varFile
            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

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Would it not be easier to sort the data before it leaves access

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not an Excel person, but is it possible that an instance of Excel is already running at some times.
    You may need some logic

    Code:
    If Excel is running then
    --use that instance
    else
    -- create an instance of Excel
    end

  6. #6
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    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.

    orange: every time I tried it I did have another Excel file open. I will try it without any other Excel files open and see if the error still happens. But is it not weird that it is giving me this literally half the time? On the odds attempts, it works fine. On the even attempts, it does not. Seems so strange to me. Shouldn't it either happen always or happen never?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Shouldn't it either happen always or happen never?
    You are assuming that no conditions change from one time to the next. That would not be a useful point of view to have when coding. I'm going to go by memory, that error 91 is "Object variable or with block variable not set" since you don't say and I'm too lazy to look it up .
    Sometimes you can pinpoint the error by stepping through and making careful comparisons of the flow when it works and when it doesn't. I suspect an object you have declared and set is lost from memory during the second run through because the flow isn't exactly the same, thus it isn't reset after being lost. You will get the error on the next line after commenting the first one out because they are practically the same. Whatever is wrong with the first is just being replaced by the second.

    If stepping through doesn't uncover the problem, I suggest trying to ascertain that the object reference is still valid by using other properties. In this case, I'm not real familiar with ActiveWorkbook.Worksheets("PayrollLog").Sort.SortF ields.Clear but I'd hazard a guess that ActiveWorkbook.Worksheets("PayrollLog") is the object. Try placing
    msgbox ActiveWorkbook.Worksheets.Name (whereby Name is a property that should work) before the point where the error now occurs. If you get the same error, then ActiveWorkbook.Worksheets is the object that is the one being referenced by the error message. You would then have to figure out why it's being lost. Can't help much there without seeing the db, I think. I suspect the global variable isn't as global (or permanent) as you think.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I, too, very seldom dabble in Excel, but my experience is that Access often actually tags the next line after the line that actually causes the error, and your comment that

    "...I have tried removing this line, and then it will give me the error on the next line down..."

    kind of says that this is happening here, as well, so I suspect that the actual problem line is

    .Range("A7:N" & EmptyRow - 1).Select

    and has to do with the data being selected when the error occurs.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    @orange: I rebooted my computer, and made sure there were no other instances of Excel running. I then ran the code. Once again, it would happen every other time I ran it.

    @Missinglinq: I removed the next several lines after the line you suggested, up through
    Code:
    .Range("B6").Select
    I ran the code 5 times, the error never happened during any of those times, and the Selection was always made just as the line of code instructs. So I don't think the problem is with the line you suggested.

    @Micron: First off, you are correct about Run-time error 91. Good memory! I tried your suggestion of the MsgBox, and you were correct -- the error then happened at that MsgBox line -- but again, only every other time I run the code. I am going to try to step through the code and see if I can figure out what is changing between the first and second running of the code.

  10. #10
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I stepped through the code, and couldn't see any different between the first time and second time. The first time it worked perfectly, and the second time I got the same error.

    Maybe I need to attempt to do the sort another way. I am using a With Statement in the area where I'm trying to do the sort, so maybe I don't need ActiveWorkbook.Worksheets("PayrollLog") in any of those lines?

    Here's some of the code that defines the Excel workbook. From looking at this, can anyone suggest the correct way to write the lines to call the sort method? I have tried various versions but none of them have worked.

    Code:
    Dim XL As Object
    Dim WB As Object
    Dim FileToOpen As String
    
    Set XL = CreateObject("Excel.Application")
    Set WB = XL.Workbooks.Open(FileToOpen)  'this variable is defined once the user selects which file they want to open.  The file always opens correctly, so I don't suspect there is anything wrong here.
    
    With XL
    
    End With
    I decided to replace ActiveWorkbook.Worksheets("PayrollLog") with .ActiveSheet to see if that made any difference. That section of code is now:

    Code:
    With XL
    
        .Range("A7:N" & EmptyRow - 1).Select
        .ActiveSheet.Sort.SortFields.Clear
        .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
    The line where it was hanging up before now works each time, HOWEVER, the line after it is causing it to hang up.

    Code:
    .ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "C8:C" & EmptyRow - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
    This time it gives me Run-time error 1004: Method 'Range' of object '_Global' failed

    And again, it's giving me this error only 50% of the time; every other time I run it. I'm not sure what else I should try.

  11. #11
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I may have figured it out, at least partly. Even though I would close Excel before running the code the second time, I discovered just now Excel was still listed in the Task Manager as a Background Process. I clicked on END TASK, and it went away. I tried running the code again. This time, I got the following error message: Run-time error 462: The remote server machine does not exist or is unavailable.

    I am running Excel on my desktop computer. The file I am accessing is on my local hard drive. So I don't get it.

    I closed Excel via the Task Manager again, and ran the code again. This time it worked.

    Closed Excel. Ended Task in the Task Manager. Tried again. Run-time 462.

    Closed Excel. Looked in Task Manager. This time there is no listing for Excel in Background Tasks.

    Ran the code again. It worked. Checked Task Manager. Excel is listed under Background Tasks again.

    Weird -- any idea why would it stay in Background Tasks once I close down Excel sometimes, and other times not?

    @orange, it seems if you were correct after all. Has anyone written code in the past to do what he suggests in his post above (#5)? I haven't a clue how to do it, and based on a Google search I just did, it seems WAY above my head.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here's one approach I found:
    Code:
    Dim xlx As Excel.Application
    Dim blnEXCEL As Boolean
    blnEXCEL = False
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds like you are not closing the workbook between calls, in which case as June7 is kinda pointing out, you can't just allow your code to re-declare all the same objects.
    BTW, probably not related, but I see in post 3 you declare varFile then immediately attempt destroy it (= Nothing), yet you haven't Set it to anything beforehand. Set is reserved for assigning objects to variables or properties, and you're not dealing with an object:
    Code:
    Dim varFile As Variant
    Set varFile = Nothing
    ....
        If .Show = True Then
            For Each varFile In .SelectedItems
    I'm surprised it doesn't generate an error each time. What are you attempting to do; make varFile be Null at first?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I'm surprised it doesn't generate an error each time. What are you attempting to do; make varFile be Null at first?
    To be honest, that entire sub was something I found on another forum. I wasn't sure how to open a file in VBA, and someone had posted this as a solution to the same question asked by someone else. So I "borrowed" the code. Would you suggest I simply delete that line, or perhaps set it to "= Null" instead? I'm not sure if I need it or not.

    You are correct that I am not closing the workbook in my code between calls. The reason I am not is I don't want the code to close Excel. I want it to leave the file open so the user can then do whatever they want with the file at that point. Do you know if there a way to close it as far as the code is concerned, but still leave it open in Windows so the user can do whatever they need to do with the file? Or is my best bet to implement the code that June7 posted above, so that if the code is run a second time, all instances of Excel are automatically closed? I'd love to avoid that if possible, just in case the user has another file open in Excel that they'd rather not have to close just to run this code.

  15. #15
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    As a test, I added WB.Close right before I set the XL and WB objects to Nothing, but I am still getting Run-time error 1004 every other time I run the code. I'm going to post the code for the entire module, in case there is something in there that I haven't posted yet that might be the cause. The first sub to run is CreatePayrollLog. It is passed a variable from the OnClick of a button on one of my forms.

    Code:
    Option Compare Database
    Option Explicit
    
    Dim XL As Object
    Dim WB As Object
    Dim FileToOpen As String
    
    Sub CreatePayrollLog(JobID As Integer)
    
    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
    
    Call OpenPayrollLogExcelFile
    
    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."
    
    WB.Close
    Set WB = Nothing
    Set XL = Nothing
    
    End Sub
    
    Sub AddPayrollData(JobID As Integer, Optional HighlightYellow As Boolean)
    
    Dim rstQ_PayrollLog As Recordset
    Dim FoundEmptyRow As Boolean
    Dim RowNumber As Integer
    Dim EmptyRow As Integer
    Dim LCounter As Integer
    Dim FoundMatchingEntry As Boolean
    Dim PositionAndOCC As String
    
    FoundEmptyRow = False
    FoundMatchingEntry = False
    RowNumber = 8
    EmptyRow = 0
    
    Set rstQ_PayrollLog = CurrentDb.OpenRecordset(Name:="Q_PayrollLog", Type:=RecordsetTypeEnum.dbOpenSnapshot)
    
    rstQ_PayrollLog.MoveLast
    rstQ_PayrollLog.MoveFirst
    
    With XL
    
        .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
                        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.
                
                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
        .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
    
    ' 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 the dialog box. If the .Show method returns True, the '
        ' user picked at least one file. If the .Show method returns '
        ' False, the user clicked Cancel. '
        If .Show = True Then
            For Each varFile In .SelectedItems
                FileToOpen = varFile
            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

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 05-27-2017, 06:40 AM
  2. Code that runs one time only
    By shod90 in forum Forms
    Replies: 1
    Last Post: 01-03-2017, 05:09 AM
  3. VBA Code Returning Error Run Time Error 3061
    By tgwacker in forum Access
    Replies: 2
    Last Post: 11-24-2013, 11:00 AM
  4. Code Runs Perfectly... Every other time.
    By offie in forum Programming
    Replies: 5
    Last Post: 08-14-2013, 03:45 PM
  5. Making sure code runs regardless
    By nvrwrkn in forum Programming
    Replies: 2
    Last Post: 10-09-2012, 11:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums