VBA correction to export multi-query data to excel (1 Viewer)

Punice

Registered User.
Local time
Today, 13:06
Joined
May 10, 2010
Messages
135
This code works fine to file a year's profit & loss spreadsheet. Tried to get it to work correctly to use the same queries, but only to fill months Jun thru Dec for 2018, in columns B-G. Then, for 2019, I'll use the same code in 2019's db to add months Jan thru Jun in columns H-M to complete a 'report' for an insurance audit. Here is my code that needs modified. I think I attached a model xlsx file You will need to change the name to 'PnL-LI.xlsx".

Code:
Private Sub PopLI_PL_Click()
    Dim xlApp As Excel.Application                      'declares the variables
    Dim lngLastDataRow As Long
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer
    Dim xlCol As Integer
    Dim qry As QueryDef
    Dim rst As Recordset
    Dim xlQry As Integer
    Dim GetDBPath As String
    Dim strFolder_Path As String
    Dim strFolder_PathNew As String
    Dim strFile_NameNew As String
    Dim PrevYear As Integer
    Dim C As Integer
    
    PrevYear = DLookup("[Biz_Year]", "tblProfitLoss")
    
    'Gets the folder name for this busines year's database
    GetDBPath = CurrentProject.Path
    strFolder_Path = GetDBPath                           'Like: "C:\12_R11_TY-18"
    strFile_NameNew = strFolder_Path & "" & "PnL-" & "LI" & ".xlsx"
   
    If Dir(strFile_NameNew) = "" Then
        MsgBox "You need to create the 'PnL-LI.xlsx' file."
        Exit Sub
    Else

    Set xlApp = New Excel.Application                     'creates the variable references
    Set xlWB = xlApp.Workbooks.Open(strFile_NameNew)
    Set xlWS = xlWB.Worksheets("Sheet1")
    
    Me.CreateLIWCI_label.BackColor = vbYellow             'at start of sheet filling
    
    xlQry = 1                                             'initializes case counter (ie., query index)
    
    Do
        Select Case xlQry
            Case Is = 1
                Set qry = CurrentDb.QueryDefs("PnLQryRevenue_RV")
                xlRow = 8                               'starts in row 8: Revenue
            Case Is = 2
                Set qry = CurrentDb.QueryDefs("PnLQryMaterial_MT")
                xlRow = 11                              'starts in row 11: Material
            Case Is = 3
                Set qry = CurrentDb.QueryDefs("PnLQryDump_DP")
                xlRow = 12                              'starts in row 12: Dump Fee
            Case Is = 4
                Set qry = CurrentDb.QueryDefs("PnLQryLabor_LB")
                xlRow = 13                              'starts in row 13: Labor
            Case Is = 5
                Set qry = CurrentDb.QueryDefs("PnLQryCust_TR")
                xlRow = 14                              'starts in row 14: Travel: Job
            Case Is = 6
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_AD")
                xlRow = 18                              'starts in row 18: Advertising
            Case Is = 7
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_VE")
                xlRow = 19                              'starts in row 19: Vehicle Expenses
            Case Is = 8
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_DE")
                xlRow = 22                              'starts in row 22: Depreciation
            Case Is = 9
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_IN")
                xlRow = 25                              'starts in row 25: Insurance (Non-Biz)
            Case Is = 10
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_IH")
                xlRow = 26                              'starts in row 26: Insurance (Health)
            Case Is = 11
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_LI")
                xlRow = 27                            'starts in row 27: Interest
            Case Is = 12
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_OE")
                xlRow = 30                              'starts in row 30: Office Expense
            Case Is = 13
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_SU")
                xlRow = 34                             'starts in row 34: Supplies
            Case Is = 14
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_TX")
                xlRow = 35                             'starts in row 35: Taxes & Licenses
            Case Is = 15
                Set qry = CurrentDb.QueryDefs("PnLQryDeps_TR")
                xlRow = 36                              'starts in row 36: Travel:Deposits
            Case Is = 16
                Set qry = CurrentDb.QueryDefs("PnLQryExps_TR")
                xlRow = 37                             'starts in row 37: Travel:Expenses
            Case Is = 17
                Set qry = CurrentDb.QueryDefs("PnLQryMatl_TR")
                xlRow = 38                             'starts in row 38: Travel:Material
            Case Is = 18
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_ME")
                xlRow = 40                             'starts in row 40: Meals & Entertainment
            Case Is = 19
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_UT")
                xlRow = 41                             'starts in row 41: Utilities
            Case Is = 20
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_WA")
                xlRow = 42                              'starts in row 42: Wages
            Case Is = 21
                Set qry = CurrentDb.QueryDefs("PnLQryExpenses_OT")
                xlRow = 43                              'starts in row 43: Other
         End Select
            
    xlCol = (xlWS.Columns("B").End(xlDown).Row)         'reads data to column 'B'

    Set rst = qry.OpenRecordset
    
    'xlCol is the column number of the first empty cell in your spreadsheet that you want to use.
    'For B8 as the 'start' cell: Column 'B', xlRow+1=8
        C = 2                                           'make c = 2 to start in column B
        xlCol = xlCol + 1                               'starts JAN in column B
    Do Until rst.EOF
        For Each acRng In rst.Fields
            xlWS.Cells(xlRow, C).Formula = acRng        'loops across columns range:[B-M] = JAN-JUN
            C = C + 1
        Next acRng
        If xlCol < 7 Then
            rst.MoveNext
        End If
    Loop
    
    Loop Until xlQry = 22                               '21 is the query count limit

