cmdExport_Click

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
111
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
 
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.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
What does this do?
Code:
Call FormatExcelSS(strOutputFile, "B1")
 
Upvote 0
I haven't run through a complete test of this, but does this get close to what you want?

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

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top