proplem exporting a query to excel with formatting

**sdel_nevo** · 02-28-2018, 04:07 AM

Hi Guys

I have a issue that I cant seem to get my head around, I have two forms Form (frm1) and (frm2) that both have a button on that when clicked exports a query to excel and formats the spread sheet automatically.

frm1 works great and also has the most complicated of the two queries exporting the data to excel

frm2 does not work when the button is press, the code always tells me there is no data to export

The code below runs on the "ExportToExcel" button click event of each form

The code on frm1 that works looks like this

Code:

 
Dim objExcelApp As Object
    Dim objExcelBook As Object
    Dim objExcelSheet As Object
    Dim strSql As String
    Dim objRecordSet As DAO.Recordset
    Dim iRowStart As Integer
    Const XLCENTER = -4108
    Const XLLEFT = -4131 'xlRight is -4152
    Const XLCELLVALUE = 1
    Const XLGREATER = 5
    '    Const xlExpression = 2
    
    
    Dim objO As Object
  
    On Error Resume Next
    Set objExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        ' No Outlook is not open, try and create object
        Err.Clear
        Set objO = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
      
            MsgBox "Excel Is Either Not Installed Or Is Unavalible You Can Not Export To Excel"
            Err.Clear
            
            Exit Sub
        End If
    End If
   
    'Now we set up the SQL used for the Recordset (RS1)
    strSql = "SELECT tblCustomers.fullname As [Customer Name], " & _
        "Sum(Abs([DateofOrder] Between Date()-30 And Date())*[OrderTotalValue]) AS [Due Between Now And 30 Days], " & _
        "Sum(Abs([DateofOrder] Between Date()-60 And Date()-31)*[OrderTotalValue]) AS [Due Between 30 And 60 Days], " & _
        "Sum(Abs([DateofOrder] Between Date()-90 And Date()-61)*[OrderTotalValue]) AS [Due Between 60 And 90 Days], " & _
        "Sum(Abs([DateofOrder] Between Date()-120 And Date()-91)*[OrderTotalValue]) AS [Due Between 90 And 120 Days], " & _
        "Sum(Abs([DateofOrder]<Date()-121)*[OrderTotalValue]) AS [All OverDue Greater Than 120 Day], " & _
        "[Due Between Now And 30 Days]+[Due Between 30 And 60 Days]+[Due Between 60 And 90 Days]+[Due Between 90 And 120 Days]+[All OverDue Greater Than 120 Day] AS [Total Outstanding]" & _
        "FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID " & _
        "GROUP BY tblCustomers.fullname, tblOrders.PaidInFull, tblOrders.HasBeenInvoiced, tblOrders.InvoiceVoided " & _
        "HAVING (((tblOrders.PaidInFull)=False) AND ((tblOrders.HasBeenInvoiced)=True) AND ((tblOrders.InvoiceVoided)=False))"
    'Execute query and populate recordset
    Set objRecordSet = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
   
    'If no data, don't bother opening Excel, just quit
    If objRecordSet.RecordCount = 0 Then
        MsgBox "No Data Selected For Export", vbInformation + vbOKOnly, "No data exported"
        DoCmd.Close acForm, "frmCompanyAgedDebtors", acSavePrompt
        Exit Sub
    Else
        'We Shall Turn On The Hour Glass, So That Users Know That Something Is Happening
        DoCmd.Hourglass (True)
    
        'Create an instance of Excel and start building a spreadsheet Late Binding Used So No Refrences Required
        Set objExcelApp = CreateObject("Excel.Application")
   
        objExcelApp.Visible = False
        Set objExcelBook = objExcelApp.Workbooks.Add() 'start a new workbook
        Set objExcelSheet = objExcelBook.Worksheets(1)
 
        With objExcelSheet
            .Name = "Aged Debtors Report"
            .Cells.font.Name = "Franklin Gothic Book"
            .Cells.font.Size = 10
        
            'Format using these examples .Columns("A").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("B").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("E").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("F").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("G").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("H").NumberFormat = "$#,##0.00;-$#,##0.00"
       
            'Format range as required To Bold a Cell use this for example .Range("A1").Cells.Font.Bold = True
            .Range("A1", "H1").Merge
            .Range("A1").Columns.AutoFit
            .Range("A2", "H2").Merge
            .Range("A2").Columns.AutoFit
            .Range("A1").HorizontalAlignment = XLLEFT
            .Range("A2").HorizontalAlignment = XLLEFT
            .Range("A1").Cells.font.Name = "Franklin Gothic Book"
            .Range("A2").Cells.font.Name = "Franklin Gothic Book"
            .Range("A1").Cells.font.Size = 12
            .Range("A2").Cells.font.Size = 10
            .Range("A1").Value = DLookup("[CompanyName]", "[tblCompanyDetails]", "[CompanyID]=1") & " " & "Aged Debtors Report" '= "Company Aged Creditors Report"
            .Range("A2").Value = Date
             
            'Now We Shall Build The Colum Headings.value is the text required
            .Range("A4").Value = "Customer Name "
            .Range("A4").HorizontalAlignment = XLLEFT
            .Range("A4").Columns.AutoFit
            .Range("B4").Value = "Due Between Now And 30 Days"
            .Range("C4").Value = "Due Between 30 And 60 Days"
            .Range("D4").Value = "Due Between 60 And 90 Days"
            .Range("E4").Value = "Due Between 90 And 120 Days"
            .Range("F4").Value = "All OverDue Greater Than 120 Day"
            .Range("G4").Value = "Total Outstanding"
            .Range("H4").Value = "Total Payment Due"
      
            'Format Column Headings set the text to the left on A4 so it looks nice, then center the values in the range B7:G3000 so all values are centered
            .Range("A4").HorizontalAlignment = XLLEFT
            .Range("B:G").HorizontalAlignment = XLCENTER
            'Then We set the withs to auto fit the comlum names nicely, to Bold you can use this on a seperate line .Range("A4:G4").Cells.Font.Bold = True
            .Range("A4").Columns.AutoFit
            .Range("B4").Columns.AutoFit
            .Range("C4").Columns.AutoFit
            .Range("D4").Columns.AutoFit
            .Range("E4").Columns.AutoFit
            .Range("F4").Columns.AutoFit
            .Range("G4").Columns.AutoFit
            .Range("H4").Columns.AutoFit
         
            'iRowStart is the row that the starting row that the recordset will enter data into
            iRowStart = 7
            'Then we Loop through recordset above and copy data from recordset until we get to the end of file
            Do While Not objRecordSet.EOF
        
                'center align the Headers between B4 and G4
                .Range("B4:G4").HorizontalAlignment = XLCENTER
            
                'fit the with of the A7 to fit the width of the widest supplier name
                .Range("A7").Columns.AutoFit
                'then we center the results
                .Range("B7:H7").HorizontalAlignment = XLCENTER
            
                'start importing the data from the record set above into the required columns A,B,C,D,E,F,G in this example
                .Range("A" & iRowStart).Value = Nz(objRecordSet![Customer Name], "")
                .Range("B" & iRowStart).Value = Nz(objRecordSet![Due Between Now And 30 Days], "")
                .Range("C" & iRowStart).Value = Nz(objRecordSet![Due Between 30 And 60 Days], 0)
                .Range("D" & iRowStart).Value = Nz(objRecordSet![Due Between 60 And 90 Days], 0)
                .Range("E" & iRowStart).Value = Nz(objRecordSet![Due Between 90 And 120 Days], 0)
                .Range("F" & iRowStart).Value = Nz(objRecordSet![All OverDue Greater Than 120 Day], 0)
                .Range("G" & iRowStart).Value = Nz(objRecordSet![Total Outstanding], 0)
                iRowStart = iRowStart + 1
                objRecordSet.MoveNext
            Loop
        
            'Now We Add Some Nice Conditional Formatting, Text Will Re Red If Value Greater Than Zero
            With .Range("B7:G3000").FormatConditions.Add(XLCELLVALUE, XLGREATER, 0)
                .font.color = RGB(255, 53, 53)
            End With
           
            'Then we calculate the total Payments required to Supplier
            .Range("H7").Formula = "=SUM($G7:$G65000)"
            .Range("H7").font.color = vbRed
                   
            'then we set a footer, we start this two rows below the last entry and leave a gap of 3 rows and format the footer nicely
            iRowStart = iRowStart + 2
            .Range("A" & iRowStart).Value = "These Are All The Payments Outstanding That You Are Due To Receive."
            .Range("A" & iRowStart).font.color = vbRed
                
        End With
        DoCmd.Hourglass False
        objExcelApp.Visible = True
        objRecordSet.Close
        Set objRecordSet = Nothing
        Set objExcelApp = Nothing
        Set objExcelBook = Nothing
        Set objExcelSheet = Nothing
        On Error GoTo 0
        Exit Sub
    End If

