cmdExport_Click

[juneau730]

I have the following code for exporting data from the dBase to excel.. It works and works great. I am wondering though if there is a way to add a line into the code that when exported, IF A2 is NULL insert "NO ACTIVITY FOR REVIEW FOUND"

Private Sub cmdExport_Click()
    Dim arrCurrentFormInfo
    Dim strCurrentForm
    Dim strCaption
    Dim strOutputFileName
    Dim strRecordSource
    Dim strOutputFile
    Dim objShell
    
    Set objShell = VBA.CreateObject("wscript.shell")
    
    arrCurrentFormInfo = Split(txtCurrentForm.Value, ":")
    If UBound(arrCurrentFormInfo) = 1 Then
        strCurrentForm = Replace(arrCurrentFormInfo(0), "form.", "")
        strOutputFileName = arrCurrentFormInfo(1)
    Else
        strCurrentForm = Replace(txtCurrentForm.Value, "form.", "")
        strOutputFileName = strCurrentForm
    End If
    
    strRecordSource = Me!NavigationSubform.Form.RecordSource

    strOutputFile = Application.CurrentProject.Path & "\Exported Data\" & strOutputFileName & ".xlsx"
    
    If Left(strRecordSource, 3) = "tbl" Then
        DoCmd.OutputTo acOutputTable, strRecordSource, acFormatXLSX, strOutputFile
    Else
        DoCmd.OutputTo acOutputQuery, strRecordSource, acFormatXLSX, strOutputFile
    End If
    
    Call FormatExcelSS(strOutputFile, "B1")
    objShell.Run "Excel.exe" & " " & Chr(34) & strOutputFile & Chr(34), , True
End Sub

 

[JonXL]

Do you mean if there are no records?

 

[juneau730]

Yes.
If the query returns no records to the form view, when I export that form view, with no records, to Excel, I would like that "NO ACTIVITY FOR REVIEW FOUND" statement inserted.

 

[welshgasman]

Test for any records, and if none, then output another dummy query that has your text as a record?

 

[juneau730]

Hello all, following up on this and trying to explain a bit better what I am looking for.

The dBase has multiple queries that pull table data and look for specific criteria. There are always instances when there is no matching criteria and I would like the query to "output" NO ACTIVITY FOR REVIEW FOUND, if there is no match..

One of the queries is:

SELECT [tbl AD User Accounts].[Full Name], [tbl AD User Accounts].[Display Name], [tbl AD User Accounts].[Last Name], [tbl AD User Accounts].[First Name], [tbl AD User Accounts].MI, [tbl AD User Accounts].[Logon Name], [tbl AD User Accounts].Email, [tbl AD User Accounts].Created, [tbl AD User Accounts].[Last Modified], [tbl AD User Accounts].[Last Logon], [tbl AD User Accounts].[Last Logon TS], [tbl AD User Accounts].[Pwd Last Set], [tbl AD User Accounts].[Pwd Nvr Exp], [tbl AD User Accounts].[Pwd Expired], [tbl AD User Accounts].[Pwd Required], [tbl AD User Accounts].Disabled, [tbl AD User Accounts].[Acct Expires], [tbl AD User Accounts].Description, [tbl AD User Accounts].Title, [tbl AD User Accounts].Department, [tbl AD User Accounts].Manager, [tbl AD User Accounts].[SIP Address], [tbl AD User Accounts].Alias, [tbl AD User Accounts].[PIV Enforced], [tbl AD User Accounts].[PIV Exemption], [tbl AD User Accounts].[PIV Exempt WO#], [tbl AD User Accounts].[Distinguished Name], Int(Date()-[Pwd Last Set]) AS [Day Difference]
FROM [tbl AD User Accounts]
WHERE ((([tbl AD User Accounts].Disabled)="No") AND (([tbl AD User Accounts].[PIV Enforced])=False) AND (([tbl AD User Accounts].[Distinguished Name]) Like "*Users*") AND ((Int(Date()-[Pwd Last Set]))>=60));

In this case the criteria in question is a date difference calculation between current date and Pws Last Set and it's has to be greater than/equal to 60 days

If this isn't met, I would like it to say "NO ACTIVITY FOR REVIEW FOUND"

 

[welshgasman]

In the form you already no there are no records, so change the query appropriately?
In fact I'd probably just have a template Excel workbook that holds that info and just copy it as the output file.?

 

[juneau730]

Changing the query and copy/paste the statement is not an option.
There are multiple queries that we'd like to apply this too (if possible) and management doesn't want users to copy/paste anything, they want it automated.
Google show various SQL strings that supposedly can do something similar to what we are looking for, but it's greek to me

 

[welshgasman]

I'm not talking about users copying files. I am talking about you doing it in code.

If no records exist you generate a xlsx file from another source.? That could be a dummy query or another file.?

 

[juneau730]

I guess I am not following you. Sorry...

I have multiple queries tired to a specific table, in this example the table is AD Users, which has 7 different queries tied to it. The table will always have data, but depending on the query, it could be empty.

If I had a dummy query, wouldn't I need one tired to each of the 7? And wouldn't the exported name be different then what the query/form name is? If that were the case, it would defeat the purpose of the queries as they are because they are used for auditing specific data points and the exported data needs to be names as they are.

 

[welshgasman]

Ok, I can understand that each output of a query could have different headings if there was valid data, BUT what does that matter if the worksheet is just going to say "NO ACTIVITY FOR REVIEW FOUND" ? The data is going to be known by the name of the file?

I would expect the tables to mostly have records?, but remember I have no clue as to what this is for or your setup.?
You would just overwrite the strRecordSource before output of the query if no records found?

Also it would be advisable to declare your variables correctly and not leave them all as variants? Not going to help with this problem, but considered good practice?