Analyze with Excel and number format

[MelB]

I have a table which contains data from seven plants. I have a form that filters the data to their plant when they open it from a button. I have a custom menu with 'filter by form', 'toggle filter', and 'analyze with excel' to allow them to further filter their records then export them to Excel which is the product they prefer for graphing and analysis.

Here is the problem... in Access all the fields in the table are defined as numeric, single, fixed with the decimal places set to either 1, 2, or 3. They display correctly on the form but when they are exported to Excel, through the analyze button, all the numbers show up at fixed with two decimal places. They want it formated with the same decimal places as it is in Access.

Does anyone have any idea how to fix this... keeping it simple... Thanks in advance.

 

[RobertQ]

Hi,

for my opinion the only way to fix this to create your own export function by opening a new Excel object and than import the current data. This will also allow you to store the imformation in your file system at a place you want and not the one on c: drive which Excel prefers.

Kind regards

 

[The_Doc_Man]

I think I have to agree with Robert Q on this one.

The problem is that the Access Export Wizard for Excel is rather dumb when it comes to formatting.

The only way I would know to approach this is to export the spreadsheet using the default export functions. Then open the workbook from VBA as an application object, go back in to find the columns, and assert formats for the correct number of decimal places. If you are a novice with VBA, that is not a comfortable solution.

 

[MelB]

to quote you Doc_Man... 'that is not a comfortable solution'. Any other thoughts anyone?

 

[RobertQ]

Hi,

another way to solve your problem: create a button on your form and connect a macro to "OnClick".
In the macro you select as "action" the "OutPutTo". Here you are able to define an object type "query" and the object name "query xyz". The ouputformat you can set to Micosoft Excel (*.xls). The outputfile you can set to a path and a file name you want. As an option you can set the autostart to "yes".

I use such an export function in my db, were I have nominal amounts with different decimal values and it works.

The only thing the user has to do is to click on the button. The thing I can't tell you is how to remove function "analyze to Excel" in the action bar.

Kind regards