The code on frm2 that always tells me there is no data to export looks like this, it always shows the msgbox highlighted in red

Code:

Dim objExcelApp As Object
    Dim objExcelBook As Object
    Dim objExcelSheet As Object
    Dim strSql As String
    Dim objRecordSet As DAO.Recordset
    Dim iRowStart As Integer
    Const XLCENTER = -4108
    Const XLLEFT = -4131 'xlRight is -4152
    Const XLCELLVALUE = 1
    Const XLGREATER = 5
    '    Const xlExpression = 2
    Dim objO As Object
  
  
    On Error Resume Next
    Set objExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        ' No Outlook is not open, try and create object
        Err.Clear
        Set objO = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
      
            MsgBox "Excel Is Either Not Installed Or Is Unavalible You Can Not Export To Excel"
            Err.Clear
            
            Exit Sub
        End If
    End If
   
    'Now we set up the SQL used for the Recordset (RS1)
    strSql = "SELECT tblCustomers.FullName AS [Customer Name], Count(tblAccountTransactions.ID) AS [Number Of Paid Invoices], Sum(tblAccountTransactions.TotalPayment), " & _
        "AS [Total Income], Avg(tblAccountTransactions.TotalPayment) AS [Average Income], " & _
        "FROM tblAccountTransactions INNER JOIN tblCustomers ON tblAccountTransactions.CustomerID = tblCustomers.CustomerID" & _
        "GROUP BY tblCustomers.FullName, tblAccountTransactions.CustomerID"
    
    'Execute query and populate recordset
    Set objRecordSet = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
   
    'If no data, don't bother opening Excel, just quit
    If objRecordSet.RecordCount = 0 Then
        MsgBox "No Data Selected For Export", vbInformation + vbOKOnly, "No data exported"
               Exit Sub
    Else
    
        'We Shall Turn On The Hour Glass, So That Users Know That Something Is Happening
        DoCmd.Hourglass (True)
    
        'Create an instance of Excel and start building a spreadsheet Late Binding Used So No Refrences Required
        Set objExcelApp = CreateObject("Excel.Application")
   
        objExcelApp.Visible = False
        Set objExcelBook = objExcelApp.Workbooks.Add() 'start a new workbook
        Set objExcelSheet = objExcelBook.Worksheets(1)
 
        With objExcelSheet
            .Name = "Customer Sales Report"
            .Cells.font.Name = "Arial"
            .Cells.font.Size = 10
        
            'Format using these examples .Columns("A").NumberFormat = "$#,##0.00;-$#,##0.00"
           
            .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"
           
       
            'Format range as required To Bold a Cell use this for example .Range("A1").Cells.Font.Bold = True
            .Range("A1", "H1").Merge
            .Range("A1").Columns.AutoFit
            .Range("A2", "H2").Merge
            .Range("A2").Columns.AutoFit
            .Range("A1").HorizontalAlignment = XLLEFT
            .Range("A2").HorizontalAlignment = XLLEFT
            .Range("A1").Cells.font.Name = "Arial"
            .Range("A2").Cells.font.Name = "Arial"
            .Range("A1").Cells.font.Size = 12
            .Range("A2").Cells.font.Size = 10
            .Range("A1").Value = DLookup("[CompanyName]", "[tblCompanyDetails]", "[CompanyID]=1") & " " & "Customer Sales Report"
            .Range("A2").Value = Date
             
            'Now We Shall Build The Colum Headings.value is the text required
            .Range("A4").Value = "Customer Name"
            .Range("A4").HorizontalAlignment = XLLEFT
            .Range("A4").Columns.AutoFit
            .Range("B4").Value = "Number Of Paid Invoices"
            .Range("C4").Value = "Total Income"
            .Range("D4").Value = "Average Income"
        
      
            'Format Column Headings set the text to the left on A4 so it looks nice, then center the values in the range B7:G3000 so all values are centered
            .Range("A4").HorizontalAlignment = XLLEFT
            .Range("B:D").HorizontalAlignment = XLCENTER
            'Then We set the withs to auto fit the comlum names nicely, to Bold you can use this on a seperate line .Range("A4:G4").Cells.Font.Bold = True
            .Range("A4").Columns.AutoFit
            .Range("B4").Columns.AutoFit
            .Range("C4").Columns.AutoFit
            .Range("D4").Columns.AutoFit
         
         
            'iRowStart is the row that the starting row that the recordset will enter data into
            iRowStart = 7
            'Then we Loop through recordset above and copy data from recordset until we get to the end of file
            Do While Not objRecordSet.EOF
        
                'center align the Headers between B4 and G4
                .Range("B4:G4").HorizontalAlignment = XLCENTER
            
                'fit the with of the A7 to fit the width of the widest supplier name
                .Range("A7").Columns.AutoFit
                'then we center the results
                .Range("B7:D7").HorizontalAlignment = XLCENTER
            
                'start importing the data from the record set above into the required columns A,B,C,D,E,F,G in this example
                .Range("A" & iRowStart).Value = Nz(objRecordSet![Customer Name], "")
                .Range("B" & iRowStart).Value = Nz(objRecordSet![Number Of Paid Invoices], "")
                .Range("C" & iRowStart).Value = Nz(objRecordSet![Total Income], 0)
                .Range("D" & iRowStart).Value = Nz(objRecordSet![Average Income], 0)
                
                iRowStart = iRowStart + 1
                objRecordSet.MoveNext
            Loop
        
                             
            'then we set a footer, we start this two rows below the last entry and leave a gap of 3 rows and format the footer nicely
            iRowStart = iRowStart + 2
            .Range("A" & iRowStart).Value = "Customer Sales Report."
            .Range("A" & iRowStart).font.color = vbRed
                
        End With
        DoCmd.Hourglass False
        objExcelApp.Visible = True
        objRecordSet.Close
        Set objRecordSet = Nothing
        Set objExcelApp = Nothing
        Set objExcelBook = Nothing
        Set objExcelSheet = Nothing
    End If