End If
    
    xlWS.Range("C5").FormulaR1C1 = PrevYear
    
 rst.Close
    Set rst = Nothing                                    'closes & destroys the rst object
    Set xlWS = Nothing                                   'closes & destroys the ws object
    xlWB.Close acSaveYes
    Set xlWB = Nothing                                   'closes & destroys the wb object
    xlApp.Quit
    Set xlApp = Nothing                                  'closes & destroys the xl object
 Me.CreateLIWCI_label.BackColor = vbGreen                'indicates sheet fill completed
End Sub
 

Attachments

  • PnL_Model-LI.xlsx
    23.2 KB · Views: 400
Last edited by a moderator:

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:06
Joined
Aug 30, 2003
Messages
36,118
Am I missing the question?
 

Punice

Registered User.
Local time
Today, 13:06
Joined
May 10, 2010
Messages
135
Yes, Paul. The submitted code exports all of the records for the full year to the spreadsheet. I tried to set the values of the constants (eg, <14 to <7) to display 6 columns, but all columns still display. I, also, tried rearranging the do-loops without
success. The code that I'm using was obtained from this forum, probably from you,
a few years ago. I'm just not familiar with the meanings of some of the supporting
statements that this sub-routine uses. I did enter them on Google to try to understand what they mean before I asked for help.

I'm using Access 2007
 

isladogs

MVP / VIP
Local time
Today, 17:06
Joined
Jan 14, 2017
Messages
18,186
Am I missing something here. Why do you need any of this code?

Why can't you just create a query with the data you require i.e. July 2018-June 2019 then use one of the following to export to Excel
1. DoCmd.TransferSpreadsheet
2. Right click the query and select Export to Excel
3. Export the query as a data task from the robbon
 

Punice

Registered User.
Local time
Today, 13:06
Joined
May 10, 2010
Messages
135
I could do that. It would be a bit unsophisticated, because I have 21 queries to deal with. In the past, I just used the code that I submitted to create two separate spread sheets: one for the previous year, one for the current year and deleted the columns that I didn't need for the auditor. That was ok, but I have a desire to learn how to do what I requested help to do. Just trying to learn more about this magnificent Access thing. Thanks for you suggestion, though.
 

isladogs

MVP / VIP
Local time
Today, 17:06
Joined
Jan 14, 2017
Messages
18,186
Yes I can see you have 21 queries though obviously I don't know how they differ or whether you could redesign the queries to make it simpler.

Even if that's not possible, you could create a union query combining the data from each of your 21 queries then use one of the 3 methods I suggested.
That would use the power of Access to make your life easier. What is unsophisticated about that?

I'm all for complex solutions where there is no better way and also have some procedures to export to Excel row by row, but I just can't see why its needed here
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:06
Joined
Sep 21, 2011
Messages
14,044
If you have to do it the complicated way and use what you have, then try

Amending the start and the loop of C
Amend the start column in the worksheet.
Amend xlCol depending on the set you are running for.

You will need some sort of flag to determine which set you want to use.?

HTH
 

Punice

Registered User.
Local time
Today, 13:06
Joined
May 10, 2010
Messages
135
Tried amending what you recommend, several different times and ways.
I'm thinking that my problem with all of the columns/rows being transferred has something to do with the statement containing 'EOF', but am too ignorant to know what to do instead.

Also, don't know about the # tag. I'll investigate that so I do the correct thing the next time around.

Good idea. I'll create a union query and use the procedure that you recommended.

Thanks for your help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:06
Joined
Sep 21, 2011
Messages
14,044
Your current requirement differs greatly (IMO) from what you initially required.

Quite often, that means going back to the drawing board, rather than trying to fudge what you had originally to work.
 

Punice

Registered User.
Local time
Today, 13:06
Joined
May 10, 2010
Messages
135
You are absolutely correct. I realized that the current code does what I needed done, then. I was hoping (lazy) that a tweak here and there would relieve me of having to exercise the gray matter some more. I guess at 84+ that it won't hurt to keep it spinning.

I'll do what you recommended and apply logic to what I want the code to do and write some code to do it, one step at a time.

Thanks, again. Out
 

Users who are viewing this thread

Top Bottom