Export Access table with Lookup column to Excel

[65goatman]

Good day,
Using Access, I break a raw data file into separate tables based on the value in a field, (Customer Name). I then export the table to Excel where I email the worksheets for the completion of fields that I added to the raw data file. I want to limit the responses that can be entered in a field in the worksheet, so when I re-build the raw file I have the restricted responses included. I can restrict entry via the lookup function options within Access fine. When I try to export this to Excel, the restriction and available input options, data validation, are not available, and thus the restrictions are not found in the Excel workbook. I can manually go back and add them to the worksheet(s) through the data validation options once the export has been completed.

I would like to have the restrictions and the acceptable responses exported with the table(s)when I export from Access, thus eliminating the manual data validation step, prior to emailing the worksheet. Is this possible?

Using Office 2007 Professional...

 

[SkipVought]

Hi,

I'm an Excel guy, so I'm looking at this issue from Excel, not Access.

I envision a workbook that has all the sheets an formatting and data validation set up. Each sheet IMPORTS a single customer from Access. MS Query can simply be used to get whatever data from MS Access on demand any time.

Problem solved!

 

[65goatman]

Thank you for the suggestion.... Unfortunately, that solution caused additional time by manually creating the from the Excel side. I tested that solution, but when I copied the cells from Access, it changed the format of the Excel column. This then added steps to go back and correct. Again, I need to be able to export from Access and have the worksheet respect the formatting that was assigned while in the access environment.

 

[SkipVought]

Creating the Excel workbook is a ONE TIME exersize. The formats, created ONE TIME remain week after week; month after month!

NOTHING is "copied" from Access!

The data form Access is IMPORTED via a query on each sheet, as I'm assuming that you want one sheet per customer. Actually, I'd use one sheet with a ComboBox control to query one customer at a time and spin off an output for each customer. You would need a modest macro to preform the repeated task.

 

[combo]

Simple export to excel generates formatted plain table without any limitations.
Without macros on the excel side you could process the raw workbook using vba automation. The idea:
1. export two (or more) queries into the same workbook, first for data, next ones with support tables for validation,
2. open workbook via vba, add validation to first worksheet using other sheets, hide or delete (if data for validation was converted to static lists) support worksheets, save.
Data validation in excel is not strict, it is not case sensitive and it is possible to paste invalid data.

combo