UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Report Grouping To Separate Excel Worksheets From Access, Any Version    
 
   
bakersburg9
post Aug 7 2019, 09:33 AM
Post#21



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


QUOTE
...page breaks ... there are PrintTitleRows and PrintTitleColumns ... and that may be all you need to be done automatically.
wait, WHAT ?? I'm listening ! Where in the Design View do I get THOSE ???
Go to the top of the page
 
strive4peace
post Aug 7 2019, 02:08 PM
Post#22


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

done!

I think this has almost everything you want* I didn't mean to to make this so complicated, but that is because it has flexibility, and easy (for me) to find again ... so lucky you (smile).

Your query must have the field you are grouping by, which it does, so you're good. As you suggested, the group value is written, to column A if it is first ... and you have an option to hide Column A (ding ding)

If you want to hide the first column, there is a parameter to hide it, so presto! the names will be on each row, but hidden from view.

*except maybe page breaks ... don't have enough information for that. But there are PrintTitleRows and PrintTitleColumns ... and that may be all you need.

This function gives you the ability to (1) make sheets in one file, each named with a corrected form of your value, truncated to 31 characters -- or (2) make different files for each grouping.

I did a little bit of testing, and hope it works ok for you too!. Please let me know. If not, what did you used for parameters and what was the error?

~~~

If all this seems like a bit much, but you haven't figured out another way to get what you want**, may I ask you devote some time to trying? Ask questions if anything is confusing.

** I agree with WildBird that pivot tables might be another option ... but if you want separate files, it is better to do it that way to begin with.

Let me know what you think, Steve, either way ~ thanks

1. make a module with this code below. Name it something logical but different than any procedure, like mod_aExcel_ExportQueryGroups