If I run the query from frm2 in the query designer the required data is shown as expected, I cant for the life of me work out why this is happening

Any help would be fantastic

steve

**June7** · 02-28-2018, 04:27 AM

I am surprised code in frm1 works.

Try MoveLast and MoveFirst. Review https://msdn.microsoft.com/en-us/lib.../ff821452.aspx

**sdel_nevo** · 02-28-2018, 05:00 AM

Hi June

Many thanks for getting back. I have tried the suggestion, but still no joy, I must say I'm pretty perplexed by it.

in total I have 5 forms including frm2 that run this code, it's only frm2 that has the issue.

I have added this code to frm2

Code:

'Execute query and populate recordset
    Set objRecordSet = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
    
    objRecordSet.MoveLast
    objRecordSet.MoveFirst
    MsgBox objRecordSet.RecordCount
 
    'If no data, don't bother opening Excel, just quit
    If objRecordSet.RecordCount = 0 Then
        MsgBox "No Data Selected For Export", vbInformation + vbOKOnly, "No data exported"
        Exit Sub
    Else

it compiles with no errors, but the code still stops at the "No data selected" msgbox

it defiantly looks like the value of the record count is being found as 0 but im a bit lost by it now to be honest, I have been looking at this the last week and im starting to think I cant see the woods for the trees lol

