Code:
Private Sub bt_Export_Click()
Dim dt As String
Dim n As Integer
Dim rspart As Dao.Recordset
Dim xlApp As Excel.Application
Dim strqry1 As String
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim temp_path As String
dt = Format(Now, "dd.mm.yyyy")
strqry1 = "SELECT orderDetails.internalNo AS [Internal No], orderDetails.transactionDate AS [Transaction Date], orderNumber.adminSiteOrderId AS [Order No], " & _
"orderNumber.newMemberSiteOrderId AS [Site Order ID], product.rewardTitle AS [Reward Title], product.variationSKU AS [Variation SKU], product.variationTitle AS " & _
"[Variation Title], partnerDetails.Name AS [Redemption Partner], category.name AS [Redemption Category], orderDetails.unitPointCost AS Point, " & _
"orderDetails.redemptionQuantity AS Qty, orderDetails.pointsRedeemedonReward AS [Points Redeemed], mailMethod.service AS [Fulfilment Method], orderStatus.Status " & _
"AS [Redemption Status], orderDetails.additionalField AS [Additional Field], buisness.reference AS [Business ID], buisness.name AS [Business Name], distributor.firstName " & _
"AS [Contact First Name], distributor.LastName AS [Contact Last Name], distributor.address1 AS [Address 1], distributor.address2 AS [Address 2], distributor.address3 AS " & _
"[Address 3], distributor.state AS [County/State], distributor.postCode AS [Postcode/Zip], country.name AS Country, distributor.contactLogin AS [Contact Login], " & _
"distributor.emailAddress AS [Contact Email Address], distributor.phoneNumber AS contact_phone_number, orderDetails.numberIM AS [Number for IM], " & _
"orderInvoice.dateRecieved AS [Date received], partnerBiWeekly.Description AS [Order List], orderInvoice.orderSentdate AS [Order sent date], orderInvoice.poNo AS [PO No], " & _
"orderInvoice.totalpoamt AS [Total PO Amt], orderInvoice.poSentDate AS [PO Sent Date], orderInvoice.invoiceRecieved AS [Invoice received], orderInvoice.invoiceNo " & _
"AS [Invoice No], orderInvoice.invoiceAmt AS [Invoice Amt], orderInvoice.ttDate AS [TT date (wed/Fri)], orderInvoice.paymentNoticeSentDate AS [Payment Notice Sent Date], " & _
"orderList.unitPrice AS [Unit Price], orderList.totalCost AS [Total Cost], orderList.adminCost AS [Admin cost], orderList.shippingCost AS [Shipping/ Handling Cost], " & _
"orderList.vatTax AS [VAT / GST TAX], orderList.grandTotal AS [Grand Total], orderList.deliveredQty AS [Delivered Qty], orderList.deliveredDate AS [Delivery Date], " & _
"orderList.trackingNo AS [Tracking #], orderList.notes AS Notes, orderList.distiInvoice AS [Disti invoice#], orderInvoice.finalStatus AS [Final Status Pending], " & _
"orderInvoice.flipStatusDueDate AS [Flip status date], orderDetails.lastUpdate " & _
"FROM (partnerBiWeekly INNER JOIN partnerDetails ON partnerBiWeekly.Id = partnerDetails.Id) INNER JOIN (orderStatus INNER JOIN (product INNER JOIN ((country " & _
"INNER JOIN distributor ON country.Id = distributor.Id) INNER JOIN (category INNER JOIN (buisness INNER JOIN ((((orderDetails INNER JOIN mailMethod ON " & _
"orderDetails.methodId = mailMethod.methodId) INNER JOIN orderInvoice ON orderDetails.orderInvoiceId = orderInvoice.orderInvoiceId) INNER JOIN orderList ON " & _
"orderDetails.orderListId = orderList.orderListId) INNER JOIN orderNumber ON orderDetails.orderId = orderNumber.orderId) ON buisness.buisnessDetailId = " & _
"orderDetails.buisnessDetailId) ON category.categoryId = orderDetails.categoryId) ON distributor.distributorId = orderDetails.distributorId) ON product.productId = " & _
"orderDetails.productId) ON orderStatus.orderStatusId = orderDetails.orderStatusId) ON partnerDetails.partnerDetailsId = orderDetails.partnerDetailsId " & _
"ORDER BY orderDetails.internalNo;"
'opening recordset and assiging the field values to variables
Set rspart = CurrentDb.OpenRecordset(strqry1, dbOpenDynaset)
rspart.MoveLast
n = rspart.RecordCount
'Defining invoice report format and storing creation path to a variable
'creating Excel App object and opening the Invoice Template file from specified path
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(temp_path)
'inserting variables values to Excel
Set ws = wb.Worksheets("Sheet1") 'Specify your worksheet name
rspart.MoveFirst
For i = 2 To n + 1
ws.Cells.Font.Name = "Cambria"
ws.Cells.Font.Size = "11"
If (rspart![Final Status Pending] = "Cancelled") Then
ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Interior.ColorIndex = 3 'For Red color
ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Font.Strikethrough = True
ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
ws.Cells(i, 2) = rspart![Transaction Date]
ws.Cells(i, 3) = rspart![Order No]
ws.Cells(i, 4) = rspart![Site Order ID]
ws.Cells(i, 5) = rspart![Reward Title]
ws.Cells(i, 6) = rspart![Variation SKU]
ws.Cells(i, 7) = rspart![Variation Title]
ws.Cells(i, 8) = rspart![Redemption Partner]
ws.Cells(i, 9) = rspart![Redemption Category]
ws.Cells(i, 10) = rspart![Point]
ws.Cells(i, 11) = rspart![Qty]
ws.Cells(i, 12) = rspart![Points Redeemed]
ws.Cells(i, 13) = rspart![Fulfilment Method]
ws.Cells(i, 14) = rspart![Redemption Status]
ws.Cells(i, 15) = rspart![Additional Field]
ws.Cells(i, 16) = rspart![Business ID]
ws.Cells(i, 17) = rspart![Business Name]
ws.Cells(i, 18) = rspart![Contact First Name]
ws.Cells(i, 19) = rspart![Contact Last Name]
ws.Cells(i, 20) = rspart![Address 1]
ws.Cells(i, 21) = rspart![Address 2]
ws.Cells(i, 22) = rspart![Address 3]
ws.Cells(i, 23) = rspart![County/State]
ws.Cells(i, 24) = rspart![Postcode/Zip]
ws.Cells(i, 25) = rspart![Country]
ws.Cells(i, 26) = rspart![Contact Login]
ws.Cells(i, 27) = rspart![Contact Email Address]
ws.Cells(i, 28) = rspart![contact_phone_number]
ws.Cells(i, 29) = rspart![Number for IM]
ws.Cells(i, 30) = rspart![Date received]
ws.Cells(i, 31) = rspart![Order List]
ws.Cells(i, 32) = "Cancelled"
ws.Cells(i, 33) = "Cancelled"
ws.Cells(i, 34) = "Cancelled"
ws.Cells(i, 35) = "Cancelled"
ws.Cells(i, 36) = "Cancelled"
ws.Cells(i, 37) = "Cancelled"
ws.Cells(i, 38) = "Cancelled"
ws.Cells(i, 39) = "Cancelled"
ws.Cells(i, 40) = "Cancelled"
ws.Cells(i, 41) = ""
ws.Cells(i, 42) = ""
ws.Cells(i, 43) = ""
ws.Cells(i, 44) = ""
ws.Cells(i, 45) = ""
ws.Cells(i, 46) = ""
ws.Cells(i, 47) = ""
ws.Cells(i, 48) = "Cancelled"
ws.Cells(i, 49) = "Cancelled"
ws.Cells(i, 50) = ""
ws.Cells(i, 51) = "Cancelled"
ws.Cells(i, 52) = "Cancelled"
ws.Cells(i, 53) = rspart![Flip status date]
ws.Cells(i, 54) = rspart![lastUpdate]
Else
If (Format(rspart![lastUpdate], "mm/dd/yyyy") = Format(Now(), "mm/dd/yyyy")) Then
ws.Cells.Range(ws.Cells(i, 1), ws.Cells(i, 54)).Interior.ColorIndex = 6 'For Red color
ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
ws.Cells(i, 2) = rspart![Transaction Date]
ws.Cells(i, 3) = rspart![Order No]
ws.Cells(i, 4) = rspart![Site Order ID]
ws.Cells(i, 5) = rspart![Reward Title]
ws.Cells(i, 6) = rspart![Variation SKU]
ws.Cells(i, 7) = rspart![Variation Title]
ws.Cells(i, 8) = rspart![Redemption Partner]
ws.Cells(i, 9) = rspart![Redemption Category]
ws.Cells(i, 10) = rspart![Point]
ws.Cells(i, 11) = rspart![Qty]
ws.Cells(i, 12) = rspart![Points Redeemed]
ws.Cells(i, 13) = rspart![Fulfilment Method]
ws.Cells(i, 14) = rspart![Redemption Status]
ws.Cells(i, 15) = rspart![Additional Field]
ws.Cells(i, 16) = rspart![Business ID]
ws.Cells(i, 17) = rspart![Business Name]
ws.Cells(i, 18) = rspart![Contact First Name]
ws.Cells(i, 19) = rspart![Contact Last Name]
ws.Cells(i, 20) = rspart![Address 1]
ws.Cells(i, 21) = rspart![Address 2]
ws.Cells(i, 22) = rspart![Address 3]
ws.Cells(i, 23) = rspart![County/State]
ws.Cells(i, 24) = rspart![Postcode/Zip]
ws.Cells(i, 25) = rspart![Country]
ws.Cells(i, 26) = rspart![Contact Login]
ws.Cells(i, 27) = rspart![Contact Email Address]
ws.Cells(i, 28) = rspart![contact_phone_number]
ws.Cells(i, 29) = rspart![Number for IM]
ws.Cells(i, 30) = rspart![Date received]
ws.Cells(i, 31) = rspart![Order List]
ws.Cells(i, 32) = rspart![Order sent date]
ws.Cells(i, 33) = rspart![PO No]
ws.Cells(i, 34) = rspart![Total PO Amt]
ws.Cells(i, 35) = rspart![PO Sent Date]
ws.Cells(i, 36) = rspart![Invoice received]
ws.Cells(i, 37) = rspart![Invoice No]
ws.Cells(i, 38) = rspart![Invoice Amt]
ws.Cells(i, 39) = rspart![TT date (wed/Fri)]
ws.Cells(i, 40) = rspart![Payment Notice Sent Date]
ws.Cells(i, 41) = rspart![Unit Price]
ws.Cells(i, 42) = rspart![Total Cost]
ws.Cells(i, 43) = rspart![Admin cost]
ws.Cells(i, 44) = rspart![Shipping/ Handling Cost]
ws.Cells(i, 45) = rspart![VAT / GST TAX]
ws.Cells(i, 46) = rspart![Grand Total]
ws.Cells(i, 47) = rspart![Delivered Qty]
ws.Cells(i, 48) = rspart![Delivery Date]
ws.Cells(i, 49) = rspart![Tracking #]
ws.Cells(i, 50) = rspart![notes]
ws.Cells(i, 51) = rspart![Disti invoice#]
ws.Cells(i, 52) = rspart![Final Status Pending]
ws.Cells(i, 53) = rspart![Flip status date]
ws.Cells(i, 54) = rspart![lastUpdate]
Else
ws.Cells(i, 1) = "M" & Format(rspart![Internal No], "00000")
ws.Cells(i, 2) = rspart![Transaction Date]
ws.Cells(i, 3) = rspart![Order No]
ws.Cells(i, 4) = rspart![Site Order ID]
ws.Cells(i, 5) = rspart![Reward Title]
ws.Cells(i, 6) = rspart![Variation SKU]
ws.Cells(i, 7) = rspart![Variation Title]
ws.Cells(i, 8) = rspart![Redemption Partner]
ws.Cells(i, 9) = rspart![Redemption Category]
ws.Cells(i, 10) = rspart![Point]
ws.Cells(i, 11) = rspart![Qty]
ws.Cells(i, 12) = rspart![Points Redeemed]
ws.Cells(i, 13) = rspart![Fulfilment Method]
ws.Cells(i, 14) = rspart![Redemption Status]
ws.Cells(i, 15) = rspart![Additional Field]
ws.Cells(i, 16) = rspart![Business ID]
ws.Cells(i, 17) = rspart![Business Name]
ws.Cells(i, 18) = rspart![Contact First Name]
ws.Cells(i, 19) = rspart![Contact Last Name]
ws.Cells(i, 20) = rspart![Address 1]
ws.Cells(i, 21) = rspart![Address 2]
ws.Cells(i, 22) = rspart![Address 3]
ws.Cells(i, 23) = rspart![County/State]
ws.Cells(i, 24) = rspart![Postcode/Zip]
ws.Cells(i, 25) = rspart![Country]
ws.Cells(i, 26) = rspart![Contact Login]
ws.Cells(i, 27) = rspart![Contact Email Address]
ws.Cells(i, 28) = rspart![contact_phone_number]
ws.Cells(i, 29) = rspart![Number for IM]
ws.Cells(i, 30) = rspart![Date received]
ws.Cells(i, 31) = rspart![Order List]
ws.Cells(i, 32) = rspart![Order sent date]
ws.Cells(i, 33) = rspart![PO No]
ws.Cells(i, 34) = rspart![Total PO Amt]
ws.Cells(i, 35) = rspart![PO Sent Date]
ws.Cells(i, 36) = rspart![Invoice received]
ws.Cells(i, 37) = rspart![Invoice No]
ws.Cells(i, 38) = rspart![Invoice Amt]
ws.Cells(i, 39) = rspart![TT date (wed/Fri)]
ws.Cells(i, 40) = rspart![Payment Notice Sent Date]
ws.Cells(i, 41) = rspart![Unit Price]
ws.Cells(i, 42) = rspart![Total Cost]
ws.Cells(i, 43) = rspart![Admin cost]
ws.Cells(i, 44) = rspart![Shipping/ Handling Cost]
ws.Cells(i, 45) = rspart![VAT / GST TAX]
ws.Cells(i, 46) = rspart![Grand Total]
ws.Cells(i, 47) = rspart![Delivered Qty]
ws.Cells(i, 48) = rspart![Delivery Date]
ws.Cells(i, 49) = rspart![Tracking #]
ws.Cells(i, 50) = rspart![notes]
ws.Cells(i, 51) = rspart![Disti invoice#]
ws.Cells(i, 52) = rspart![Final Status Pending]
ws.Cells(i, 53) = rspart![Flip status date]
ws.Cells(i, 54) = rspart![lastUpdate]
End If
End If
rspart.MoveNext
Next i
xlApp.Application.DisplayAlerts = False
'xlApp.Application.ActiveWorkbook.CheckCompatibility = False
'xlApp.Application.ActiveWorkbook.SaveAs FileName:="E:\XYZ\Redemptions\APAC\Order Processing\MDB\Database Files\MasterLog\Master Log_Mumbai_" & dt & ".xlsx", FileFormat:=-4143
xlApp.Application.ActiveWorkbook.SaveAs FileName:="E:\XYZ\Redemptions\APAC\Order Processing\MDB\Database Files\MasterLog\Master Log_Mumbai_" & dt & ".xlsx"
xlApp.Application.ActiveWorkbook.Close
xlApp.DisplayAlerts = True
'wb.SaveAs inv_rptpath 'Saves as Excel
xlApp.Quit
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing
rspart.Close
MsgBox "Master Log data successfully Exported to D:\XYZ\MasterLog_" & dt & ".xls", vbInformation
End Sub