Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-01-2019, 11:20 AM   #1
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 108
Thanks: 47
Thanked 1 Time in 1 Post
Punice is on a distinguished road
VBA correction to export multi-query data to excel

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
Attached Files
File Type: xlsx PnL_Model-LI.xlsx (23.2 KB, 2 views)


Last edited by isladogs; 12-02-2019 at 11:28 AM. Reason: Added code tags to improve readability
Punice is offline   Reply With Quote
Old 12-01-2019, 11:29 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,371
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA correction to export multi-query data to excel

Am I missing the question?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-01-2019, 11:42 AM   #3
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 108
Thanks: 47
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: VBA correction to export multi-query data to excel

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

Punice is offline   Reply With Quote
Old 12-01-2019, 11:50 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,252
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: VBA correction to export multi-query data to excel

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 12-01-2019, 12:03 PM   #5
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 108
Thanks: 47
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: VBA correction to export multi-query data to excel

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.
Punice is offline   Reply With Quote
Old 12-01-2019, 12:30 PM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,252
Thanks: 115
Thanked 3,075 Times in 2,793 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: VBA correction to export multi-query data to excel

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Punice (12-02-2019)
Old 12-01-2019, 12:52 PM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,515
Thanks: 439
Thanked 840 Times in 811 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: VBA correction to export multi-query data to excel

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-02-2019, 10:02 AM   #8
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 108
Thanks: 47
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: VBA correction to export multi-query data to excel

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.
Punice is offline   Reply With Quote
Old 12-02-2019, 10:45 AM   #9
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,515
Thanks: 439
Thanked 840 Times in 811 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: VBA correction to export multi-query data to excel

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 07:09 AM   #10
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 108
Thanks: 47
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Smile Re: VBA correction to export multi-query data to excel

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

Punice is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Query to Excel then Delete Query data from Table cochise Queries 5 11-20-2013 08:45 AM
Export Query Data to Excel Range Danny_H Modules & VBA 9 02-14-2013 06:09 AM
Multi forms export to excel Rakesh935 Forms 4 10-16-2012 06:03 AM
Export Access Query Data To Excel Cell IanT Modules & VBA 0 08-30-2011 03:29 AM
Export Access data to excel based on excel data lookup shalva.gelashvili Modules & VBA 1 09-08-2008 11:04 AM




All times are GMT -8. The time now is 05:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World