There is one main function, aExcel_ExportQueryGroups, and two helper procedures, CorrectName and DeleteFile
CODE
'module: mod_aExcel_ExportQueryGroups
   '~~~~~~~~~~~~~~~~~~~~~~~~~ aExcel_ExportQueryGroups
   Function aExcel_ExportQueryGroups( _
      psQueryname As String _
      , psGroupField As String _
      , psPath As String _
      , Optional psFilename As String = "" _
      , Optional psFilePrefix As String = "" _
      , Optional psFileExtension As String = "xlsx" _
      , Optional psDateFormatCode As String = "" _
      , Optional psRow1Title As String = "" _
      , Optional pbHideA As Boolean = False _
      , Optional piTitleCols As Integer = 1 _
      ) As Long
      
   '190806, 7 strive4peace
   ' Break a query into groups for exportin to Excel.
   ' Create different sheet tabs in the same file,
   ' or many files with one sheet each.
  
      'PARAMETERS
      '  psQueryname is a query name.
      '  psGroupField = break by values for separate sheets/files
      '     must BE a field in the query.
      '     if it is listed first, column A can be hidden.
      '  psPath = path to write Excel file
      'OPTIONAL
      '  psFilename is the filename.
      '      "" if each group will be a different file that is automatically created.
      '        or, if different sheet tabs will be created for each group in ONE file,
      '        this is a name such as "MyExcelFile.xlsx"
      '  psFilePrefix is what to write, if anything, before the value in the filename
      '     if groups are in different files (psFilename = "")
      '  psDateFormatCode is the date format code for adding it to the file name
      '     default = "" but you may specify something like "yymmdd" or "yymmdd_hhnn"
      '  psRow1Title = Literal text to print.
      '     Use [Field] to substitute group field value
      '  pbHideA. True to HIDE Column A. Default=False.
      '  piTitleCols: if >1 and psRow1Title is specified, cells will be merged and boxed
      
      'RETURN
      '  number of records
      '
      'CALLS
      '  CorrectName
      '  DeleteFile
  
      aExcel_ExportQueryGroups = 0
      
      ' late binding
      Dim oAppExcel As Object _
         , oWb As Object _
         , oWs As Object
      'Reference to Microsoft Excel #.# Object Library
      '  for early binding
   '   Dim oAppExcel As Excel.Application _
         , oWb As Excel.Workbook _
         , oWs As Excel.Worksheet
  
      Dim db As DAO.Database _
         , rs As DAO.Recordset _
         , rsGroup As DAO.Recordset _
         , qdf As DAO.QueryDef
      
      Dim sQuerySQL As String _
         , sPathFile As String _
         , sFilename As String _
         , sSheetname As String _
         , sRow1Title As String _
         , sSQL As String _
         , sMsg As String _
         , sValue As String _
         , nRecords As Long _
         , nRowHeadings As Long _
         , nColTitle As Long _
         , i As Integer _
         , iCountFields As Integer _
         , iSheet As Integer _
         , iGroup As Integer _
         , iGroups As Integer _
         , iDataType As Integer _
         , iSheetsInNewWorkbook As Integer _
         , vValue As Variant _
         , bMakeFiles As Boolean
      
      Dim asFieldname() As String
      
      If Right(psPath, 1) <> "\" Then
         psPath = psPath & "\"
      End If
      
      If psFilename = "" Then
         'path specified. Different workbooks will be created
         sPathFile = ""
         bMakeFiles = True
      Else
         'file specified -- create worksheets in same workbook
         'future - count and break into files if too many
         sPathFile = psPath & psFilename
         bMakeFiles = False
      End If
      
      Set db = CurrentDb
        
      '--------------------- all records to write
      
      
      sSQL = "SELECT Count(*) as NumRecords " _
         & " FROM " & psQueryname
        
      Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
       With rs
         If .EOF Then
            Debug.Print "*** no records, " & Now
            Debug.Print psQueryname
            MsgBox "There are no records to export", , "No records"
            GoTo Proc_Exit
         End If
         nRecords = !NumRecords
         .Close
      End With
    
      Set qdf = db.QueryDefs(psQueryname)
      
      With qdf
         sQuerySQL = .SQL
         iCountFields = .Fields.Count
         iDataType = .Fields(psGroupField).Type
        
         'Array with Field Names for column headings
         ReDim asFieldname(iCountFields)
         '-1 for zero-based
         For i = 0 To .Fields.Count - 1
            asFieldname(i) = .Fields(i).Name
         Next i
      End With
      Set qdf = Nothing
      
      '--------------------- groups
      sSQL = "SELECT DISTINCT " & psGroupField & "  FROM " _
         & psQueryname & " as q" _
         & " WHERE Not IsNull(" & psGroupField & ")" _
         & " ORDER BY " & psGroupField
  
      Set rsGroup = db.OpenRecordset(sSQL, dbOpenSnapshot)
      
      With rsGroup
         If .EOF Then
            Debug.Print "*** no groups, " & Now
            Debug.Print psQueryname
  
            MsgBox "There are no groups to export", , "No group records"
            GoTo Proc_Exit
         End If
         .MoveLast
         iGroups = .RecordCount
         .MoveFirst
      End With
  
      'data is ready to write
      Set oAppExcel = CreateObject("Excel.Application")
      With oAppExcel
         iSheetsInNewWorkbook = .SheetsInNewWorkbook
         .Visible = True 'let user see what is happening
         .EnableEvents = False 'don't run any code
  
         If bMakeFiles = False Then
            'One workbook, with #Groups sheets
            'future: split if too many
            .SheetsInNewWorkbook = iGroups
            iSheet = 0 'this will be incremented
         Else
            'Lots of workbooks with one sheet each
            .SheetsInNewWorkbook = 1
         End If
      
      End With
      
      'Add a new Workbook
      Set oWb = oAppExcel.Workbooks.Add
      Set oWs = oWb.Sheets(1)
  
      iGroup = 0
      sRow1Title = ""
      '---------------------------- loop through groups
      Do While Not rsGroup.EOF
         iGroup = iGroup + 1 'next Group record
   'If iGroup >= 4 Then Stop
         With rsGroup
            vValue = .Fields(psGroupField)
            
            If psRow1Title <> "" Then
               sRow1Title = Replace(psRow1Title, "[Field]", vValue)
            End If
         End With 'rsGroup
        
         sMsg = iGroup & " of " & iGroups & " groups: " & vValue
         Debug.Print sMsg, Now()
         Application.SysCmd acSysCmdSetStatus, sMsg
              
         'correct bad characters
         sValue = CorrectName(vValue)
        
         'get file name
         If bMakeFiles = True Then
            sFilename = psFilePrefix & sValue
            If psDateFormatCode <> "" Then
               sFilename = sFilename & "_" & Format(Now, psDateFormatCode)
            End If
            'add extension
            sFilename = sFilename & "." & psFileExtension
            'lots of files with one sheet in each
            sPathFile = psPath & sFilename
            'make new workbook (with one sheet)
            Set oWb = oAppExcel.Workbooks.Add
            Set oWs = oWb.Sheets(1)
         Else
            'set sheet to next one
            iSheet = iSheet + 1
            Set oWs = oWb.Sheets(iSheet)
         End If
        
         'select sheet to filter and freeze
         oWs.Select
        
         'sheetname max is 31 characters
         sSheetname = Left(sValue, 31)
  
         'delimit value, format numbers and dates
         If iDataType = 10 Then
            'string - probably most common
            vValue = """" _
               & Replace(vValue, """", """""") _
               & """"
         Else
            'not changing for now ... assuming number
            'todo: format as US if decimal character <> "."
            '-------------------- todo: write code
            '1,2,3,4 = whole.5 = cur. 6,7 = float. 8 = date.
         End If
        
         'get information from query
         sSQL = "SELECT q.* FROM " & psQueryname & " as q " _
            & " WHERE " & psGroupField & " = " & vValue
                          
         'open recordset for group
         'close previous recordset if not on first group
         If iGroup > 1 Then
            rs.Close
         End If
        
         Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
        
         '----- where to write stuff
         If sRow1Title <> "" Then
            If piTitleCols > 1 Then 'boxed -- skip row below
               nRowHeadings = 3
            Else
               nRowHeadings = 2
            End If
         Else
            nRowHeadings = 1
         End If
        
         If pbHideA = True Then
            nColTitle = 2
         Else
            nColTitle = 1
         End If
      
         '---------------------------------------export group recordset to worksheet
         With oWs
            
            'write column headings in nRowHeadings
            'NateO's way ...
            Let .Range("a" & nRowHeadings).Resize(1, iCountFields).Value = asFieldname
            
            'write data: Copy Recordset one row below the column headings
            .Range("a" & nRowHeadings + 1).CopyFromRecordset rs
      
            'Rename Worksheet
            .Name = sSheetname
              
            '------------------------- Format
            With .Cells.Font
               .Name = "Calibri"
               .Size = 10
            End With
            
            'xlDiagonalDown 5
            'xlDiagonalUp 6
            'xlEdgeLeft 7
            'xlEdgeTop 8
            'xlEdgeBottom 9
            'xlEdgeRight 10
            'xlInsideVertical 11
            'xlInsideHorizontal 12
            
            'column heading row
            With .Range(.Cells(nRowHeadings, 1), .Cells(nRowHeadings, iCountFields))
               .VerticalAlignment = -4108   'xlCenter
               .Font.Size = 8 'make larger if desired
               .HorizontalAlignment = -4131 'xlLeft, xlHAlignLeft
               With .Interior
                  .Color = RGB(225, 225, 225) 'light gray
               End With
               For i = 7 To 12 'outer and inner borders
                  With .Borders(i)
                     .LineStyle = 1 'xlContinuous
                     .Color = RGB(150, 150, 150) 'medium gray
                     .Weight = 2 'xlThin
                  End With
               Next i
            End With
  
            .Range("C" & nRowHeadings + 1).Select
                    
            'Hide Column A?
            If pbHideA = True Then
               .Columns(1).EntireColumn.Hidden = True
            End If
            
            'set margins, orientation, header
            'do this last in With block
            With .PageSetup
               'title rows is 1 to the row headings
               .PrintTitleRows = "1:" & nRowHeadings
               'title columns
               If pbHideA Then
                  .PrintTitleColumns = "B:B"
               Else
                  .PrintTitleColumns = "A:B"
               End If
              'tab name, date, page, total pages
              '&[Tab] - 8/7/2019 10:43:00 AM - &[Page]/&[Pages]
              .RightHeader = "&""Times New Roman,Italic""&10&A - " & Now() & " - &P/&N"
              .LeftMargin = 36 'oAppExcel.InchesToPoints(0.5)
              .RightMargin = 36
              .TopMargin = 36
              .BottomMargin = 36
              .HeaderMargin = 24
              .FooterMargin = 24
              .CenterHorizontally = True
              .Orientation = 2 'xlLandscape
            End With
  
         End With 'oWs
        
         ' turn on the auto filter
         oAppExcel.Selection.AutoFilter
         'do best-fit after filter arrows, instead of before?
      
         With oWs
            'best-fit columns
            '-- done after filter arrows so column heading insn't chopped
            '        but before title row is written
            .Range(.Columns(1), .Columns(iCountFields)).EntireColumn.AutoFit
            
            'title row
            If sRow1Title <> "" Then
               With .Cells(1, nColTitle) 'title is in row 1
                  .Value = sRow1Title
                  .Font.Size = 12   'adjust?
                  .Font.Bold = True
               End With
               If piTitleCols > 1 Then 'box title if > 1 column
                  With .Range(.Cells(1, nColTitle), .Cells(1 _
                     , nColTitle + piTitleCols - 1))
                     .MergeCells = True
                     For i = 7 To 10 'outer borders
                        With .Borders(i)
                           .LineStyle = 1 'xlContinuous
                           .Color = RGB(100, 100, 100) 'dark gray
                           .Weight = -4138 'xlMedium
                        End With
                     Next i
                  End With
               End If
            End If
            
            'best-fit rows
            .Cells.EntireRow.AutoFit
              
         End With 'oWs
        
         'freeze panes -- 2 columns, rows to just below heading
         'don't do things after feezing ...
         oAppExcel.ActiveWindow.FreezePanes = True
        
         'save file and close
         If bMakeFiles = True Then
            'close and specify PathFile to save
            If sPathFile <> "" Then
               'delete path\file if it already exists
               If Not DeleteFile(sPathFile) Then
              
                  sMsg = "Can't delete " & sPathFile _
                     & vbCrLf & vbCrLf & "Click OK to continue if you switched and closed while this message was open"
                  If MsgBox(sMsg, vbOKCancel, "Error naming file, OK if closed now") = vbOK Then
                     If Not DeleteFile(sPathFile) Then
                        sMsg = "Can't delete " & sPathFile _
                           & vbCrLf & vbCrLf & "change parameters and try again"
                        
                        Debug.Print sMsg, "Aborting " & Now()
                        MsgBox sMsg, , "Error replacing file, Aborting"
                        GoTo Proc_Exit
                     End If
                  Else
                     'cancel
                     GoTo Proc_Exit
                  End If
                  
               End If
            
               oWb.Close True, sPathFile
               sPathFile = ""
            End If
         Else
            'see if iSheet too big?
         End If
        
         ' next group
         rsGroup.MoveNext
      Loop
      
      'save file and close
      'this will happen if one workbook
      'and lots of sheets
      If sPathFile <> "" Then
         'delete file if it already exists
         Call DeleteFile(sPathFile)
         'select first sheet
         oWb.Sheets(1).Select
         'save file
         oWb.Close True, sPathFile
      End If
      
      Set oWs = Nothing
      Set oWb = Nothing
  
      aExcel_ExportQueryGroups = nRecords
  
   Proc_Exit:
      On Error Resume Next
  
      If Not rs Is Nothing Then
         rs.Close
         Set rs = Nothing
      End If
      
      If Not rsGroup Is Nothing Then
         rsGroup.Close
         Set rsGroup = Nothing
      End If
      If Not rs Is Nothing Then
         rs.Close
         Set rs = Nothing
      End If
      Set db = Nothing
        
      Set oWs = Nothing
      If Not oWb Is Nothing Then
         oWb.Close True, sPathFile
         Set oWb = Nothing
      End If
      
      If Not oAppExcel Is Nothing Then
         With oAppExcel
            'put SheetsInNewWorkbook back to way it was before
            .SheetsInNewWorkbook = iSheetsInNewWorkbook
            'quit this instance of Excel (CreateObject used)
            .Quit
         End With
         ' release Excel.Application object variable
         Set oAppExcel = Nothing
      End If
      
      If aExcel_ExportQueryGroups <> 0 Then
         sMsg = "Exported " & nRecords & " records in " _
            & iGroups & " groups" _
            & vbCrLf & vbCrLf & "created " _
            & IIf(bMakeFiles = True _
               , iGroups & " Files" _
               , "1 File with " & iGroups & " Sheets")
          
         Debug.Print "---- Done " & Now()
         Debug.Print Space(5) & sMsg
         sMsg = sMsg & vbCrLf & vbCrLf & "Open Path? "
        
         If MsgBox(sMsg, vbYesNo, "Done") = vbYes Then
            Application.FollowHyperlink psPath
         End If
      End If
      
      Application.SysCmd acSysCmdClearStatus
      Exit Function
    
   Proc_Err:
      MsgBox Err.Description _
         & vbCrLf & vbCrLf & sPathFile & " may be open or file name is bad", , _
           "ERROR " & Err.Number _
           & "   Error writing file aExcel_ExportQueryGroups"
      Resume Proc_Exit
      Resume
      
   End Function
  
    
   '~~~~~~~~~~~~~~~~~~~~~~~~~ CorrectName
   Function CorrectName( _
      ByVal psName As String _
      , Optional psReplaceCharacter As String = "_" _
      ) As String
   'strive4peace
  
      Dim i As Integer _
         , sName As String _
         , sChar As String * 1 _
         , sLastChar As String _
         , sNewChar As String
  
      'PARAMETERS
      '  psName is the string you want to correct
      'RETURNS
      '  corrected name
      
      'EXAMPLE USEAGE
      'in code to fix names before writing files
      '  or renaming application objects
      '  sNewName =  CorrectName(sName)
      'in a query:
      '  field --> CorrectName: CorrectName([Fieldname])
      
      'EXAMPLE
      ' ? CorrectName("as(,48209j@##@!")
      ' --> as_48209j_
      ' ? CorrectName("Fred Flintstone")
      ' --> Fred_Flintstone
      ' ? CorrectName("Fred Flintstone","")
      ' --> FredFlintstone
      
      CorrectName = ""
      
      If Len(Nz(psName)) < 1 Then Exit Function
      
      psName = LTrim(Trim(psName))
      
      For i = 1 To Len(psName)
         sChar = Mid(psName, i, 1)
        
         'also replaces spaces
         If InStr("`!@#$%^&*()+=|\:;""'<>,.?/ ", sChar) > 0 Then
            sNewChar = psReplaceCharacter
         Else
            sNewChar = sChar
         End If
    
         If sNewChar <> "" Then
            If (sLastChar = psReplaceCharacter _
                  And sNewChar = psReplaceCharacter) Then
               'SKIP - leave the same for multiple characters to replace in a row
            Else
               sName = sName & sNewChar
            End If
         End If
    
         sLastChar = sNewChar
      Next i
  
      CorrectName = sName
          
   End Function
  
   '~~~~~~~~~~~~~~~~~~~~~~~~~ DeleteFile
   Function DeleteFile(psPathFile As String) As Boolean
   'strive4peace
   'True is PathFile is okay to use
   'False if it couldn't be deleted -- maybe it is open?
      On Error Resume Next
      DeleteFile = True
      Dim sFile As String
      sFile = ""
      sFile = Dir(psPathFile)
      If sFile <> "" Then
         Kill psPathFile
         DoEvents
         'make sure file is gone
         If Dir(psPathFile) <> "" Then
            DeleteFile = False
         End If
      End If
   End Function


