Exporting Contacts From Access To Excel

[DMT Dave]

Hi Guy's I haven't started this yet but need to quite quickly!!!



I have an Excel file called Contacts.xlsx, there are 26 pages named A-B-C To Z

What is the easiest option to have a command button to export Data form a contact Table to the relevant page

There is only one issue that i will come across is some contacts can start with a number and that would need to be on page A

Similar to my last post but setting criteria's where Name starts = <b then name starts with b then name starts with c to got to pages A-B & C

I haven't generated a starting point yet but just a general idea would help please ?

ie:

myCriteria = < B
Next Criteria = C (Will I need 26 Criterias to cater for the alphabet ?
Set the rs WHERE Name = < b etc etc
Open XL File
With xl Work Sheet A Output the recordset
Next Output

I don't want to start without the general so if it's possible, what is the best way to achieve this please ?

Kindest

[Minty]

You can determine if a string contains a number first by using the Val() function
Air code

If Val(MyString) > 0 Then ' The contact starts with a number

You could even add this a a calculated field in your query to drive the page sorting for the export.

MyExcelWSTab: IIF (Val(MyString) > 0 Then "0-9", Left(MyString,1))

[ranman256]

adjust the table name / fields as you need

Public Sub export26()
Dim sSql As String
Dim vChr
Dim i As Integer
Dim qdf As QueryDef
Const kQRY = "qsNames2Export"
Dim vFile, vLtr


On Error Resume Next


vFile = "c:\temp\contacts.xls"


 sSql = "select * from tClients where left(ucase([lastn]),1)<='A'"
Set qdf = New QueryDef
qdf.SQL = sSql
qdf.Name = kQRY
CurrentDb.QueryDefs.Append qdf


For i = 65 To 90
     vLtr = Chr(i)
     If i = 65 Then   'A or less
        sSql = "select * from tClients where left(ucase([lastn]),1)<='" & vLtr & "' ORDER BY tClients.LastN, tClients.FirstN;"
     Else
        sSql = "select * from tClients where left(ucase([lastn]),1)='" & vLtr & "' ORDER BY tClients.LastN, tClients.FirstN;"
     End If
     
     Set qdf = CurrentDb.QueryDefs(kQRY)
     qdf.SQL = sSql
     qdf.Close
     
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, kQRY, vFile, True, vLtr
Next
Set qdf = Nothing
End Sub

[CJ_London]

to remove the starting number perhaps use the val function like this

?replace("123ABC",val("123ABC"),"",,1)
ABC

Or to get the sheet character

?left(replace("123ABC",val("123ABC"),"",,1),1)
A
?left(replace("CBA",val("CBA"),"",,1),1)
C

[ArviLaanemets]

Why do you need a code for this?

In excel, create an ODBC query which reads your contacts table from your Access database [back-end]

And no need for have a separate sheet for every litera in Excel. Either
a) Read all contacts into single table, and use autofilter feature in Excel do display any selection of contacts you want;
b) Add a Data Validation list to cell at top of sheet with querytable, where user can select a litera (A or B or C etc.), and the query uses this cell as parameter to update returned data immediately, and returns the list of contacts matching for this selection.

[Micron]

Aren't you making this much more difficult with 26 sheets vs 1 sheet with sorted names? Some sheets will only have a couple of names on them, some none? I have to imagine that this workbook isn't going to be used much, otherwise why not just work with the Outlook address book?

[DMT Dave]

Thank you to all of you who have advised, i have studied your ideas and will get a start on this shortly,

Micron just to answer, I am maybe thinking down the wrong lines but the reason for this

Customer updates online (one drive) spreadsheet

My button opens (contacts file)

1st Page Called Input we copy and paste from one drive to input page

Customer only adds Name, Town, Post code

I need to put more data in and I always think the most unique criteria to DLookup is postcode, Note > we never have 1 company next door to another nor private addresses sp postcodes are all different.

The issue i have is the customer uploading info can have character incorrect (maybe typo) on their system therefore most of my code doesn't work as postcode doesn't match

so I thought which ever is incorrect, for quickness click on the relevant page letter: copy postcode, return to 1st page (input page) and paste the postcode we have got

The database will now add all of the records from the input page

MY LAST THREAD IS INVOLVED WITH THIS COPY EXCEL TO DB works fantastic but this is something i need to add as I found a flaw (Customer doesn't always have the same postcode)

I like all of your inputs though and going to read through a few times

Thank you again to all of you, will let you know how I get on and any more suggestions based on this I will study

Have a fab day and kindest

[DMT Dave]

Just adpated ranmans suggestion works great

also looked at other options and will do some studying

Thank you guys