Results 1 to 8 of 8

Issue with formatting separate Excel files in a module loop

  1. #1
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    6

    Issue with formatting separate Excel files in a module loop

    Good afternoon . .

    It has been a while since I have posted anything here. But I am stumpted to what is going on.

    I have code below that takes data based on a group code / subgroup code and create separate enrollment files.
    The code writes out the files and names them as the group / subgroup.

    Once I had the initial code working, I wanted to start to format each Excel file. Format the font, space out for better readability, put a bold header line (1st line) of the data
    and then go from there.

    The code goes through the first file and then stops. It acts like it won't finish and close before moving on. Stops at Error 1st time through.

    I then have stop Excel through Task Manager and kill the process and stop the macro that calls this.

    If anyone has any ideas, I would appreciate any advise.

    Thanks,

    Jay



    vvvvvvvvvvvvv MODULE CODE Below vvvvvvvvvvvvvvvvvv



    Const cstrQueryName = "CV_Roster_Summary_Query"
    Const cstrSummaryQueryName = "CV_Roster_Query_Parameter_By_Subgroup"
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim srst As DAO.Recordset2
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim Group As String
    Dim subgroup As String
    Dim CurTableName As String
    Dim NewTableName As String
    Dim outputFileName As String
    Dim xlApp As Object


    Dim xlWorkbook As Object
    Dim xlSheet As Object
    Dim ename As String

    ' Open pointer to current database
    Set dbs = CurrentDb()

    ' Open recordset on saved queries
    Set rst = dbs.OpenRecordset(cstrQueryName)

    ' Deletes and prior files created that are in the Exported folder
    Call Delete_External_Files

    ' Display data from one record and move to the next record until finished
    Do While Not rst.EOF
    ' MsgBox "Record #: " & rst![Subgroup_Count] & " Subgroup: " & rst![subgroup] & " Member Count: " & rst![Members]

    ' Now query initial table for each subgroup from main loaded table
    strSQL = ""
    Group = ""
    subgroup = ""
    CurTableName = ""
    NewTableName = ""
    TablesLoaded = ""
    TabName = ""
    ExportFileName = ""
    outputFileName = ""
    ename = ""

    Group = rst![Group]
    subgroup = rst![subgroup]

    dbs.Execute "Insert Into [CV_Roster_Subgroup_" & rst![Subgroup_Count] & "] Select * from [CV_Roster_Minus_Fields] where [SubGroup] = '" & subgroup & "'"
    CurTableName = "CV_Roster_subgroup_" & rst![Subgroup_Count]
    ' MsgBox CurTableName
    ' NewTableName = "CV_Roster_" & Group & "_" & subgroup
    ' MsgBox NewTableName
    TablesLoaded = rst![Subgroup_Count]
    TabName = Group & " " & subgroup

    ' Export table that was created to an Exce file
    ExportFileName = "" & Group & "-" & subgroup & "-Enrollment File"
    ' MsgBox "Export File Name: " & ExportFileName
    ' MsgBox "TabName: " & TabName

    ' outputFileName = CurrentProject.Path & ExportFileName & ".xls"
    outputFileName = "N:\Data Management\REPORTS - Scheduled\Jay\Enrollment By Subgroups\Exported Files" & ExportFileName & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, CurTableName, outputFileName, True, TabName
    DoEvents

    ' Format output file for better readability ---- NEW Code for formatting ---------------
    ename = "N:\Data Management\REPORTS - Scheduled\Jay\Enrollment By Subgroups\Exported Files" & ExportFileName & ".xlsx"

    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(ename)
    xlApp.Visible = False
    ' Set xlSheet = xlWorkbook.Sheets(TabName)
    Set xlSheet = xlWorkbook.ActiveSheet

    With xlSheet
    .Rows("1:1").Font.Bold = True
    .Cells.Select
    .Cells.EntireColumn.AutoFit
    .Range("A1").Select
    With ActiveWindow
    .SplitColumn = 0 ' ERROR 1st time through
    .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    End With

    xlWorkbook.Save
    xlApp.Quit
    DoEvents

    Set xlApp = Nothing
    Set xlWorkbook = Nothing
    Set xlSheet = Nothing

    'Turn on warnings
    DoCmd.SetWarnings True



    ' Get next record and re-loop
    rst.MoveNext
    Loop


    MsgBox TablesLoaded & " - Tables have been loaded with subgroup information !"
    rst.Close
    dbs.Close


    End Function

  2. #2
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    6
    Using Access 2013

  3. #3
    Bulzie is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    899
    Not sure, maybe it is not getting the correct ActiveWindow to set the Splitcolumn value? Can you step through it and see if it does what you need?

  4. #4
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    6
    It goes through the code and opens the 1st excel file and does the headings bold and underline, then chokes.
    It ends at the code I mentioned above.

    Any ideas why?

  5. #5
    Bulzie is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    899
    What does the error say. If you do a compile on that code, what does it say?

  6. #6
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    6
    There is no errors when you compile it.

    The 1st excel file opens and hangs. It acts like Excel is confused to what to do.

    It gets to the line I mention above --> SplitColumn = 0

    Then throws an error --> RunTime Error 424 Object Required

    I wouldn't think I need a set when I am referencing the active window.

    Thoughts?

  7. #7
    Bulzie is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    899
    Just shooting in dark here. If you comment out or step over .SplitColumn, does it hang on SplitRow? What do those 2 settings do in Excel, can you do without them? Are those the right words or way to reference those properties?

  8. #8
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,315
    Its been a while since I tried to use automation to control Excel.


    Maybe this will work:
    Code:
    Option Compare Database  '<< should be at the top of EVERY code module
    Option Explicit          '<< should be at the top of EVERY code module
    
    Function testExcel()  'my made up name, since you did not provide one :eek:
        Const cstrQueryName = "CV_Roster_Summary_Query"
        Const cstrSummaryQueryName = "CV_Roster_Query_Parameter_By_Subgroup"
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        '    Dim srst As DAO.Recordset2   'not used
        '    Dim qdf As QueryDef   'not used
        '    Dim strSQL As String   'not used
        Dim Group As String
        Dim subgroup As String
        Dim CurTableName As String
        Dim NewTableName As String
        Dim outputFileName As String
        Dim xlApp As Object
        Dim xlWorkbook As Object
        Dim xlSheet As Object
        Dim ename As String
    
        '    these were not delcared. I guessed at the types
        Dim TablesLoaded As Integer
        Dim TabName As String
        Dim ExportFileName As String
    
    
        ' Open pointer to current database
        Set dbs = CurrentDb()
    
        ' Open recordset on saved queries
        Set rst = dbs.OpenRecordset(cstrQueryName)
        rst.MoveFirst
    
        ' Deletes and prior files created that are in the Exported folder
        Call Delete_External_Files
    
        ' Display data from one record and move to the next record until finished
        Do While Not rst.EOF
            ' MsgBox "Record #: " & rst![Subgroup_Count] & " Subgroup: " & rst![subgroup] & " Member Count: " & rst![Members]
    
            ' Now query initial table for each subgroup from main loaded table
            '        strSQL = ""
            Group = ""
            subgroup = ""
            CurTableName = ""
            NewTableName = ""
            TablesLoaded = ""
            TabName = ""
            ExportFileName = ""
            outputFileName = ""
            ename = ""
    
            Group = rst![Group]
            subgroup = rst![subgroup]
    
            dbs.Execute "Insert Into [CV_Roster_Subgroup_" & rst![Subgroup_Count] & "] Select * from [CV_Roster_Minus_Fields] where [SubGroup] = '" & subgroup & "'"
            CurTableName = "CV_Roster_subgroup_" & rst![Subgroup_Count]
            ' MsgBox CurTableName
            ' NewTableName = "CV_Roster_" & Group & "_" & subgroup
            ' MsgBox NewTableName
            TablesLoaded = rst![Subgroup_Count]
            TabName = Group & " " & subgroup
    
            ' Export table that was created to an Exce file
            ExportFileName = "" & Group & "-" & subgroup & "-Enrollment File"
            ' MsgBox "Export File Name: " & ExportFileName
            ' MsgBox "TabName: " & TabName
    
            ' outputFileName = CurrentProject.Path & ExportFileName & ".xls"
            outputFileName = "N:\Data Management\REPORTS - Scheduled\Jay\Enrollment By Subgroups\Exported Files" & ExportFileName & ".xlsx"
    
            '**********************************
            ' You cannot use the RANGE argument when exporting.
            'from Help: A string expression that's a valid range of cells or the name of a range in the spreadsheet.
            ' This argument applies only to importing. Leave this argument blank to import the entire spreadsheet.
            'When you export to a spreadsheet, you must leave this argument blank.
            'If you enter a range, the export will fail.
            '        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, CurTableName, outputFileName, True, TabName
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, CurTableName, outputFileName, True
            '**********************************
    
            DoEvents
    
            ' Format output file for better readability ---- NEW Code for formatting ---------------
            ' *** could have used "outputFileName" instead of creating "ename" variable
            ename = "N:\Data Management\REPORTS - Scheduled\Jay\Enrollment By Subgroups\Exported Files" & ExportFileName & ".xlsx"
    
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Visible = False     'changed position in code
            Set xlWorkbook = xlApp.Workbooks.Open(ename)
            '    Debug.Print xlApp.ActiveWindow.ActiveSheet.Name
            ' Set xlSheet = xlWorkbook.Sheets(TabName)
            Set xlSheet = xlWorkbook.Worksheets("Sheet1")
            xlSheet.Activate
            '    Debug.Print xlApp.ActiveWindow.ActiveSheet.Name
    
            With xlSheet
                .Cells.Select
                .Cells.EntireColumn.AutoFit
                .Rows("1:1").Font.Bold = True
                .Range("A1").Select
            End With
    
            With xlApp
                .ActiveWindow.SplitColumn = 0
                .ActiveWindow.SplitRow = 1
                .ActiveWindow.FreezePanes = True
            End With
    
            xlWorkbook.Save
            DoEvents
            xlApp.Quit
            DoEvents
    
    
            'Turn on warnings
            DoCmd.SetWarnings True   'not sure why this line is here. Did not see where SetWarnings was turned off???
    
    
            ' Get next record and re-loop
            rst.MoveNext
        Loop
    
    
        Set xlApp = Nothing
        Set xlWorkbook = Nothing   ' I moved these out of the loop
        Set xlSheet = Nothing
    
        MsgBox TablesLoaded & " - Tables have been loaded with subgroup information !"
        rst.Close
        dbs.Close
    
    
    End Function
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Extracting number of files found by module
    By JeroenMioch in forum Access
    Replies: 4
    Last Post: 01-21-2016, 10:43 AM
  2. Replies: 3
    Last Post: 04-17-2014, 07:37 PM
  3. Saving Module Code to Text Files
    By ioMatt in forum Modules
    Replies: 2
    Last Post: 07-02-2011, 07:18 AM
  4. Replies: 1
    Last Post: 02-21-2011, 08:55 PM
  5. Replies: 0
    Last Post: 03-03-2010, 12:28 PM

Posting Permissions

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