2. and here is your quickie little launcher to make different files for each group of data:

CODE
Sub Steve_Launch()
   's4p 190807
      'Steve, change whatever you want in here to send different parameters
      Dim sQueryname As String _
         , sGroupField As String _
         , sPath As String _
         , sFilename As String _
         , sFilePrefix As String _
         , sRow1Title As String _
         , iTitleCols As Integer
        
      sQueryname = "qMyQueryName"   ' ------------- customize
      sGroupField = "PDatInception"  '"MyFieldname"   ' ------------- customize
      
      '-------------------------------------- customize and choose
      'if you want to make a file for each group
      ' set filename to "",
      ' and prefix to whatever you want before each group value,
      ' if anything
      sFilename = ""
      sFilePrefix = "JobCostBilling_"
      '  OR
      'uncomment if you want all tabs to be in the same workbook,
      'and specify a filename to create yourself
      'sFilename = "MyExcelFile.xlsx"
      '--------------------------------------
      
      'create a title row above headings row
      '[Field] will be replaced with group value
      sRow1Title = "Job Cost and Billing Detail - Project Director is [Field]" ' ------------- customize
      'merge title across 6 columns
      iTitleCols = 6 'change to whatever you want. 1=no box or merge
      
      sPath = CurrentProject.Path & "\Reports\" ' ---------- customize if desired
      'if path doesn't exist, make it
      If Dir(sPath) = "" Then 'this only works for one folder level
         MkDir sPath
      End If
  
      'export the data to Excel
      Call aExcel_ExportQueryGroups(sQueryname, sGroupField _
         , sPath, sFilename, sFilePrefix, _
         , , sRow1Title, True, iTitleCols)
  
   End Sub

