Exporting a query that I have created into Excel.

[Cark]

I have a query set up which extracts the top 10 most frequently occurring ATAs from my main table and I will refer to this as the First Query.

I then use this query with the Second Query in order to pull out the rest of the fields associated with the top 10 most frequently occurring ATAs.

Just a little note regarding the Criteria that I have applied in the queries. The date criteria is given by filling in boxes on my reporting form and the ModelLink = 2 refers to filtering just the Boeing 737-300 aircraft that I have (I will be replicating this query and export another 2 times for the 737-800 and 757-200).

When I fill in the 2 date parameters (the only thing required to set the query) and look at the Second Query, the query filters the information perfectly. However, when I come to export the data using my code, a load of random columns which I have not included in my query are exported and some columns which I have asked to be renamed are not renamed.

Any ideas as to why this is happening? I managed to get it working one time for the 737-300 and 757-200 exports (I initially got the 737-300 set of queries working and then copied them over for the other 2), but then for some reason the 737-800 report started having the issue that I am replicating now. I tried deleting all the queries and trying to rebuild them, but I still keep getting this issue.

My end goal is to have my query exported into Excel and the workbook to open for the user to be able to edit the Excel spreadsheet as they choose. I also don't want the spreadsheet to be saved automatically with a filepath etc, I would rather the user chooses just via Excel whether they want to save it or not.

Private Sub Top10ATA738Expt_Click()

    Dim dbsCurrent      As Database
    Dim SQL_Name        As QueryDef
    Dim SQL_Output      As String
    Dim dStart          As String
    Dim dEnd            As String


    Set dbsCurrent = CurrentDb
    Set SQL_Name = dbsCurrent.QueryDefs("Top10ATA737800")
    
    DoCmd.Echo False
    DoCmd.OpenQuery "Top10ATA737800"
    Call FormatExcelTop10ATAExport("Top10ATA737800.xlsx")
    DoCmd.Close acQuery, "Top10ATA737800"
    DoCmd.Echo True
    
End Sub

 

[pbaldy]

Two thoughts. First, nothing you've shown creates an Excel file. Presumably it's done in the function, but not being able to see that code means nobody will likely be able to determine why the export is wonky. Second, I'd probably have the aircraft as a parameter, rather than having sets of queries for each.

 

[Cark]

Sorry for not knowing which bit exactly is spitting out the data into Excel, I am working off code someone else has written. I am quite the amateur at access and am just piecing together bits that I find would embellish the database I currently have in a vain effort to develop it.

Where would I find where this function was set up to do the export to Excel?

 

[Cark]

    Dim dbsCurrent      As Database
    Dim SQL_Name        As QueryDef
    Dim SQL_Output      As String
    Dim dStart          As String
    Dim dEnd            As String


    Set dbsCurrent = CurrentDb
    Set SQL_Name = dbsCurrent.QueryDefs("Top10ATA737800")
    
    DoCmd.Echo False
    DoCmd.OpenQuery "Top10ATA737800"
    DoCmd.RunCommand acCmdOutputToExcel
    Call FormatExcelTop10ATAExport("Top10ATA737800.xlsx")
    DoCmd.Close acQuery, "Top10ATA737800"
    DoCmd.Echo True

