Export to Excel

[access7]

Good Morning

I am hoping for some advice on exporting data into Excel (2010). I have written the following VBA which is working really well. The only issue I have is that two of the fields (LTD and PAYE) are yes / no fields and the data is exported as True / False into the spreadsheet. This wont mean much to the use so I am hoping for advice on how to get this to say Yes / No instead on the spreadsheet....

Code as follows:

Private Sub CmdRptHot_Click()

' set references for recordset and creating an instance of excel
Dim recContract As DAO.Recordset
Dim lsSQL As String
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
' get info for report from database
lsSQL = ""
lsSQL = " SELECT [Company], [Ltd], [PAYE], [Timesheets], [Revenue] FROM tbl_Company "
lsSQL = lsSQL & " Where CompanyType = 2 "
lsSQL = lsSQL & " AND Status = 'Hot'"
' create a recordset to be exported into excel
Set recContract = CurrentDb.OpenRecordset(lsSQL)
' create a new instance of excel and open at a new workbook, sheet1
Set XL = New Excel.Application
Set XLBook = XL.Workbooks.Add
XL.Visible = True
XLBook.Windows(1).Visible = True
Set XLSheet = XLBook.Worksheets(1)

' Create headings for data that is being exported *** apart from sheet heading - done at later stage
XLSheet.Range("A3") = "Company Name"
XLSheet.Range("B3") = "Ltd"
XLSheet.Range("C3") = "PAYE"
XLSheet.Range("D3") = "Est. Timesheets"
XLSheet.Range("E3") = "Est. Revenue"

' format the properties of the cells to how you want them to appear, e.g. bold, font size, alignment etc.
XLSheet.Range("A3", "E3").Font.Bold = True
XLSheet.Columns.Range("A:A", "E:E").EntireColumn.HorizontalAlignment = xlCenter

' populate excel with data from SQL statement
XLSheet.Range("A5").CopyFromRecordset recContract

' re-size the columns to fit data that has just been imported
XLSheet.Columns.Range("A:A", "E:E").EntireColumn.AutoFit

' Add the title of the spreadsheet - this is done at the end, after the autofit of columns to avoid the column with the title in being too big
XLSheet.Range("A1") = "Hot Prospects"
XLSheet.Range("A1").Font.Size = 16
XLSheet.Range("A1").Font.Bold = True
XLSheet.Range("A1").HorizontalAlignment = xlLeft

'XLSheet.Range("B5", "B100").FormulaR1C1 = "=IF(""True"",""Yes"",""NO"")" - ***** NOT WORKING - NEED NEW FORMULA TO TURN 'TRUE' INTO YES AND 'FALSE' INTO NO
'XLSheet.Range("C6").FormulaR1C1 = "=IF(""False"",""No"",""Yes"")"

' reset
recContract.Close
Set XL = Nothing
Set XLBook = Nothing
Set XLSheet = Nothing

End Sub

 

[vbaInet]

Two ways:

1. In your query in Access use the IIF() function to translate the field. E.g.:

IIF([[COLOR=Red]Field[/COLOR]], "Yes", "No")

2. Or you can export it to Excel as is and do a Find/Replace on that column alone.

 

[access7]

Thanks! I have added this to the query and then changed the code to look at the query rather than the table - not sure why I didnt do that to begin with to be honest, much simplier and it means I dont need to filter in the SQL statement as the query does it all for me

I think I was confused with the SELECT SQL statement I wrote, I wasnt aware you could reference queries themselves.

Thanks so much - you've saved me a lot of time and taught me something new

 

[vbaInet]

Oh yes you could.

You're welcome.