The sheets are somewhat formatted, columns are best-fit, filter arrows are added. Attached is a picture of what a sheet that it creates might look like.

Attached File(s)
Attached File  aExcel_ExportQueryGroupsZ.png ( 10.52K )Number of downloads: 0
 

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 03:06 PM
Post#23



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Wow, thanks!!!!!!!!!!!! I'll get back to you - let you know how it went - THANK YOU !!!! cool.gif

If there's a PM named Fred Flintstone, I'm screwed....

Steve
This post has been edited by bakersburg9: Aug 7 2019, 03:12 PM
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 04:39 PM
Post#24



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Still working - was trying to figure out how to send e-mail via GMAIL, and it's above my pay grade - I watched a video with no sound, and they were doing something using the object browser, which I've never used before - he was clicking things, but with no sound, I was confused, was he selecting them, or what ? Appreciate your help - I'll get to it - having a stressful day - thanks again - I'll get to it ! cool.gif

Steve
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 05:16 PM
Post#25



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


QUOTE
what did you used for parameters and what was the error?
you mean BEFORE ? I'm not sure what you mean as far as parameters, sorry...
Go to the top of the page
 
strive4peace
post Aug 7 2019, 05:34 PM
Post#26


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

you're welcome!

*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

Paste code after compiler directive(s) -- anything starting with "Option" at top.
Paste the code from both blocks -- the function and its 2 helpers, and then add a couple blank lines below that, and put your Steve_launch sub on that module sheet too.
once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