many thanks

Steve

**aytee111** · 02-28-2018, 10:09 AM

Add the line:
Debug.Print strSQL
Then copy the SQL from the immediate window to a new query window and run it, see what it produces.

**sdel_nevo** · 02-28-2018, 11:36 AM

Hi aytee111

I have done that and copied the output to a new query and the data is produced as expected.

I’m out of the office now but when back in I will take another look just to make sure

Steve

**June7** · 02-28-2018, 12:34 PM

I don't see how the compiled SQL could work in Access query designer.

Remove the commas at the end of Sum(tblAccountTransactions.TotalPayment), and [Average Income],

Add a space at the end of the FROM ... JOIN line.

Also, the query in frm1 is missing a space after AS [Total Outstanding]". Guess the [] compensate for the missing space.

Why bother with aliases?

**sdel_nevo** · 02-28-2018, 12:46 PM

Hi June

I will double check the compiled sql runs in the query designer I’m sure it did when I tried this morning, I will amend the code as you suggest, your right about the aliases as I can control these on the spread sheet anyway, I will remove the aliases also.

When I’m back in the office I will update you as to how it’s going

Many thanks for your help

Steve

**sdel_nevo** · 03-01-2018, 03:21 AM

Hi June and aytee111

I ran the debug code, and blow me it created an error I was 100% sure this worked when I lasted tested it