I went digging around in my backups (I keep backups of every single minor tweak I make as I'm quite paranoid about breaking stuff) and came across this snippet which includes the acCmdOutputToExcel. This command seems to cause the code to export 2 Excel files (one standard export and one which is formatted how I want according to the "Call FormatExcelTop10ATAExport" bit which also comes out as a read only version). How do I make it so that only the formatted copy is exported and is also not a read only?

This code also seems to get rid of the issue that I was having with the 737-800 export giving me the totally incorrect columns. Effectively all I need now is to be able to get rid of the duplicate read only copies of the exports.

 

[Cark]

What is even weirder, is that when I changed the code again to my latest database iteration (the one which I am having the random columns export issue), the correct columns are now exported except the ATA2Digit column. I really don't understand what Access is doing during this stage....

    Dim dbsCurrent      As Database
    Dim SQL_Name        As QueryDef
    Dim SQL_Output      As String
    Dim dStart          As String
    Dim dEnd            As String


    Set dbsCurrent = CurrentDb
    Set SQL_Name = dbsCurrent.QueryDefs("Top10ATA737300")
    
    DoCmd.Echo False
    DoCmd.OpenQuery "Top10ATA737300"
    Call FormatExcelTop10ATAExport("Top10ATA737300.xlsx")
    DoCmd.Close acQuery, "Top10ATA737300"
    DoCmd.Echo True
    
    Set dbsCurrent = Nothing
    Set SQL_Name = Nothing

 

[Minty]

From my dim memories of a previous thread, ATA2Digit was a calculated field, maybe that is now missing again from your query.

Your code in the latest post is also not creating an excel export, merely formatting an existing xls by the look of it.

As Paul suggested you are making this very complicated by having different queries for each aircraft type. Simply add the aircraft type as another criteria from your reporting form.

 

[pbaldy]

Where would I find where this function was set up to do the export to Excel?

Put the cursor in the function name and hit Shift-F2.

 

[Cark]

This issue is driving me insane at the moment as it seems I am so close to the end, but I just can't get it to work.

I don't understand how an Excel is being produced then, because I am getting all 3 Excel files (one for each aircraft type).

The FormatExcelTop10ATAExport function is as follows:

Sub FormatExcelTop10ATAExport(FileName As String)
'Format excel file
'20150531
'http://www.ozgrid.com/forum/showthread.php?t=17608
'http://www.accessibledatasolutions.com/articles11/AccessToExcel.htm

    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    Set wb = objapp.workbooks.Open(FileName, True, False)
    'select all worksheets & cells In turn
    For Each WS In wb.worksheets
    With WS
            .Cells.Font.Name = "Arial"
            lastrow = .Range("A1").currentregion.Rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count
        .Columns("C").Font.Bold = True
        .Columns("C").Font.Italic = True
        .Columns("F").Font.Italic = True
        .Rows(1).Font.Bold = True
        .Rows(1).Font.Italic = False

    End With
    
    Next 'next worksheet
 objapp.sheets(1).Activate
    Set objapp = Nothing
    
End Sub

Some data from my database is getting exported when I set the date parameters, but when I add a new record into the database which falls inside of the date range, when I try to produce another export, the new record doesn't come out even though it appears in the query.

Why does the DoCmd.OpenQuery not update this?

This is the only issue I am having with the exports now.

 

[pbaldy]

A query just displays data, it isn't exporting to Excel. Frankly I doubt the open/close lines are necessary. That said, it's still a mystery how the Excel files are getting created. This line might, though it's not a method I've ever used:

DoCmd.RunCommand acCmdOutputToExcel

Are you sure you're not seeing the same Excel file? Try deleting it/them and see if you get new files created. Can you attach the db here?

 

[Cark]

I have tried all sorts of troubleshooting methods to try and isolate what it is that is causing it to not update and have made sure it's not a case of Access trying to overwrite the Excel file that is exported. In my testing, I removed the DoCmd.RunCommand acCmdOutputToExcel line and replaced it with Call FormatExcelTop10ATAExport("Top10ATA737300.xlsx") and because the FormatExcelTop10ATAExport has Set objapp = CreateObject("Excel.Application") inside of it, I think that this is also able to open an Excel sheet and may provide an explanation to why 2 Excel files were opened when I had both lines in a piece of code (I think there was a specific order I had them in for it to export an original + a read only but I don't fully remember).

Anyway I have gotten around this issue by reverting to the Transferspreadsheet function which seem to be the most common method of exporting. My only gripe with this method is that it saves a file to a specified location. I could do without the bloating of saving exports in locations as I just want Excel files to be presented to the user for them to be able to do what they want with them rather than being forced into saving to places etc. I'll survive for now until I find a way.