if you do have errors, Fix any errors on the highlighted lines.
Add needed references, remove unnecessary references, resolve missing references -- (from the menu: Tools, References...) -- my code shouldn't have any reference issues because it uses late binding (which means your version of Excel will be referenced at runtime, as opposed to specifying it when code compiles), but you may have errors in other code.

If something doesn't compile and you don't know how to fix it, put a single quote at the beginning of the line to comment it -- or maybe comment the whole procedure. You can turn on the Edit toolbar in VBA. then you can highlight a whole block of code and click the icon to comment or uncomment everything selected.

keep compiling until nothing happens (this is good!) -- then Save

Click the diskette icon on the toolbar to save the module. Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc.

IMPORTANT: do NOT name the module the same as any procedure. Give it a unique name like "mod_whatever" or "bas_whatever" -- or mod_aExcel_ExportQueryGroups, as I suggested for this. 'aExcel' is my notation for 'automation with Excel'

~~~~~
it is a good idea, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up by the compiler, as well as other errors than can be fixed before running.
CODE
Option Explicit ' require variable declaration


============================
once the code is in and compiles, change qMyQueryName in Steve_Launch to your query name. then debug, compile again ... then save, then ... ready for this? ... click in Steve_Launch and press F5 to run it! Once you see what it does, you can make adjustments if you want to. Easy squeezy!

