Results 1 to 14 of 14

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
    8

    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
    8
    Using Access 2013

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,200
    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
    8
    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 online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,200
    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
    8
    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 online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,200
    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
    7,003
    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.)

  9. #9
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    8
    Steve:

    Good morning . . .

    I want to thank you for the updates.

    I reviewed the updates and wanted to give you some feedback. If I take out the code after the following:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, CurTableName, outputFileName, True, TabName
    DoEvents

    For the formatting the files export without an issue and the 6 files I have export just fine.

    However, when I uncomment the code and make changes based on your suggestions, the first file goes through and then abends again.
    The VBA code stops at:

    Set xlSheet = xlWorkbook.ActiveSheet("Sheet1")

    The error is as follows:

    Run-time error '438'

    Object doesn't support this property or method.

    Any further suggestions?

    Thanks,

    Jay

  10. #10
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,003
    Quote Originally Posted by Jaybird114 View Post
    I reviewed the updates and wanted to give you some feedback. If I take out the code after the following:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, CurTableName, outputFileName, True, TabName
    DoEvents
    Read this again about the TransferSpreadsheet command:

    '**********************************
    ' 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 '<<< correct syntax
    '**********************************


    Somehow the correct worksheet needs to be activated. Maybe try this:
    Change
    Code:
    Set xlSheet = xlWorkbook.Worksheets("Sheet1")
    to
    Code:
    Set xlSheet = xlWorkbook.Worksheets(CurTableName)


    Put the attached dB in a folder and open it. Clicking the button will save a formatted workbook in
    "N:\Data Management\REPORTS - Scheduled\Jay\Enrollment By Subgroups\Exported Files"

    The test code is in Module1.
    Your code I modified is in Module 2

    The dB is to show that the code executes with no errors. If you make the change to
    Code:
    Set xlSheet = xlWorkbook.Worksheets(CurTableName)
    you should get a formatted workbook.

    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    Jaybird114 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Location
    Albany, NY
    Posts
    8
    Steve:

    THANK YOU!

    That worked . .

    Jay

  12. #12
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,003
    Excellent!!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    I came too late to the party (as I read, I thought, "how can a hidden window be the active window without an explicit command to make it so?")
    But, I still have 2 cents I can add - Group is a reserved word and shouldn't be used as a variable name.
    See http://allenbrowne.com/AppIssueBadWord.html for more.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  14. #14
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,003
    Ack!!! I missed that! Good catch, Micron.
    (I hate missing things like that.....)
    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