Results 1 to 7 of 7
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Microsoft Excel cannot determine which row in your list or selection contains column labels

    I am using MS Access VBA to update excel spreadsheets after they have been exported from Access.

    When I get to the section that subtotals the results I get the error message: Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command"

    The exported spreadsheets have varying number of columns as each day new data is added. The data is a mixture of text, number and time, which is why you see me setting the column values to time below. Basically the data goes Text, text, text (these are stagnant and column 2 is used for grouping. The results data go in a pattern of number, time, time time, number, time, time, time etc



    The error happens in this section of the vba. It has to do with a range I think but I can't figure it out.

    Thanks for the help as always

    Code:
        With mybook.Worksheets(1)                     If .ProtectContents = False Then
                        Worksheets("qry_Daily_Export").UsedRange.Select
                        ActiveSheet.Range("1:1").Font.Bold = True
                        ActiveSheet.Range("1:1").EntireColumn.AutoFit
                        
                        ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("G:I").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("K:M").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("O:R").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("S:U").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("W:Y").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("AA:AC").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AE:AG").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AI:AK").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AM:AO").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AQ:AS").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AU:AW").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("AY:BA").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BC:BE").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BG:BI").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("BK:BM").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BO:BQ").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BS:BU").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BW:BY").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CA:CC").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("CE:CG").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CI:CK").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CM:CO").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CQ:CS").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CU:CW").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CY:DA").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DC:DE").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DG:DI").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DK:DM").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("DO:DR").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DS:DU").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DW:DY").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("EA:EC").NumberFormat = "[h]:mm:ss"
                        
                       Selection.Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What range is selected? How is UsedRange defined?

    If you want to provide files for analysis, follow instructions at bottom of my post.
    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.

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by June7 View Post
    What range is selected? How is UsedRange defined?

    If you want to provide files for analysis, follow instructions at bottom of my post.
    THis is the entire code

    Code:
    Option Compare DatabaseFunction daily_split_Update()
     Dim dbs As DAO.Database
    
    
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
            Dim acc As Object
    Set acc = CreateObject("Excel.Application")
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim rng As Range
        Dim cell As Range
        Dim y() As Variant
       
    
    
     Set dbs = CurrentDb
        'Fill in the path\folder where the files are
        MyPath = "C:\Automation\Aspect_Reports\daily"
    
    
        'Add a slash at the end if the user forget it
        If Right(MyPath, 1) <> "\" Then
            MyPath = MyPath & "\"
        End If
    
    
        'If there are no Excel files in the folder exit the sub
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            
        End If
    
    
        'Fill the array(myFiles)with the list of Excel files in the folder
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
    
    
        'Change ScreenUpdating, Calculation and EnableEvents
        With Application
           ' .Calculation = xlCalculationManual
           ' .ScreenUpdating = False
            '.EnableEvents = False
        End With
    
    
        'Loop through all files in the array(myFiles)
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
    
    
                If Not mybook Is Nothing Then
    
    
    
    
                    'Change cell value(s) in one worksheet in mybook
                     On Error Resume Next
                    With mybook.Worksheets(1)
                         If .ProtectContents = False Then
                        Worksheets("qry_Daily_Export").UsedRange.Select
                        ActiveSheet.Range("1:1").Font.Bold = True
                        ActiveSheet.Range("1:1").EntireColumn.AutoFit
                        
                        ActiveSheet.Range("E:E").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("G:I").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("K:M").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("O:R").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("S:U").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("W:Y").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("AA:AC").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AE:AG").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AI:AK").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AM:AO").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AQ:AS").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("AU:AW").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("AY:BA").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BC:BE").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BG:BI").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("BK:BM").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BO:BQ").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BS:BU").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("BW:BY").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CA:CC").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("CE:CG").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CI:CK").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CM:CO").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CQ:CS").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CU:CW").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("CY:DA").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DC:DE").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DG:DI").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DK:DM").NumberFormat = "[h]:mm:ss"
                         ActiveSheet.Range("DO:DR").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DS:DU").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("DW:DY").NumberFormat = "[h]:mm:ss"
                        ActiveSheet.Range("EA:EC").NumberFormat = "[h]:mm:ss"
                        
                       Selection.Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133)
                      
    
    
                        Else
                            ErrorYes = True
                        End If
                    End With
                  
                    If Err.Number > 0 Then
                        ErrorYes = True
                        Err.Clear
                        'Close mybook without saving
                        mybook.Close savechanges:=False
                    Else
                        'Save and close mybook
                        mybook.Close savechanges:=True
                    End If
                    On Error GoTo 0
                Else
                    'Not possible to open the workbook
                    ErrorYes = True
                End If
    
    
            Next Fnum
        End If
    
    
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
    
    
        'Restore ScreenUpdating, Calculation and EnableEvents
        With Application
            '.ScreenUpdating = True
            '.EnableEvents = True
            '.Calculation = CalcMode
        End With
     End Function

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review this discussion that also used Active<something> syntax in a loop https://www.utteraccess.com/forum/in...ic=2057857&hl=

    I don't do a lot of Excel VBA and will need to work with files and I don't want to try to build replica of yours.
    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.

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    The error happens in this section of the vba. It has to do with a range I think but I can't figure it out.
    Ensure that there are not empty cells in the first row (header).

    Also, no need to Select anything. Has to do with UI and often causes problems. So, we have to avoid it in automation.

    For example:
    Code:
        On Error Resume Next
        With mybook.Worksheets("qry_Daily_Export").UsedRange
            .Range("E:EC").NumberFormat = "[h]:mm:ss"
            .Range("F:F,J:J,N:N,R:R,V:V,Z:Z,AD:AD,AH:AH,AL:AL,AP:AP,AT:AT,AX:AX,BB:BB,BF:BF,BJ:BJ,BN:BN,BR:BR,BV:BV,BZ:BZ,CD:CD,CH:CH,CL:CL,CP:CP,CT:CT,CX:CX,DB:DB,DF:DF,DJ:DJ,DN:DN,DR:DR,DV:DV,DZ:DZ").NumberFormat = "General"
            If .Rows(1).SpecialCells(xlCellTypeBlanks) Is Nothing Then 'There are no blanks
                .Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133)
            End If
            With .Rows(1)
                .Font.Bold = True
                .EntireColumn.AutoFit
            End With
        End With

  6. #6
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    This is so close -THANK YOU.....The subtotals are appearing but they are only subtotalling 4,5,6 the rest are empty.

    This is a sample section of one of the reports

    Manager TW_Program_Code REP Total_Dials Total_TT 05/01/2020 DIALS 05/01/2020 MSTEAMS 05/01/2020 TT 05/01/2020 WEBEX
    MGR1 DTP CBM REP1 16 2:12:08 0 0:00:00 0:00:00 0:00:00
    MGR1 DTP CBM REP2 54 15:19:45 4 0:00:00 0:48:22 0:00:00
    MGR1 DTP CBM REP3 36 9:14:24 4 0:00:00 0:51:30 0:00:00
    MGR1 DTP CBM REP4 40 14:42:03 8 0:00:00 3:15:48 0:00:00
    MGR1 DTP CBM REP5 36 15:23:07 6 0:00:00 1:46:42 0:00:00
    MGR1 DTP CBM REP6 50 10:15:26 2 0:00:00 0:38:38 0:00:00
    MGR1 DTP CBM REP7 33 12:50:47 3 0:00:00 1:02:23 0:00:00
    MGR1 DTP CBM REP8 31 11:19:49 4 0:00:00 1:41:03 0:00:00
    DTP CBM Total 296 91:17:30 31


    Quote Originally Posted by accesstos View Post
    Ensure that there are not empty cells in the first row (header).

    Also, no need to Select anything. Has to do with UI and often causes problems. So, we have to avoid it in automation.

    For example:
    Code:
        On Error Resume Next
        With mybook.Worksheets("qry_Daily_Export").UsedRange
            .Range("E:EC").NumberFormat = "[h]:mm:ss"
            .Range("F:F,J:J,N:N,R:R,V:V,Z:Z,AD:AD,AH:AH,AL:AL,AP:AP,AT:AT,AX:AX,BB:BB,BF:BF,BJ:BJ,BN:BN,BR:BR,BV:BV,BZ:BZ,CD:CD,CH:CH,CL:CL,CP:CP,CT:CT,CX:CX,DB:DB,DF:DF,DJ:DJ,DN:DN,DR:DR,DV:DV,DZ:DZ").NumberFormat = "General"
            If .Rows(1).SpecialCells(xlCellTypeBlanks) Is Nothing Then 'There are no blanks
                .Subtotal Groupby:=2, Function:=xlSum, TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133)
            End If
            With .Rows(1)
                .Font.Bold = True
                .EntireColumn.AutoFit
            End With
        End With

  7. #7
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by mindbender View Post
    This is so close -THANK YOU.....The subtotals are appearing but they are only subtotalling 4,5,6 the rest are empty.

    This is a sample section of one of the reports

    Manager TW_Program_Code REP Total_Dials Total_TT 05/01/2020 DIALS 05/01/2020 MSTEAMS 05/01/2020 TT 05/01/2020 WEBEX
    MGR1 DTP CBM REP1 16 2:12:08 0 0:00:00 0:00:00 0:00:00
    MGR1 DTP CBM REP2 54 15:19:45 4 0:00:00 0:48:22 0:00:00
    MGR1 DTP CBM REP3 36 9:14:24 4 0:00:00 0:51:30 0:00:00
    MGR1 DTP CBM REP4 40 14:42:03 8 0:00:00 3:15:48 0:00:00
    MGR1 DTP CBM REP5 36 15:23:07 6 0:00:00 1:46:42 0:00:00
    MGR1 DTP CBM REP6 50 10:15:26 2 0:00:00 0:38:38 0:00:00
    MGR1 DTP CBM REP7 33 12:50:47 3 0:00:00 1:02:23 0:00:00
    MGR1 DTP CBM REP8 31 11:19:49 4 0:00:00 1:41:03 0:00:00
    DTP CBM Total 296 91:17:30 31

    When I ran it this morning it failed all together with the following error and nothing was done to the files
    Click image for larger version. 

Name:	Capture.PNG 
Views:	6 
Size:	10.2 KB 
ID:	41880

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-06-2020, 03:13 PM
  2. Replies: 4
    Last Post: 02-25-2020, 02:52 AM
  3. Replies: 2
    Last Post: 10-29-2019, 02:24 PM
  4. Replies: 11
    Last Post: 02-09-2016, 07:45 AM
  5. Adding labels in new column
    By Hello World in forum Queries
    Replies: 1
    Last Post: 10-13-2011, 08:57 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