============================
PrintTitleRows and PrintTitleColumns are set on the Sheet tab of Page Setup. They are like freezing rows and columns for printing. Click the little launcher icon in the lower right of the Page Setup Group, like the attached picture, which shows that rows 1-3 will be at the top of each page and, assuming Column A is hidden, Column B will appear on every page too.

============================
> "I'm not sure what you mean as far as parameters"

Parameters are variables that you send to the function for your specific data. The Steve_launch procedure sets up the parameters and makes them easy for you to change -- and then calls aExcel_ExportQueryGroups, sending it the information it needs to process what you want smile.gif there are other parameters too, but skipping their discussion for now.

============================
The only main thing I can think that may need to be added is perhaps another parameter to sort ... I originally wrote this by filtering the main query recordset, but decided to change that ... and thus any sorting might need to be set up again too ~ ... short of having code read what is in the Order By clause, which I didn't do (but could -- get this working then twist my arm and I'll add that if you need it).

Let's get this working for you! -- and then we can tweak it. I really like this function! so thanks for asking how to do it, Steve. I've written similar procedures in the past but couldn't find them when I looked. I'll keep better track of this one! it would make a good video lesson too. Lots of folks need to do this. The beauty of this is that it works with any query you create!



This post has been edited by strive4peace: Aug 7 2019, 06:08 PM
Attached File(s)
Attached File  PageSetup_Sheet_launcher.png ( 30.38K )Number of downloads: 1
 

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 06:06 PM
Post#27



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Crystal,
I'm so sorry - I'm in a bad situation - I didn't want to PM you, because people don't read them sometimes - basically, the controller, my "boss" at this temp job thinks I magically know what to do - there is this Construction Accounting Software called Timberline - and they have a ton of transactions where they have job costs, but haven't been billed - I don't need him to do the technical side, but he needs to tell me what he wants - before I got this data dump, he had me manually updating an Excel workbook with data from hard-copy print reports, by Job number - so there's this Excel workbook with hundreds and hundreds of lines - before I got the data dump, I was, in addition to ad hoc projects, just plugging along updating this thing. What I've been doing is cleaning up the data - there are several fields, like OFFICE (number), DIVISION, YEAR OF INCEPTION, Project Director - the key to all of this is baked into the job number, which is xxx-xx-xxxxx - the first character means this, the 2nd character means that - the last five digits is a sequential number starting either with 6000, or 00000, in cases where 2 offices share the same office number - crazy, I know - so the Anaheim office is number "1," and so is the Chicago office - but if the last five digits starts with "60001," then it's Chicago, if it starts with 00001, then it's Anaheim - crazy, right? Anyway, I cleaned up the data for him to make it easier to do reports, analytics, etc. So he sets up a meeting with me, titled: "Project Status" - and I think: "holy [censored]!" does he think I'm still manually updating that Excel workbook ? Funny thing is, I mentioned it in passing, and he said: "What's that?" so obviously he wasn't - *WHEW* so I give this big schpeil, and it seemed real positive (well, nothing negative), and he asked for a copy of my workbook with the data all cleaned up - I'm thinking: "Great, he's going to get back to me, and, TELL ME WHAT HE WANTS! I'm afraid to address the Elephant in the room, and say: "Hey, you know I'm waiting for you to tell me what you want ! when I did my dog-and-pony show, I did real well, and maybe he thinks great, this guy knows what to do - I don't ... so as for the whole page break thing, I might get to it, but it's not high on the priority list right now - my #1 priority is to not stroke out, because I feel like my heart is going to jump out of my chest!!!

So can you think of anything he might want, something I can at least say: "Well, I've been working on this and this...." ???

This is almost exactly like a Seinfeld episode where George's boss thought George followed him into the bathroom, and he (George's boss) was telling him what to do, but George DIDN'T follow him into the bathroom! So George was freaking out, because he didn't want to tell his boss he didn't know what to do - so, again, can you think of any analytics I could do ? As a reminder, the data is of job costs which weren't billed - I've attached a copy - thanks! And sorry you did all that, but hopefully I'll get to it soon - I've figured a work-around for the page-break thing - maybe I'll share it with you... thanks again for all the help! If I was a praying person, I'd be praying like crazy ! Thanks again, Steve
This post has been edited by bakersburg9: Aug 7 2019, 06:08 PM
Attached File(s)
Attached File  Cost_Zero_Billing_2017_01_01_to_2018_12_31_SAMPLEonly.zip ( 101.48K )Number of downloads: 1
 
