Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default MSAccess output to Excel losing formatting of number

I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples:
100.00
0.00
35.42

When I use msAccess' OutputTo Excel feature, the amount column looks like
this:
100
0
35.42

What am I doing wrong? I don't want the users to have to do additional work
when it outputs to Excel. How can I make it output from msAccess properly?

Thanks in advance.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,836
Default MSAccess output to Excel losing formatting of number

Sounds like you are creating the Excel sheet on the fly. If there is o
formatting, then excel will just use a default format, such as General, which
truncates zeros on the right of numbers (excel thinks they are irrelevant).
You will have to control the formatting process from Access. Use this macro
(in Access):

Option Compare Database



Option Explicit ' Use this to make sure your variables are defined



' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub



Private objExcel As Excel.Application

Private xlWB As Excel.Workbook

Private xlWS As Excel.Worksheet





Sub Rep()



Dim strFile As String



strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls"



€˜Of course, this is just an example; put the actual path to your actual file
here€¦

' Opens Excel and makes it Visible



Set objExcel = New Excel.Application



objExcel.Visible = True



' Opens up a Workbook



Set xlWB = objExcel.Workbooks.Open(strFile)







' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.



Set xlWS = xlWB.ActiveSheet



' Set xlWS = xlWB("Sheet1")





With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here...do the formatting here...you can even record
a macro and make the process super easy!!



End With



' Close and Cleanup



xlWB.SaveAs xlSaveFile

xlWB.Close



xlapp.Quit

Set xlapp = Nothing



End Sub

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"slickdock" wrote:

I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples:
100.00
0.00
35.42

When I use msAccess' OutputTo Excel feature, the amount column looks like
this:
100
0
35.42

What am I doing wrong? I don't want the users to have to do additional work
when it outputs to Excel. How can I make it output from msAccess properly?

Thanks in advance.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Losing all formatting in Excel 2007 files stumped Al Excel Discussion (Misc queries) 1 February 26th 10 05:18 AM
macro to save current excel sheet only without losing formatting Genesis Excel Programming 2 June 22nd 07 04:31 PM
Excel columns keep losing their formatting in 2007 Beta upgrade w. McNaddy Excel Discussion (Misc queries) 2 October 24th 06 01:41 PM
Run An MSAccess VBA Function From Excel Frank Excel Programming 1 April 1st 05 01:51 PM
pasting into an excel template without losing the formatting chachi Excel Discussion (Misc queries) 2 January 14th 05 05:08 AM


All times are GMT +1. The time now is 04:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"