anyway, I amended the query, removed the aliases as i can manipulate these through excel

the code below works brilliantly.

Code:

 Dim objExcelApp As Object
    Dim objExcelBook As Object
    Dim objExcelSheet As Object
    Dim strSql As String
    Dim objRecordSet As DAO.Recordset
    Dim iRowStart As Integer
    Const XLCENTER = -4108
    Const XLLEFT = -4131 'xlRight is -4152
    Const XLCELLVALUE = 1
    Const XLGREATER = 5
    '    Const xlExpression = 2
    Dim objO As Object
    
    
    On Error Resume Next
    Set objExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        ' No Outlook is not open, try and create object
        Err.Clear
        Set objO = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
    
            MsgBox "Excel Is Either Not Installed Or Is Unavalible You Can Not Export To Excel"
            Err.Clear
    
            Exit Sub
        End If
    End If
    
    'This is the SQL that creates the data
    strSql = "SELECT tblCustomers.FullName, Count(tblAccountTransactions.ID) AS CountOfID, Sum(tblAccountTransactions.TotalPayment) AS SumOfTotalPayment, Avg(tblAccountTransactions.TotalPayment) AS AvgOfTotalPayment " & _
        "FROM tblAccountTransactions INNER JOIN tblCustomers ON tblAccountTransactions.CustomerID = tblCustomers.CustomerID GROUP BY tblCustomers.FullName, tblAccountTransactions.CustomerID;"
    
    'Execute query and populate recordset
    Set objRecordSet = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
    objRecordSet.MoveLast
    objRecordSet.MoveFirst
    
    
    'If no data, don't bother opening Excel, just quit
    If objRecordSet.RecordCount = 0 Then
        MsgBox "No Data Selected For Export", vbInformation + vbOKOnly, "No data exported"
        Exit Sub
    Else
    
        'We Shall Turn On The Hour Glass, So That Users Know That Something Is Happening
        DoCmd.Hourglass (True)
    
        'Create an instance of Excel and start building a spreadsheet Late Binding Used So No Refrences Required
        Set objExcelApp = CreateObject("Excel.Application")
    
        'hide excel as we build the spreadsheet
        objExcelApp.Visible = False
        Set objExcelBook = objExcelApp.Workbooks.Add() 'start a new workbook
        Set objExcelSheet = objExcelBook.Worksheets(1)
    
        With objExcelSheet
            .Name = "Customer Sales Report"
            .Cells.font.Name = "Arial"
            .Cells.font.Size = 10
    
            'Format using these examples .Columns("A").NumberFormat = "$#,##0.00;-$#,##0.00"
    
            .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"
            .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"
    
    
            'Format range as required To Bold a Cell use this for example .Range("A1").Cells.Font.Bold = True
            .range("A1", "H1").Merge
            .range("A1").Columns.AutoFit
            .range("A2", "H2").Merge
            .range("A2").Columns.AutoFit
            .range("A1").HorizontalAlignment = XLLEFT
            .range("A2").HorizontalAlignment = XLLEFT
            .range("A1").Cells.font.Name = "Arial"
            .range("A2").Cells.font.Name = "Arial"
            .range("A1").Cells.font.Size = 12
            .range("A2").Cells.font.Size = 10
            .range("A1").Value = DLookup("[CompanyName]", "[tblCompanyDetails]", "[CompanyID]=1") & " " & "Customer Sales Report"
            .range("A2").Value = Date
    
            'Now We Shall Build The Colum Headings.value is the text required
            .range("A4").Value = "Customer Name"
            .range("A4").HorizontalAlignment = XLLEFT
            .range("A4").Columns.AutoFit
            .range("B4").Value = "Number Of Orders"
            .range("C4").Value = "Total Payment"
            .range("D4").Value = "Avgerage Payments"
    
    
            'Format Column Headings set the text to the left on A4 so it looks nice, then center the values in the range B7:G3000 so all values are centered
            .range("A4").HorizontalAlignment = XLLEFT
            .range("B:D").HorizontalAlignment = XLCENTER
            
            'Then We set the withs to auto fit the comlum names nicely, to Bold you can use this on a seperate line .Range("A4:G4").Cells.Font.Bold = True
            .range("A4").Columns.AutoFit
            .range("B4").Columns.AutoFit
            .range("C4").Columns.AutoFit
            .range("D4").Columns.AutoFit
    
    
            'iRowStart is the row that the starting row that the recordset will enter data into
            iRowStart = 7
            
            'Then we Loop through recordset above and copy data from recordset until we get to the end of file
            Do While Not objRecordSet.EOF
    
                'center align the Headers between B4 and G4
                .range("B4:G4").HorizontalAlignment = XLCENTER
    
                'fit the with of the A7 to fit the width of the widest supplier name
                .range("A7").Columns.AutoFit
                'then we center the results
                .range("B7:D7").HorizontalAlignment = XLCENTER
    
                'start importing the data from the record set above into the required columns A,B,C,D,E,F,G in this example
                .range("A" & iRowStart).Value = Nz(objRecordSet![FullName], "")
                .range("B" & iRowStart).Value = Nz(objRecordSet![CountOfID], "")
                .range("C" & iRowStart).Value = Nz(objRecordSet![SumOfTotalPayment], 0)
                .range("D" & iRowStart).Value = Nz(objRecordSet![AvgOfTotalPayment], 0)
    
                iRowStart = iRowStart + 1
                objRecordSet.MoveNext
                
            Loop
    
    
            'then we set a footer, we start this two rows below the last entry and leave a gap of 3 rows and format the footer nicely
            iRowStart = iRowStart + 2
            .range("A" & iRowStart).Value = "Customer Sales Report."
            .range("A" & iRowStart).font.color = vbRed
            
            'place the cursor at the required place
            .range("A3").select
            
                        'NOW WE SAVE THE NEWLY CREATED EXCELFILE TO THE NETWORK
                        objExcelSheet.Application.DisplayAlerts = False
                        objExcelSheet.SaveAs (DLookup("FilePath", "tblCompanyDetails", "CompanyID = 1")) & "\Company Details" & "\Reports" & "\Customer Sales" & "\Customer Sales Report " & ".xls" 
                        objExcelSheet.Application.DisplayAlerts = True
            
                        'NOW WE OPEN THE FILE WE HAVE JUST SAVED
                        objExcelApp.Visible = True
                        objExcelApp.Workbooks.Open (DLookup("FilePath", "tblCompanyDetails", "CompanyID = 1")) & "\Company Details" & "\Reports" & "\Customer Sales" & "\Customer Sales Report " & ".xls" 
                        Set objExcelApp = Nothing
    
        End With
        
        DoCmd.Hourglass False
        ''objExcelApp.Visible = True
        objRecordSet.Close
        Set objRecordSet = Nothing
        Set objExcelApp = Nothing
        Set objExcelBook = Nothing
        Set objExcelSheet = Nothing
    
    End If

many many thanks for your help guys pointing me in the right direction.

i was starting to go mad over this

Steve

proplem exporting a query to excel with formatting

Thread Tools

proplem exporting a query to excel with formatting

Similar Threads

Exporting with formatting to excel Active X error 429

Exporting Query to Excel Conditional Formatting

Source Code for Formatting an Excel spreadsheet when Exporting a Query

Formatting off when exporting from access to excel

Exporting To Excel And Formatting, Office 2010

Posting Permissions