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

 

[juneau730]

The query heading matters, because whether it is blank of not, the exported excel needs to be named exactly as it is in the dBase. It's what we are required..
Each of our queries do have a different heading. I have over a dozen different queries, all looking for different data points in 10s of 1000s raw data tables.
Each of our tables will always have data on them and the tables cannot be over written. We're required to keep all raw data (the tables) and all output files for a year.

I appreciate your help, but I am guessing there isn't a means to do what I seek.

 

[welshgasman]

The exported excel will file be named the same as one with data in. I have never suggested anything different? I am talking about changing the source query that would generate what you want. You said 7 queries?
If you only have 7 queries, then I would go with 7 MTYourFilenames and just copy the correct one as your output file. That would be the easiest. If you have 700, that would be a different matter.

Regardless, what you are asking for can be achieved, but you will need to work harder for it.?

I'm offline now until tomorrow.

 

[welshgasman]

What does this do?

Call FormatExcelSS(strOutputFile, "B1")

 

[JonXL]

I haven't run through a complete test of this, but does this get close to what you want?

Private Sub cmdExport_Click()
    Dim arrCurrentFormInfo
    Dim strCurrentForm
    Dim strCaption
    Dim strOutputFileName
    Dim strRecordSource
    Dim strOutputFile
    Dim objShell
    Dim lngFieldCount As Long
    Dim sqlNoRecords As String
  
    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
        lngFieldCount = CurrentDb.TableDefs(strRecordSource).Fields.Count
    Else
        lngFieldCount = CurrentDb.QueryDefs(strRecordSource).Fields.Count
    End If
  
    If dCount("*", strRecordSource) = 0 Then
        sqlNoRecords = NoRecordQuery(lngFieldCount, strRecordSource)
    Else
        sqlNoRecords = vbNullString
    End If
  
    CurrentDb.CreateQueryDef("DummyOutPut"), "SELECT * FROM [" & strRecordSource & "]" & sqlNoRecords
  
    DoCmd.OutputTo acOutputQuery, "DummyOutPut", acFormatXLSX, strOutputFile
  
    DoCmd.DeleteObject acQuery, "DummyOutPut"

    Call FormatExcelSS(strOutputFile, "B1")
    objShell.Run "Excel.exe" & " " & Chr(34) & strOutputFile & Chr(34), , True
End Sub

Private Function NoRecordQuery(NumberOfFields As Long, recSource As String) As String
    Dim l
    Dim sqlString As String
  
    sqlString = " UNION ALL SELECT TOP 1 'NO ACTIVITY FOR REVIEW FOUND'"
  
    For l = 2 To NumberOfFields
        sqlString = sqlString & ", NULL"
    Next l
  
    sqlString = sqlString & " FROM [" & recSource & "]"

    NoRecordQuery = sqlString

End Function