Export to Excel from Access with dropdown using VBA

[REBBROWN]

Hey guys,


hoping someone can help me here.
got a DB dumped in my lap and a TON of VB code, and told to ‘replicate’ which so far has not happened, both because of cryptic code and my knowledge level of VBA.

1) need to export to Excel data from a table.

Here is the SQL for it:
ASM_SQL = "SELECT [ASM Translation Table All].CERTIFICATION, " & _
"[ASM Translation Table All].[Certification Translation], " & _
"[ASM Translation Table All].[Qual Type], " & _
"[ASM Translation Table All].TMS, " & _
"[ASM Translation Table All].[End Month ID] AS SQD " & _
"FROM [ASM Translation Table All];"

2) here is the export to Excel


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyExportQuery", "" & LOCATIONFILE & " " & SQDName & ".xls", False
DoCmd.DeleteObject acQuery, "MyExportQuery"

3) a little more code to shed some ‘light’ on above transfer statement

Set ASMSQL = DB.OpenRecordset(ASM_SQL)
Set strExcelExport = DB.CreateQueryDef("MyExportQuery", ASM_SQL)
If ASMSQL.EOF Then
intExportListCount = 0
Else
ASMSQL.MoveLast
End If
intExportListCount = ASMSQL.RecordCount
intCounter = 1
ASMSQL.MoveFirst
ExportFileName = "" & FILELOC & " " & SQDName & ".xls"
OverrideFile = True

Ok, this works fine, exports to Excel, no problems so I know this works but it’s what the powers that be want to follow once it arrives in Excel.
They want Certification Translation, (2^nd line in SQL statement above) to be in a drop down (or combo box) depending on how you refer to it.
So 4 or 5 fields or columns populated in the exported Excel, but 2^nd one to create programmatically a drop down and populated by the exported values from that field in the SQL.
Is there a quick and easy way to do this, essentially a ‘cut and paste’ just to get it to work in case they decide they want another drop down from remaining fields?
Any and ALL help will be greatly appreciated.

[June7]

You want to export records to a new spreadsheet and then set up a dropdown for one or more of the columns?

This requires opening the Excel as a VBA object and manipulating the spreadsheets to create the lookup reference range and the dropdown properties. Will be very complicated code.

Have you at least learned how to manually set lookup in Excel to have basic frame of reference?

Why Excel and not viewing data in forms and reports?