Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2011
    Posts
    18

    Unanswered: Help with exporting multiple reports with a date

    Hey guys, I have a database with multiple reports I must generate. I figured out how to add a date to the end of each report with the following sample code:

    Public Function ExportFileNameFunction()
    Dim Current_Date As String
    Dim File_Name As String
    Current_Date = Format$(Now(), "mm\-dd\-yyyy")
    File_Name = "c:/Test/Reports/Capital-" + Current_Date + ".xls"
    File_Name2 = "c:/Test/Reports/Capital2-" + Current_Date + ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital Report", [File_Name]
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital2 Report", [File_Name2]
    End Function


    This works well and I have a macro that will output all reports. What I would like to do is use an IF statement to evaluate each report to see if it has data in it (New data is posted into a table each day that these reports draw from). If the report has data I would like it to be exported, if not obviously I would like it to not export anything. Can anyone give me a hand on how to accomplish this? It would also be great if I didn't have to create a File_Name2, File_Name3, etc.. and instead could have the vba just look at all my reports at once, evaluate if they have data or not, and then export them. Thanks in advance!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have a look at the report OnNoData event in the Help file. This might be what you need.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by weejas View Post
    Have a look at the report OnNoData event in the Help file. This might be what you need.
    Hi, thanks for the response! Unfortunately I misspoke, these are not actually reports but queries that are getting exported. It looks like the onnodata property only works with reports. Any other suggestions?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Two options present themselves.

    You can open a recordset based on each query in turn, and only export those with a record count greater than 0. Or you can execute the DCount function against each query and again, only export those that return more than 0.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2011
    Posts
    18
    Do you think you could give me some sample code as to how the recordset object would work? I have not used that before. Thanks again for the help!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This is a useful link for all things recordset. You will also needs to set a reference to the latest version of MS ActiveX Data Objects.

    Given that you've only got two queries, you probably don't need an ornate loop structure for this.

    Code:
    Dim rstTest as ADODB.Recordset
    
    Set rstTest = New ADODB.Recordset
    
    rstTest.Open "Capital Report", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    If rstTest.Recordcount > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital Report", [File_Name]
    End If
    
    rstTest.Close
    
    rstTest.Open "Capital2 Report", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    If rstTest.Recordcount > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital2 Report", [File_Name]
    End If
    
    rstTest.Close
    
    Set rstTest = Nothing
    That should get you started.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Feb 2011
    Posts
    18
    Quote Originally Posted by weejas View Post
    This is a useful link for all things recordset. You will also needs to set a reference to the latest version of MS ActiveX Data Objects.

    Given that you've only got two queries, you probably don't need an ornate loop structure for this.

    Code:
    Dim rstTest as ADODB.Recordset
    
    Set rstTest = New ADODB.Recordset
    
    rstTest.Open "Capital Report", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    If rstTest.Recordcount > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital Report", [File_Name]
    End If
    
    rstTest.Close
    
    rstTest.Open "Capital2 Report", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    If rstTest.Recordcount > 0 Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Capital2 Report", [File_Name]
    End If
    
    rstTest.Close
    
    Set rstTest = Nothing
    That should get you started.
    Sorry it took so long to respond, I did get this working, thanks! My final question is, is there a way I could create vba code that would export all of the queries that I have without me having to specify each one. I'm not sure if that would be possible but thought I would ask, thanks again guys!

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hmm.

    I normally cheat for this sort of thing, and create a table to hold the names of the queries that I want to export, and then loop through the contents of the table.

    If your export queries all have a similar name, you can always try something with the database's QueryDefs - check the help file or Google for more info.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •