Hi folks,

I'm in need of some VBA code to do the following from a Microsoft Access Table (version 2007).
The idea is to attach the code to a macro in Access and attach to a button on a Access form.

I need to create/export two Excel spreadsheets from the table named "Main" and place them to a specified
location (For Example: C:\my exports). I've done transfer to spreadsheet in the past but in this case I need to do some specific checks and manipulation so help is greatly appreciated!

Table 'Main' has 6 columns (No Primary Keys):

Section: Text
Page #: Text
Item: Text: Text
New_Page #: Text
Page_Sort: Number
Delete: (Check Box)

Spreadsheet #1:
Needs to look like this:

Item (Column A) Pages (Column B)
Give Kids a Smile 9
Concentrix Handpieces New! 468
430-Series Handpieces 468
BURS Specialty 52, 54_55, 70_71
BURS 49_127
BURS Carbide 49_79


It needs to first evaluate the 'Delete' field (Check Box) and ignore any records with a check.
It then needs to evaluate the 'New_Page #' field and ignore any blank records.
It then needs to evaluate where the 'New_Page #" field is blank (or null) and the 'Delete' field is ALSO blank and
if that scenario is true, then a 'Msg Box' should appear warning the user "Files Not Complete, Do you want to proceed?"
(FYI - I will have a report for them to view those instances outside of this process)
If they choose to proceed then:


Basically it needs to take all records for given 'Item' and truncate the data in the 'New_Page #' field and
comma seperate them.


Spreadsheet #2:
Needs to look like this:

Section (Column A) Page Numbers (Column B)
COVER, STORY, & INTRO 1-17
Total Health 18-24
Acrylics 25-30
Alloys 31-35
Anesthetics 36-44
Articulating 45-48
Burs 49-127
CAD/CAM 128-138
Cements & Liners 139-159


The evaluation on this one is a little tricker as the related fields are text as opposed to a number.
It basically needs to search the 'New_Page #" field for the smallest number (thought text...as some records could have underscores)
and search for the largest number and add the "-" (Hyphen) between them.


This process also needs to ignore any records with a check in the 'Delete' column.
This process should also ignore any records with a blank (or null) in the 'New_Page #' field.
Below is a Sample of what the table 'Main' looks like:

HELP!
I hope I gave enough info but if I missed anything please ask and I will gladly answer and I appreciate all the help!

Sample of what table "Main" looks like:

Section Page # Item New_Page # Page_Sort Delete
COVER, STORY, & INTRO 9 Give Kids a Smile 10 9 0
COVER, STORY, & INTRO 12_15 What’s NEW! 12 -1
Total Health 18_24 TOTAL HEALTH New! 18 0
Total Health 19 VELscope Vx 19 0
Total Health 20_22 Sleep Complete New! 20 0
Total Health 23 Microlux DL 23 0
Total Health 23 DNA Testing 23 0
Total Health 23 Salivary DNA Tests 23 0
Total Health 24 OralDNA 24 0
Total Health 24 OraRisk HPV Salivary DNA Test 24 0
Total Health 24 MyPerioPath Salivary DNA Test 24 0
Acrylics 25 Coe Tray Plastic 25 0
Acrylics 25 Fastray 25 0
Acrylics 25 Sapphire Impression Material 25 0
Acrylics 25 Easy Tray 25 0
Acrylics 25_30 ACRYLICS & RELINE MATERIALS 25 0
Acrylics 25 Rimseal 25 0
Acrylics 25 Hydroplastic 25 0
Acrylics 25 Jet_Tray 25 0
Acrylics 26 Hydro_Cast 26 0
Acrylics 26 Paladon Ultra New! 26 0
Acrylics 26 Tissue Conditioner 26 0
Acrylics 27_28 Hard Reline Materials 27 0
Acrylics 27 Hygenic Perm 27 0
Acrylics 27 Chairside Reline Material 27 0
Acrylics 27 Ufi Gel Hard C 27 0
Acrylics 27 Coe Rect 27 0
Acrylics 27_29 Reline Materials 27 0
Acrylics 28 Dentusil Denture Reline 28 0
Acrylics 28 Silk Line 28 0
Acrylics 28_29 Soft Reline Materials 28 0
Acrylics 28 Truliner 28 0
Acrylics 29 Sofreliner 29 0
Acrylics 29 Acraweld Repair Material 29 0
Acrylics 29 Z_Bur 29 0
Acrylics 29 Versa_Soft 29 0
Acrylics 29_30 Repair Materials 29 0
Acrylics 29 Trusoft 29 0
Acrylics 29_30 Denture Repair Materials 29 0
Acrylics 30 DuraLay 30 0
Acrylics 30 Dura Seal 30 0
Burs 49_127 BURS 53_128 49 0
Burs 52 BURS Specialty 50 52 0
Burs 54_55 BURS Specialty 68_78 54 0
Burs 70_71 BURS Specialty 88_98 70 0
Equipment- Small 368 Microetcher 368 -1