Export to excel with variable filename based on query result

[Leechow]

Hi, all. My access is 2013.

Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.

How can I export the list of graduates to multiple excel files, with the filename based on "year"?

I have tried to set the output file in macro as "c:\desktop\" & query.year & ".xls"

But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.

Thanks

 

[Kevin320]

Are you writing this in vba or creating a macro?

 

[MarkK]

Hello and welcome to the forum

You commonly use recordsets to work with data in tables. You use SQL query text as the command that opens the recordset. Consider the following code . . .

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT GradYear, GradName " & _
   "FROM tblGraduates")
with rst
   do while not .eof
      debug.print !GradYear, !GradName
      .movenext
   loop
   .close
end with

See how that uses a SQL statement, and then loops thru each record in the recordset, printing the results to the immediate pane.
Hope this helps,

 

[Leechow]

Are you writing this in vba or creating a macro?

I tried in macro, but didn't work.

 

[Leechow]

Re: Hello and welcome to the forum

But how to export the respective list with the respective filename?

You commonly use recordsets to work with data in tables. You use SQL query text as the command that opens the recordset. Consider the following code . . .

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT GradYear, GradName " & _
   "FROM tblGraduates")
with rst
   do while not .eof
      debug.print !GradYear, !GradName
      .movenext
   loop
   .close
end with

See how that uses a SQL statement, and then loops thru each record in the recordset, printing the results to the immediate pane.
Hope this helps,

 

[MarkK]

I am addressing this issue from your original post . . .

I have tried to set the output file in macro as "c:\desktop\" & query.year & ".xls"

. . . where you want to use data from the table in the filename. Where you use "query.year" you need to open a recordset. Alternatively you could use DLookup(), which is a function that retrieves data from a table.

 

[Leechow]

I have converted my macro to VBA and the codes are as follows. So, where should I place your code? Thanks.


Function Macro1()

On Error GoTo Macro1_Err
DoCmd.OutputTo acOutputTable, "tblGraduates", "Excel97-Excel2003Workbook(*.xls)", "C:\Desktop\" & tblGraduates.GradYear & ".xls", False, "", , acExportQualityPrint

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

I am addressing this issue from your original post . . .

. . . where you want to use data from the table in the filename. Where you use "query.year" you need to open a recordset. Alternatively you could use DLookup(), which is a function that retrieves data from a table.

 

[MarkK]

The code that I posted: do you understand what it does?

 

[Leechow]

I can't understand how it can be used for exporting variable filename.

Your code seems going thru each record in the table and printing the result immediately (though I dont the purpose of printing) . However, after printing, there is no further action to export, but just move to another record.

Say I have 100 records in the table containing graduates years and names, which are categorized into 3 graduate years 2008, 2009 and 2010. If go thru each record one by one, it will only print "2008"&"Peter", "2009"&"David", "2010"&"Susan", etc


But what I need is three files carrying names 2008, 2009 and 2010, containing the respective names. So, I dont know why I have to go thru each record to get the graduate year, but doing nothing before move to next record. And how the code can group the respective names into respective year?

The code that I posted: do you understand what it does?

 

[Gasman]

It can be done in VBA and at a quick glance I would have thought your code would work?

http://stackoverflow.com/questions/...vba-to-an-excel-spreadsheet-in-same-directory

 

[Leechow]

But the variable filename I want is not the timestamp, but the fields within the recrodset.

It can be done in VBA and at a quick glance I would have thought your code would work?

 

[Gasman]

But the variable filename I want is not the timestamp, but the fields within the recrodset.

A variable is a variable, hence it's name. You just substitute whatever variable you want.

I would be approaching this as running 3 queries, one for each year. The year would be the variable to the query and used in the filename on output.

I had to do something similar with Access 2003, which does not create pdf files as later versions do. So I named the report the same name as the ship, and when printing it it automatically has the ship name as the filename.

You could approach it that way as well. Copy the query to a query named by the variable, run the query, export it and delete the object.

HTH

Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click

    Dim stRptName As String, stParam As String, stLinkCriteria As String
    Dim iPreview As Integer, iDialog As Integer
    
    iPreview = 0
    iDialog = 0
    If Me.ChkPreview Then
        iPreview = 2
        iDialog = 3
    End If
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.value & "'"
    
    DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stParam, iPreview, , stLinkCriteria, iDialog

    DoCmd.Close acReport, stParam
    DoCmd.DeleteObject acReport, stParam

Exit_cmdShip_Click:
    Exit Sub

Err_cmdShip_Click:
    MsgBox Err.description
    Resume Exit_cmdShip_Click
    
End Sub

 

[Leechow]

Do u mean I have to run the query 3 times? But what if I am not certain about the years, so that I cannot set the query criteria and the number of query I should run?

A variable is a variable, hence it's name. You just substitute whatever variable you want.

I would be approaching this as running 3 queries, one for each year. The year would be the variable to the query and used in the filename on output.

I had to do something similar with Access 2003, which does not create pdf files as later versions do. So I named the report the same name as the ship, and when printing it it automatically has the ship name as the filename.

You could approach it that way as well. Copy the query to a query named by the variable, run the query, export it and delete the object.

HTH

Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click

    Dim stRptName As String, stParam As String, stLinkCriteria As String
    Dim iPreview As Integer, iDialog As Integer
    
    iPreview = 0
    iDialog = 0
    If Me.ChkPreview Then
        iPreview = 2
        iDialog = 3
    End If
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.value & "'"
    
    DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stParam, iPreview, , stLinkCriteria, iDialog

    DoCmd.Close acReport, stParam
    DoCmd.DeleteObject acReport, stParam

Exit_cmdShip_Click:
    Exit Sub

Err_cmdShip_Click:
    MsgBox Err.description
    Resume Exit_cmdShip_Click
    
End Sub

 

[Gasman]

Well if you don't know the years each time and the number of years, then yes I'd be running it for as many times as required passing the year to the vba to run the query. You could use a dummy date to end the vba program, else ask for another year each time.

If you knew the number of years and the actual years, you could hard code it, but still running it a many times as needed.

 

[MarkK]

You can write a query that groups by a value, so that value only appears once in the query, and other values are summarized. By way of example, I have just written a database to keep track of electrical circuits in my house, so I have a table called tCircuit. Using this SQL I can returns all records from that table . .

SELECT CircuitID, PanelID, [Number], X, Y, Amps, Name, Description
FROM tCircuit;

Now, if I want to know how many circuits of each amperage I have, I can write a GROUP BY query like this . . .

SELECT Amps, Count(tCircuit.CircuitID) AS CircuitCount
FROM tCircuit
GROUP BY Amps;

. . . returns data as follows . . .

Amps	CircuitCount
0	2
15	24
30	4
40	2

where each distinct amperage has it's own row, and the second field is a count of how many rows there are in the table matching that amperage.

This means that you can write a query--and therefore open a recordset--that has one row for each year in your data, and then summarize other data that falls into that year in other fields.