Code:
Dim stAppName As String, myURL As String, OpenChrome As String, myApp As String, stPathName As StringDim myCust As String, cAdd1 As String, cAdd2 As String, cTown As String, cPC As String, myDealer As String
Dim c1 As String, c2 As String, c3 As String, c4 As String, c5 As String, c8 As String, myPC As String
Dim xlRow As Long, LastRow As Long, ColCount As Long, LastRows2 As Long
Dim myRow As Integer, i As Integer, c6 As Integer, c7 As Integer, DelQty As Integer, myRows2 As Integer, i2 As Integer
Dim FindRow As Range
Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet, xlsht2 As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim rsDel As DAO.Recordset
Dim a1 As String, a2 As String, a3 As String, a4 As String, a5 As String
Dim DelTown As String, DelPC As String
srcPath = "T:\DMT Ltd\XL Files\"
srcFile = "New Items.xlsx"
myCust = Forms!frmMainMenu!frmIndex1!cboCustomer
cAdd1 = DLookup("Add1", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cAdd2 = DLookup("Add2", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cTown = DLookup("Town", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cPC = DLookup("Postcode", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(srcPath & srcFile)
Set xlsht = xlWrkBk.Worksheets(1)
Set xlsht2 = xlWrkBk.Worksheets(2)
xl.Visible = False
LastRow = xlsht2.UsedRange.Rows.Count
myRow = "1"
For i = myRow To LastRow
'xlsht2
c1 = xlsht2.Cells(i, 1).Value 'MFG
c2 = xlsht2.Cells(i, 2).Value ' SL
c3 = xlsht2.Cells(i, 3).Value 'DELTO
c4 = xlsht2.Cells(i, 4).Value 'END USER
c5 = xlsht2.Cells(i, 6).Value 'ITEM TYPE
'combined date to go to different cells on xksht1,also search town and postcode
a1 = c3
a2 = c1 & "-" & c4
a3 = c2
a4 = c5
DelTown = DLookup("Town", "tblDealers", "[Name] Like ""*" & c3 & "*""")
DelPC = DLookup("Postcode", "tblDealers", "[Name] Like ""*" & c3 & "*""")
xlsht.Cells(i, 2).Value = a1
xlsht.Cells(i, 3).Value = DelTown
xlsht.Cells(i, 4).Value = DelPC
xlsht.Cells(i, 5).Value = a2
xlsht.Cells(i, 6).Value = a3
xlsht.Cells(i, 9).Value = a4
Next i
xl.Quit
Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing