Help Importing Excell data to Access table

[ifshaanm]

Hi All

I have a VBA function to syncsuppliers as below

Function SyncSuppliers()

On Error GoTo errhandle
Filename = DLookup("SupplierPath", "Setup", "SetupActive = True")
If Filename = "" Then
Exit Function
End If
Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Open(Filename)
Set xlsheet = xlBook.Sheets("Cordell Suppliers")
xlapp.Visible = False

'Supplier Section

Dim SuppID As Variant
Dim SupName() As String
Dim SupplierName As String
Dim SupplierTable As DAO.Recordset
Dim TeleFaxStr() As String
Set xlRange = xlsheet.Range("E11:E1000")
For Each MyCell In xlRange
If (xlsheet.Cells(MyCell.row, 1).value <> "") Then

If xlsheet.Cells(MyCell.row, 5).value = "" Then
SupplierName = Trim(LastSupName) & " (2)"
Else
SupName = Split(xlsheet.Cells(MyCell.row, 5).value, " (")
SupplierName = Trim(SupName(0))
LastSupName = Trim(SupName(0))

End If
SuppID = DLookup("[SupplierID]", "[Approved Suppliers]", "[SupplierName]= '" & SupplierName & "'")

'Supplier already Exists
If Not IsNull(SuppID) Then

Set SupplierTable = CurrentDb.OpenRecordset("Approved Suppliers")

SupplierTable.FindFirst "SupplierID = " & SuppID
If Not SupplierTable.NoMatch Then

SupplierTable.Edit
SupplierTable!SupplierName = SupplierName
SupplierTable!Address = xlsheet.Cells(MyCell.row, 7).value
TeleFaxStr = Split(xlsheet.Cells(MyCell.row, 6).value, "FAX-")
SupplierTable!Telephone = Trim(Right(TeleFaxStr(0), Len(TeleFaxStr(0)) - 4))
SupplierTable!FaxNumber = Trim(TeleFaxStr(1))
SupplierTable!RiskFactor = xlsheet.Cells(MyCell.row, 2).value
SupplierTable!Status = xlsheet.Cells(MyCell.row, 1).value
SupplierTable!LastUpdated = Now()
SupplierTable.Update

End If

Else
Set SupplierTable = CurrentDb.OpenRecordset("Approved Suppliers")

SupplierTable.AddNew
SupplierTable!SupplierName = SupplierName
SupplierTable!Address = xlsheet.Cells(MyCell.row, 7).value
TeleFaxStr = Split(xlsheet.Cells(MyCell.row, 6).value, "FAX-")
SupplierTable!Telephone = Trim(Right(TeleFaxStr(0), Len(TeleFaxStr(0)) - 4))
SupplierTable!FaxNumber = Trim(TeleFaxStr(1))
SupplierTable!RiskFactor = xlsheet.Cells(MyCell.row, 2).value
SupplierTable!Status = xlsheet.Cells(MyCell.row, 1).value
SupplierTable!LastUpdated = Now()
SupplierTable.Update

End If

Else
Exit For
End If
Next MyCell
Dim DateStr() As String

'Set SupplierTable = CurrentDb.OpenRecordset("Approved Suppliers")
''Check to see if the recordset actually contains rows

Set SupplierTable = Nothing 'Clean up
errhandle:
Resume Next
'msgstr = msgstr & CStr(MyCell.row) & " "
xlsheet.Close
xlBook.Close
xlapp.Quit
Set xlsheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
MsgBox ("Approved Supplier List Updated")
End Function

The data imports into mu access table but stops at the first bracket?

sample data in excel sheet:

APPROVED M AIR RECEIVERS Design and manufacture of pressure vessels and air receivers ABBOTT & CO (NEWARK) LTD TEL-01636 704208 FAX-01636 705742 Newark Boiler Works, Northern Road, Newark, NG24 2EJ APPROVED M AGENCY The provision of temporary and permanent staff resourcing ABC CONTRACT SERVICES LIMITED TEL-01582 692692 FAX-08700 500357 800 The Boulevard, Capability Green, Luton, Bedfordshire, LU1 3BA APPROVED M LIGHTING Hazardous Area & Industrial Lighting A-BELCO LTD (HADAR LIGHTING) TEL- 01670 813275 FAX- 01670 851141 Jubilee Industrial Estate, Ashington, Norhumberland NE63 8UG

The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row
A-BELCO LTD will import (HADAR LIGHTING) does not.

Any help is much appreciated.

Ifshaanm

[Arkadi]

ifshaanm,

Please wrap your code in code tags in future... much easier to read and follow (highlight the code, and hit the # symbol). Any chance you can post the workbook itself (or a copy without sensitive data).

[ifshaanm]

Hi sorry new to forums,thanks for the tip.

sample workbook attached

Please Login or Register  to view this content.

[Arkadi]

Without the rest of the code it is impossible for me to step through this to find the issue...
So... the values you are referring to that get cut off, that's SupplierName?

[Arkadi]

What is this line for?

Please Login or Register  to view this content.

it splits your supplier name left and right of the opening of a bracket, and then you are setting supplier name to the first half... so yes the code would not post supplier names with brackets, since if the name is not blank the first thing you do is split up the string for supplier name, and then assign to the variable SupplierName only what is to the left of "("

[ifshaanm]

yes the suppliers name gets cut of when the brackets appeari in the excel sheet until then it works fine.

example1: Brammers (Middlesbrough)
Brammers (Manchester)

only brammers will be exported into the access table.

example 2: Abbot & co (Newark) Ltd

only Abbot & co will be exported into access table

it is like the brackets end the export for that field.

[Arkadi]

No.... you are only exporting the variable SupplierName, and you are making SupplierName only the part before the brackets... that's how your code works. So again, why are you splitting suppliername from your sheet at the brackets?

[Arkadi]

ifshaanm,

Is this originally your code? or someone else's? If yours can you explain briefly why the split function is being used there, specifically why both SupplierName and LastSupplier are assigned only the first part of the supplier name before the brackets?