Go to the top of the page
 
strive4peace
post Aug 7 2019, 06:25 PM
Post#28


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

I've heard of Timberline -- its been around for a long time.

Too bad whatever is exported is in flat format and then needs to be parsed (separated) and related! Ideally, you could get an ODBC connection to Timberline, even if it is just read-only! That is so much better than having to (waste time) writing code to separate stuff from the flat files. Then you could link to the Timberline tables and write your own queries to get what you want. I feel your pain! Somewhere, the things that are parsed from the job number, are probably already being stored too.

ODBC means that a generic way to access the data can be used. Access can link to ODBC tables!!! yay! Access can link to almost anything ~

> " If I was a praying person ... praying like crazy!"

thanks, Steve. Good thoughts are good too! Wishing you the best. Trying to help ~


This post has been edited by strive4peace: Aug 7 2019, 06:35 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
strive4peace
post Aug 11 2019, 04:18 PM
Post#29


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

on the Object Browser ... what a wonderful place to learn more about objects, properties, and methods! And definitely something to get comfortable with ...

In case it helps, I wrote some stuff about the Object Browser here:

https://www.UtterAccess.com/forum/index.php...=0#entry2726296

those instructions refer to looking stuff up in the Scripting library ... but you don't have to change to that one ~ its good to limit the scope of what you see to something though




This post has been edited by strive4peace: Aug 11 2019, 04:21 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 11:21 AM
Post#30



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Thanks SO much for all your efforts - yes, I need to learn about object browser - thanks for the positive thoughts - I'm really 'nervy,' I have to say - this guy STILL hasn't told me what he wants - the other day he said: "charts and graphs" - so I did some pretty cool charts, even thought that's not my forte at ALL - I just wish he would tell me what he WANTS !!!!

Thanks again for all your efforts...

Steve
Go to the top of the page
 
strive4peace
post Aug 12 2019, 02:37 PM
Post#31


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

you're welcome!! but don't just say thank you ....

its hard to imagine what using Access, or any Office app, would be like without the Object Browser -- glad you are discovering how wonderful it is!!!

Back to your original post ... I get it -- now you're in a holding pattern, waiting for direction -- and you're asking all kinds of questions as you're pulled this way and that. Maybe though, focus on one thing at a time till you get it done. Prioritize and re-evaluate priorities each day. Show your boss what he can get!!! If he knew how to do that himself, and define what he wants better, he wouldn't need you. Give him options and choices.

You saw that it would be beneficial to separate data by each project manager's name ... so do it! Excel files are a great way to report information from a database that Access can link to, and most everyone can easily open them. Then they can calculate, project, chart, and whatever else they want to do.

I took the time to write code to help you ... please, make the time to run it. If you have questions how to make it work, all you have to do is ask ...

Maybe the query you built is different now ... that is ok! Send its name, whatever it is. Group by whatever you want! If you want to group by multiple fields, make a calculated field that concatenates the values.

For now, make a module with the code I gave you. Follow steps to compile and save. Then just change few lines where indicated, compile and save again. Then run. Geez, Steve, I spent a day on this... surely you can spend an hour or two ... when you're rested and clear though, ... so maybe tomorrow morning? or the next morning? or maybe this weekend? sometime though!

Modify Steve_Launch for your information, compile, save, ... and run!!! I really want to know what you think, which you can't tell me until you actually give it a try ...

Add your value

best,
This post has been edited by strive4peace: Aug 12 2019, 02:55 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 02:53 PM
Post#32



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Do I create the Module in Access or Excel ?
sorry, this is a lot... I figured out a way to handle the page break issue - it's sloppy, but it will work - I don't know what this guy wants, and I'm 90% sure he thinks I'm working on something great - I did make some pretty cool charts, but that's just going to prolong this... Edit: I just sent you a long PM explaining this really bad situation, and it wouldn't go through....
This post has been edited by bakersburg9: Aug 12 2019, 02:59 PM
Go to the top of the page
 
strive4peace
post Aug 12 2019, 03:16 PM
Post#33


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

good to hear back from you. I know it's a lot ... nothing you don't need though ...

> "Do I create the Module in Access or Excel ?"
create the module in Access!!! where you have a query .... with a field in that query you want to group/separate by ...follow instructions to Create a Standard (General) Module


> "page break issue"
really, that's a minor detail ... just need to specify logic for when that needs to happen -- not the first step though!

===

on charting ... we can help you with that too -- but let's cross one bridge at a time.

Btw, did you realize you can also use a QUERY to make charts of a sort ... if all you need are bars to graphically show the relative magnitude of a number, its really easy using something like String( someNumber, ChrW(9600))

Attached an image. Easy to show numbers visually with a calculated number of block characters.

