Export table to excel file

[Anticephalous]

Hi guys, could anyone help me please. I got this code that exports table to excel file

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\List of Companies" & Format(date, "dd-MMM-yyyy") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Companies", outputFileName, True

it exports but the problem is that the field Sector, which is a dropdown table/query, instead of exporting what was selected, it gives me characters.

 

[namliam]

Yes obviously it will give you the characters of what was selected.

This is one of the many pitfalls of using lookup fields in tables, tables should be clean lookup fields/combo boxes belong on forms.

You will have to make a query and add the field you want, subsequently export the query as you would the table.

 

[Anticephalous]

Sorry, when I said, characters, i meant symbols, like in words, insert symbols? like that symbols...

 

[namliam]

sorry dont understand you...

Are you indeed using lookup fields?

 

[Anticephalous]

yes lookup fields...i don't understand why it has happened. Access has that button to export access table or query to excel, and the export is perfectly fine. The selected sector name is exported not some symbols. Help me understand this please.

 

[Minty]

Lookup Fields don't export or do a ton of other things properly , which is why you shouldn't use them - exactly as namliam pointed out.

Remove the lookup from the table, and store what you meant to store.

 

[namliam]

Some symbols, like what symbols?

What kind of lookup, did you enter values or is it referencing another table?

 

[Anticephalous]

Oh I got it now... when i tried exporting the query it was exported fine. Thank you namliam!!

 

[namliam]

Now get to removing all lookup fields at table level !