cmdExport_Click

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
80
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"

VBA Code:
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JonXL

Active Member
Joined
Feb 5, 2018
Messages
326
Office Version
365, 2016
Platform
Windows
Do you mean if there are no records?
 

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
80
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

Well-known Member
Joined
May 25, 2013
Messages
777
Test for any records, and if none, then output another dummy query that has your text as a record?
 

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
80
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:

Code:
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

Well-known Member
Joined
May 25, 2013
Messages
777
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

Board Regular
Joined
Jun 7, 2018
Messages
80
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

Well-known Member
Joined
May 25, 2013
Messages
777
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

Board Regular
Joined
Jun 7, 2018
Messages
80
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

Well-known Member
Joined
May 25, 2013
Messages
777
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" ? :unsure: 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,732
Messages
5,446,184
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top