Exporting to Excel Based on Different Criteria

[certifydgangsta]

I am exporting from access to excel using the code below, but I'd like to edit the code so that it exports to excel for each original value in column A. For example, if column A contains the values "Type A", "Type B" and "Type C" then I'd like to export/save three different excel files (one for "Type A", one for "Type B" and one for "Type C"). If "Type A" appears in column A seven times then I'd want to export all seven rows for columns A through E.

Private Sub ExportToExcel_Click()
'Declaration of variables for file path
Dim CurrentFolder As String
Dim FileName As String
Dim CurrentCycle As String
'Initializing
CurrentCycle = Format(Date, "yyyymm")
FileName = SVCnumber1 & "Output.xls"
CurrentFolder = "C:\Users\Certifydgangsta\Desktop\" & _
                CurrentCycle & "\" & _
                SVCnumber1 & "\"
 
'Creates directory if it does not already exist
If Len(Dir(CurrentFolder, vbDirectory)) = 0 Then
   MkDir CurrentFolder
End If
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qBasic", CurrentFolder & SVCnumber1 & " Output", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qComplex", CurrentFolder & SVCnumber1 & " Output", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qWinner", CurrentFolder & SVCnumber1 & " Output", True
 
i = MsgBox("Export Complete", vbOKOnly)
End Sub

Any help is greatly appreciated.

 

[TJPoorman]

Sounds to me like you are wanting something like a GROUP BY query.

Check this link out:
http://www.w3schools.com/sql/sql_groupby.asp

 

[certifydgangsta]

My issue is that the values in column A in excel (or in the 'Type' field in access) will change every month so I can't create a query that says group all "Type A" together then create report and export, because the criteria would need to be changed each month.

 

[namliam]

Something like:

Dim rs as dao.recordset
set rs = currentdb.openrecordset("Select distinct ColumnA from yourTable")
Do while not rs.eof
    currentdb.querydefs("qBasic").sql = "Select ..... from ... where ... ColumnA = '" & rs!ColumnA & "'"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qBasic", CurrentFolder & SVCnumber1 & rs!ColumnA & " Output", True
    rs.movenext
loop

This offcourse is just to give you a quick idea, hope you can addapt it to suite your own purpose.

 

[certifydgangsta]

I'm attempting to make your code work on a very basic level and then plan on expanding on it once I have it working. Right now it seems that I'm having an issue with the CurrentDb.QueryDefs line. Thank you in advance for any help.

[SIZE=3][FONT=Calibri]Dim rs as dao.recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]set rs = currentdb.openrecordset("SELECT DISTINCT [Jersey Number] FROM [Jersey Number Reference]")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Do while not rs.eof[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CurrentDb.QueryDefs("qBasic").sql = "SELECT [Jersey Number] FROM [Jersey Number Reference] WHERE [Jersey Number] = '" & rs![Jersey Number] & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qBasic", CurrentFolder & rs![Jersey Number] & " Output", True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]rs.movenext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]loop[/FONT][/SIZE]

 

[namliam]

Please indent your code and keep your queries readable...

What is your problem with the querydefs line? What is the error that is generated?
Is Jersey Number, as it sounds a number field? or is it indeed text?

Dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT DISTINCT [Jersey Number] FROM [Jersey Number Reference]")
Do while not rs.eof
    CurrentDb.QueryDefs("qBasic").sql = " SELECT [Jersey Number] " & _
                                        " FROM [Jersey Number Reference] " & _ 
                                        " WHERE [Jersey Number] = '" & rs![Jersey Number] & "'"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qBasic", CurrentFolder & rs![Jersey Number] & " Output", True
    rs.movenext
loop

Dont see CurrentFolder defined anywhere assume it is a function of some sort....

If Jersey number is indeed a number field try addapting the sql to

... ... ... ... WHERE [Jersey Number] = " & rs![Jersey Number] & ""

 

[certifydgangsta]

The jersey number is alpha numeric so that part should be ok.

CurrentFolder is declared as a string with a drive path where the files should be exported.

No error pops up when I run the code, but I added an msgbox at the beginning and end of the code and when that line of code is included in then it displays the first msgbox, but won't display the second msgbox.

Sorry for the poor formatting. Thanks again for the help.

 

[namliam]

what if you open the query qBasic, does it contain the proper SQL?
If you run the query manually does it throw an error?

 

[certifydgangsta]

The query runs fine manually...I should mention that the SQL code for the query qBasic does not have the same SQL code as what is written in the QueryDefs portion of the vba code.

 

[namliam]

Well the line is meant to write the SQL to the specific query object called qBasic...
Can you perhaps post a wattered down version of your db with only this code, required querie(s), table(s), some dummy data... Then I can have a look wtf is goings on.

 

[certifydgangsta]

Attached is a dummy database with just the bare bones vba code that is causing the problem. Thanks for the help.

 

[certifydgangsta]

I should mention that I inherited the original DB from someone else and it was not stopping to debug when the code is entered incorrectly, but this new DB will kickout to the VBA code and show me what is causing the error. The original DB didn't have 'Use Special Access Keys' checked, so now I can at least see the exact line that isn't working which is the same one that we had discussed above (QueryDefs).

 

[namliam]

If Jersey number is indeed a number field try addapting the sql to

... ... ... ... WHERE [Jersey Number] = " & rs![Jersey Number] & ""

Do what I told you 3 days ago and it shall work :banghead:

it is offcourse completly broken, but for your simpel test... it exports the files.

 

[certifydgangsta]

Using this dummy DB that I uploaded I receive the error "Run-time error '31532' Microsoft Access was unable to export the data," when clicking the Export cmd button.

 

[namliam]

Using this dummy DB that I uploaded I receive the error "Run-time error '31532' Microsoft Access was unable to export the data," when clicking the Export cmd button.

Yes you get that error with your uploaded database....

 

[certifydgangsta]

Ahhh it works, just so I don't seem like a complete idiot (only a partial idiot), in the original DB it was alpha numeric so I copied the code over and applied it to a number field, but with special access keys checked now I should be able to do a little bit more debugging for myself. Thanks sooo much, hopefully you didn't bang your head on the wall too many times.

 

[namliam]

Thanks sooo much, hopefully you didn't bang your head on the wall too many times.

Not many, just once, now we have a pass thru window so people can give me my coffee easier

I forgot I installed some steel plates just last year

Good luck on working this out to a working solution, let me know if you run into trouble.