I use VBA to generate output files in Excel from Access reports. It works just fine except the format of the Excel output file. Below script as part of the total code block is running okay to generate Excel output files and place them in a desired location. Code looks into the file and picks up the customer name and number then cerates a string with the xls extension and puts it in the desired location which is selected by the user via the browse button.
DoCmd.OutputTo acOutputReport, "Excel Cust Price List NEWDESC1", acFormatXLS, sFile, False
The issue is that after the file is generated and placed in the right place when it is opened there is a blank row after the title row and data is grouped. I just can't get the output file to be displayed without the blank row and grouping and can't figure out why it shows that way.
Below is the complete code that runs the above described process.
Public Sub Make_Excel_Files1(Optional ByVal sOutputPathNEWDESC As String = vbNullString)
Dim db As DAO.Database
Dim rsCustomers As DAO.Recordset
Dim qdfReportData As DAO.QueryDef
Dim rsReportData As DAO.Recordset
Dim sOldSQL As String 'variable to hold original sql statement
Dim sNewSQL As String 'variable to hold new sql statement during construction
Dim sFile As String
Dim sCustNum As String
Dim sCustName As String
Dim hr As Long
Dim cWherePos As Long
Set db = CurrentDb
Set rsCustomers = db.OpenRecordset("qryCustomerList") 'open unique list of customers
Set qdfReportData = db.QueryDefs("qryReportData_NEWDESC") ' get original query
sOldSQL = Trim(qdfReportData.SQL)
sOutputPathNEWDESC = Trim(sOutputPathNEWDESC)
' Default to the The Default Database Folder indicated in Tools, Options
' when no path was specified
If Len(sOutputPathNEWDESC) = 0 Then
sOutputPathNEWDESC = Application.GetOption("Default Database Directory") & "\"
If Right(sOutputPathNEWDESC, 1) <> "\" Then sOutputPathNEWDESC = sOutputPathNEWDESC & "\"
Do While Not rsCustomers.EOF 'loop through all customers and adjust query
sCustNum = Nz(rsCustomers.Fields("cust").Value, "")
sCustName = Nz(rsCustomers.Fields("custname").Value, "")
sNewSQL = "SELECT * FROM qryReportData_Source_NEWDESC " _
& "WHERE ([customer price list report source_tbl1].customer='" _
& sCustNum & "')"
qdfReportData.SQL = sNewSQL
'sFile = sOutputPath & "\" & rsCustomers.Fields("cust") & ".xls"
sFile = sOutputPathNEWDESC & _
sCustNum & "_" & _
sCustName & ".xls"
'DoCmd.OutputTo acOutputReport, "Excel Cust Price List NEWDESC", acFormatXLS, sFile, False 'new report format
DoCmd.OutputTo acOutputReport, "Excel Cust Price List NEWDESC1", acFormatXLS, sFile, False 'old report format
qdfReportData.SQL = sOldSQL ' restore original sql statement
'clean up objects
Set qdfReportData = Nothing
Set rsCustomers = Nothing
Set db = Nothing
MsgBox "Excel Files are Complete"
Any thoughts, help?