Export query to Excel

[dev82]

Hey Folks,



i have a problem with my query export to excel. my code is:

Private Sub Befehl1_Click() 
    DoCmd.TransferSpreadsheet acExport, 8, "Umsatz_Netto", "C:\Abfrage.xls" _ 
                            , True, "Umsatz_Netto" 
End Sub

i set the format for numbers in the query to: #.##0;(#.##0)
but access does not get the right format the excel sheet is wrong. the format is not correct.

dou you have any ideas? how could i write the vba code?

thanks
Dev82

[thhui]

The other way round is pulling the raw data from access and then
setting it as you want in excel by using vba in excel instead.

[dev82]

Hello,

thanks for your answer. is there no other way to do that?

regards
Dev82

[NTC]

in the access table field definition - increase the decimal count. also be sure this parameter is consistent in any form controls for this field also.

you want that 0 value held in the table itself, not tagged on at the last minute via vb.

[dev82]

hello,

i have an photo from my query there is the right format.

[Rawb]

If the Query doesn't export with the proper formats, the only option I can think of is to use Office Automation.

Export the Query like normal. Then use Office Automation to open the Excel Spreadsheet and correct the format for the columns that are wrong.

[thhui]

You can call excel vba in Access.
But knowing excel vba is critical.

[thhui]

Excel automatically overrides the format access gives out.
You can test this with simple csv file with value 0 in 0.000 format

[Rawb]

Ummm. So it turns out we're all wrong. . .

The problem's in your format, not in the Query: You need to use "#.000;(#.000)" instead of "#.##0;(#.##0)".

In "#.##0;(#.##0)", you're telling Excel that it only needs to show the third digit after the decimal (12.100 will show 12.10 because the first 0 after the decimal is hidden)!

[thhui]

It stuns me after my trial in actual operation in my access.

Format under the query like "#,##0.000" works after the export to excel button is pressed.

Only csv and text files failed.

The format could be retained in the export to excel operation.

[Rawb]

lol!

I actually ran into the opposite: I created a Query in Access (and specified formats for the result Fields). Then, when I exported it to Excel, it ignored my Query format settings. It did, however, use the Table format settings!

To get around this, I had to use OLE Automation (Office Automation). To see exactly what I did, check out the attached Access Database (the VBA code is in Form1)

[thhui]

Dear Rawb,

Your access file has some interesting features:-
Not allow me to open - saying it is excluded from safe domain and must obtain my approval of protection to open it.

Also, upon opening the query, I cannot find the windows interface to have the option to change format.
I make a new query and format it accordingly and
find it is proper to export the data with format changed in the query window.

For the original table, I export it with the format option properly displayed in excel either.

I see your form and vba code.
It is using excel vba to format the data after the export.
The same approach like me stated before.

[dev82]

@ thhui,

thats right, there is something wrong with this access file, i cant open it.

[thhui]

Just right click the mdb file and there is option for properties that can force to open it by eliminating the protection.

[dev82]

Hello thhui,

thanks for the info. but the format in the sheet is wrong. as you already written in your previous message.