Trouble with Export Feature in Access

[lorenda]

I have a form whose fields are calculated based off VBA code, including fuctions, SQL, etc. Because of the nature of my form, I cannot save the calculations to a table before exporting to and excel spreadsheet.

When I use the File>Export feature of Access and save to and Excel format, I am not able to change the format of my numbers in Excel. For example, I have tried to highlight some of my exported numbers, right-click, and change the format to currency in Excel (or even decimal places). In order for excel to change the format, I have to select the cell, place my cursor in the edit window, and hit enter.....then the format will change.....is there any way to avoid this?

Please let me know if this isn't clear, and I'll attempt to explain it better.

 

[boblarson]

You could always use the Excel COM Objects to create the Excel spreadsheet and then save things to it via code and modify the properties of the cells via code. That's a more complex way of doing it, but would totally put everything under your control.

 

[Bat17]

View the form in datasheet view and copy all of the data that way then paste special into Excel using Biff5 usualy works.

HTH

Peter

 

[lorenda]

Went the easy way for now....I'm a definite VB beginner so even something as simple as formatting tends to take years.....I appreciate your help though.

But, FYI, Copying from datasheet view worked so long as I pasted as Unicode Text, not Biff.

Will definitely get started on the programming though.

 

[Bat17]

Seems to vary from machine to machine, some work with a direct paste others Biff or unicode. Sometimes dates still end up as text

Peter

 

[ghudson]

Check this thread out once you are ready to do format your Excel file from Access using code [VBA]...
hiding excel manipulations and showing a progress bar