===
you will find the code I gave you very flexible, not just for what you asked about. Run it. Then see how you can change the Steve_Launch procedure to get something really different!

Try it, you'll like it wink.gif


This post has been edited by strive4peace: Aug 12 2019, 03:26 PM
Attached File(s)
Attached File  barChart_String_Nbr_ChrW.png ( 3.27K )Number of downloads: 0
 

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 03:29 PM
Post#34



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Crystal,

Wouldn't know where to start, other than create the module - In Access thumbup.gif

I've attached CostNoBillingUA db, to narrow down my 'challenge'

There's only one table, query and report, so no need to get into the names

but for what it's worth, the query the report is based on is: "qCostZeroNoInvByPM"

thanks !

Update - dad gummit, it wouldn't let me attach - I'll try again
This post has been edited by bakersburg9: Aug 12 2019, 03:30 PM
Go to the top of the page
 
strive4peace
post Aug 12 2019, 03:33 PM
Post#35


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

I don't see an attachment. Did you ZIP it? But really, I wouldn't know what to do with it!
> "one table, query and report"that query has a name -- put it in Steve_Launch. that query has a field you want to group by -- put it in Steve_Launch.

Trying to give YOU knowledge since you're the one who knows .... given you lots of instructions. Maybe it is too late today to start from the beginning and read again ... must push out other pressures and focus on one thing at a time.

on PM -- I can't get that, sorry. Please describe more and ask your questions here.


This post has been edited by strive4peace: Aug 12 2019, 03:35 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 03:35 PM
Post#36



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


I shrunk it to <2mb
Attached File(s)
Attached File  CostNoBillingUA.zip ( 62.21K )Number of downloads: 2
 
Go to the top of the page
 
strive4peace
post Aug 12 2019, 03:52 PM
Post#37


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

looking at the download ...



This post has been edited by strive4peace: Aug 12 2019, 03:57 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 03:58 PM
Post#38



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Crystal,

I followed the steps, everything compiled, no need to 'troubleshoot' - got an error when I pressed F5 Steve_Launch - there are no groups to export
Go to the top of the page
 
bakersburg9
post Aug 12 2019, 04:00 PM
Post#39



Posts: 5,562
Joined: 2-November 04
From: Downey, CA


Here's the latest version
Attached File(s)
Attached File  CostNoBillingUA159p.zip ( 89.21K )Number of downloads: 0
 
Go to the top of the page
 
strive4peace
post Aug 12 2019, 04:04 PM
Post#40


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve, attached is a database back
1. saved your query without the specified JobNums because there weren't any records. that version is called: qCostZeroNoInvByPM_All

2. changed Steve_Launch to:

CODE
Sub Steve_Launch()
's4p 190807, 190812
  'Steve, change whatever you want in here to send different parameters
  '
  '   CLICK HERE
  '      press F5 to Run!
  '
  
  Dim sQueryname As String _
     , sGroupField As String _
     , sPath As String _
     , sFilename As String _
     , sFilePrefix As String _
     , sRow1Title As String _
     , iTitleCols As Integer
    
  sQueryname = "qCostZeroNoInvByPM_All"   ' ------------- customize
  sGroupField = "PDatInception"  '  ' ------------- customize
  
  '-------------------------------------- customize and choose
  'if you want to make a file for each group
  ' set filename to "",
  ' and prefix to whatever you want before each group value,
  ' if anything
  sFilename = ""
  sFilePrefix = "JobCostBilling_"
  '  OR
  'uncomment if you want all tabs to be in the same workbook,
  'and specify a filename to create yourself
  'sFilename = "MyExcelFile.xlsx"
  '--------------------------------------
  
  'create a title row above headings row
  '[Field] will be replaced with group value
  sRow1Title = "Job Cost and Billing Detail - Project Director is [Field]" ' ------------- customize
  '  OR, for now title across top
  sRow1Title = ""
  
  'merge title across 6 columns
  iTitleCols = 1 'no box or merge -- wouldn't happen anyway since no row title is defined
  
  sPath = CurrentProject.Path & "\Reports\" ' ---------- customize if desired
  'if path doesn't exist, make it
  If Dir(sPath) = "" Then 'this only works for one folder level
     MkDir sPath
  End If

  'export the data to Excel
  Call aExcel_ExportQueryGroups(sQueryname, sGroupField _
     , sPath, sFilename, sFilePrefix, _
     , , sRow1Title, True, iTitleCols)

End Sub

then ran the code -- click in Steve_Launch and press F5 to Run!
5 workbooks are created in a directory called Reports below the path of the database.
attached is the Access database zipped up.
You could put this code in any database you create, and simply change Steve_Launch, as I did ...



Attached File(s)
Attached File  CostNoBillingUA_190812_.zip ( 89.44K )Number of downloads: 0
 

--------------------
have an awesome day,
crystal
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 06:48 PM