Exporting a column's format to Excel

G

Guest

I have a query that I export (transferspreadsheet) to Excel. It has a column
explicity defined (using the property sheet for the column, in the query
itself) as currency, 4 decimal positions. That's also how the column is
defined on the table the query references.

In Excel, the column shows up formatted as currency, 2 decimal positions.

In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")

I figured that would do the same thing as I was doing by using the property
sheet, but I tried it anyway. It did. Same result. 2 decimal places, not 4.

However, the data was not truncated. For example, $12.1234 shows as $12.12.
But when I reformat the column from within Excel itself, the unrounded data
pops out, and I see $12.1234.

Any ideas on how to override Excel's default for currency fields of two
decimal places?
 
J

John Nurick

I think you'll need to modify the cell format in Excel after the export,
either manually or by writing VBA code that automates Excel from Access.

I have a query that I export (transferspreadsheet) to Excel. It has a column
explicity defined (using the property sheet for the column, in the query
itself) as currency, 4 decimal positions. That's also how the column is
defined on the table the query references.

In Excel, the column shows up formatted as currency, 2 decimal positions.

In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")

I figured that would do the same thing as I was doing by using the property
sheet, but I tried it anyway. It did. Same result. 2 decimal places, not 4.

However, the data was not truncated. For example, $12.1234 shows as $12.12.
But when I reformat the column from within Excel itself, the unrounded data
pops out, and I see $12.1234.

Any ideas on how to override Excel's default for currency fields of two
decimal places?
Click to expand...
 
G

Guest

Thanks, John.

I decided to forget about TransferSpreadsheet, and go straight to
CopyFromRecordSet. This solved my four decimal places problem, but the column
is still a general number, not a currency.

As you can see from the code below, I am setting a few properties on the
spreadsheet, but I don't know how to set a column to currency, or set its
horizontal alignment. Can you provide an example of each?

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open(strExcelToUse)

Set rstX =
Application.CurrentDb.OpenRecordset("qryrptTradesByBk-Stage2-forExport")
rstX.MoveLast
rstX.MoveFirst
intRowCount = rstX.RecordCount
intFieldCount = rstX.Fields.Count

Set xlSheet = xlWorkbook.Sheets(1)
For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Value = rstX.Fields(intColNbr).Name
Next

For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Font.Bold = True
Next

With xlSheet
.Range("A3").CopyFromRecordset rstX
.Name = "Trades"
End With

xlSheet.Columns("A:A").ColumnWidth = 10
xlSheet.Columns("B:B").ColumnWidth = 10
xlSheet.Columns("C:C").ColumnWidth = 12
xlSheet.Columns("D:D").ColumnWidth = 12
xlSheet.Columns("E:E").ColumnWidth = 25
xlSheet.Columns("F:F").ColumnWidth = 10
xlSheet.Columns("G:G").ColumnWidth = 12

' For intRowNbr = 2 To intRowCount + 1
' xlSheet.Cells(intRowNbr, 7).NumberFormat = "currency"
' Next

xlSheet.Columns("H:H").ColumnWidth = 25
xlSheet.Columns("I:I").ColumnWidth = 10
xlSheet.Columns("J:J").ColumnWidth = 12
xlSheet.Columns("K:K").ColumnWidth = 25
xlSheet.Columns("L:L").ColumnWidth = 10
xlSheet.Columns("M:M").ColumnWidth = 12
xlSheet.Columns("N:N").ColumnWidth = 10
xlSheet.Columns("O:O").ColumnWidth = 10
xlSheet.Columns("P:p").ColumnWidth = 10
xlSheet.Columns("Q:Q").ColumnWidth = 8
xlSheet.Columns("R:R").ColumnWidth = 9
xlSheet.Columns("S:S").ColumnWidth = 25
xlSheet.Columns("T:T").ColumnWidth = 20
xlSheet.Columns("U:U").ColumnWidth = 20
xlSheet.Columns("V:V").ColumnWidth = 50

xlSheet.Cells(1, 1).Value = " "
xlSheet.Cells(1, 3).Value = Date

' With xlSheet
' .Columns.Cells("A:A").HorizontalAlignment = "center"
' End With


John Nurick said:
I think you'll need to modify the cell format in Excel after the export,
either manually or by writing VBA code that automates Excel from Access.

I have a query that I export (transferspreadsheet) to Excel. It has a column
explicity defined (using the property sheet for the column, in the query
itself) as currency, 4 decimal positions. That's also how the column is
defined on the table the query references.

In Excel, the column shows up formatted as currency, 2 decimal positions.

In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")

I figured that would do the same thing as I was doing by using the property
sheet, but I tried it anyway. It did. Same result. 2 decimal places, not 4.

However, the data was not truncated. For example, $12.1234 shows as $12.12.
But when I reformat the column from within Excel itself, the unrounded data
pops out, and I see $12.1234.

Any ideas on how to override Excel's default for currency fields of two
decimal places?
Click to expand...
Click to expand...
 
J

John Nurick

Numeric formats are done by setting an Excel Range's NumberFormat
property, e.g.

"_(£#,##0.0000_);(£#,##0.0000)"

Horizontal alignment I can't remember, but you can look it up in Excel
VBA help or search in the Object Browser.

Thanks, John.

I decided to forget about TransferSpreadsheet, and go straight to
CopyFromRecordSet. This solved my four decimal places problem, but the column
is still a general number, not a currency.

As you can see from the code below, I am setting a few properties on the
spreadsheet, but I don't know how to set a column to currency, or set its
horizontal alignment. Can you provide an example of each?

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open(strExcelToUse)

Set rstX =
Application.CurrentDb.OpenRecordset("qryrptTradesByBk-Stage2-forExport")
rstX.MoveLast
rstX.MoveFirst
intRowCount = rstX.RecordCount
intFieldCount = rstX.Fields.Count

Set xlSheet = xlWorkbook.Sheets(1)
For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Value = rstX.Fields(intColNbr).Name
Next

For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Font.Bold = True
Next

With xlSheet
.Range("A3").CopyFromRecordset rstX
.Name = "Trades"
End With

xlSheet.Columns("A:A").ColumnWidth = 10
xlSheet.Columns("B:B").ColumnWidth = 10
xlSheet.Columns("C:C").ColumnWidth = 12
xlSheet.Columns("D:D").ColumnWidth = 12
xlSheet.Columns("E:E").ColumnWidth = 25
xlSheet.Columns("F:F").ColumnWidth = 10
xlSheet.Columns("G:G").ColumnWidth = 12

' For intRowNbr = 2 To intRowCount + 1
' xlSheet.Cells(intRowNbr, 7).NumberFormat = "currency"
' Next

xlSheet.Columns("H:H").ColumnWidth = 25
xlSheet.Columns("I:I").ColumnWidth = 10
xlSheet.Columns("J:J").ColumnWidth = 12
xlSheet.Columns("K:K").ColumnWidth = 25
xlSheet.Columns("L:L").ColumnWidth = 10
xlSheet.Columns("M:M").ColumnWidth = 12
xlSheet.Columns("N:N").ColumnWidth = 10
xlSheet.Columns("O:O").ColumnWidth = 10
xlSheet.Columns("P:p").ColumnWidth = 10
xlSheet.Columns("Q:Q").ColumnWidth = 8
xlSheet.Columns("R:R").ColumnWidth = 9
xlSheet.Columns("S:S").ColumnWidth = 25
xlSheet.Columns("T:T").ColumnWidth = 20
xlSheet.Columns("U:U").ColumnWidth = 20
xlSheet.Columns("V:V").ColumnWidth = 50

xlSheet.Cells(1, 1).Value = " "
xlSheet.Cells(1, 3).Value = Date

' With xlSheet
' .Columns.Cells("A:A").HorizontalAlignment = "center"
' End With


John Nurick said:
I think you'll need to modify the cell format in Excel after the export,
either manually or by writing VBA code that automates Excel from Access.

I have a query that I export (transferspreadsheet) to Excel. It has a column
explicity defined (using the property sheet for the column, in the query
itself) as currency, 4 decimal positions. That's also how the column is
defined on the table the query references.

In Excel, the column shows up formatted as currency, 2 decimal positions.

In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")

I figured that would do the same thing as I was doing by using the property
sheet, but I tried it anyway. It did. Same result. 2 decimal places, not 4.

However, the data was not truncated. For example, $12.1234 shows as $12.12.
But when I reformat the column from within Excel itself, the unrounded data
pops out, and I see $12.1234.

Any ideas on how to override Excel's default for currency fields of two
decimal places?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

got it. thanks.

John Nurick said:
Numeric formats are done by setting an Excel Range's NumberFormat
property, e.g.

"_(£#,##0.0000_);(£#,##0.0000)"

Horizontal alignment I can't remember, but you can look it up in Excel
VBA help or search in the Object Browser.

Thanks, John.

I decided to forget about TransferSpreadsheet, and go straight to
CopyFromRecordSet. This solved my four decimal places problem, but the column
is still a general number, not a currency.

As you can see from the code below, I am setting a few properties on the
spreadsheet, but I don't know how to set a column to currency, or set its
horizontal alignment. Can you provide an example of each?

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open(strExcelToUse)

Set rstX =
Application.CurrentDb.OpenRecordset("qryrptTradesByBk-Stage2-forExport")
rstX.MoveLast
rstX.MoveFirst
intRowCount = rstX.RecordCount
intFieldCount = rstX.Fields.Count

Set xlSheet = xlWorkbook.Sheets(1)
For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Value = rstX.Fields(intColNbr).Name
Next

For intColNbr = 0 To intFieldCount - 1
xlSheet.Cells(2, intColNbr + 1).Font.Bold = True
Next

With xlSheet
.Range("A3").CopyFromRecordset rstX
.Name = "Trades"
End With

xlSheet.Columns("A:A").ColumnWidth = 10
xlSheet.Columns("B:B").ColumnWidth = 10
xlSheet.Columns("C:C").ColumnWidth = 12
xlSheet.Columns("D:D").ColumnWidth = 12
xlSheet.Columns("E:E").ColumnWidth = 25
xlSheet.Columns("F:F").ColumnWidth = 10
xlSheet.Columns("G:G").ColumnWidth = 12

' For intRowNbr = 2 To intRowCount + 1
' xlSheet.Cells(intRowNbr, 7).NumberFormat = "currency"
' Next

xlSheet.Columns("H:H").ColumnWidth = 25
xlSheet.Columns("I:I").ColumnWidth = 10
xlSheet.Columns("J:J").ColumnWidth = 12
xlSheet.Columns("K:K").ColumnWidth = 25
xlSheet.Columns("L:L").ColumnWidth = 10
xlSheet.Columns("M:M").ColumnWidth = 12
xlSheet.Columns("N:N").ColumnWidth = 10
xlSheet.Columns("O:O").ColumnWidth = 10
xlSheet.Columns("P:p").ColumnWidth = 10
xlSheet.Columns("Q:Q").ColumnWidth = 8
xlSheet.Columns("R:R").ColumnWidth = 9
xlSheet.Columns("S:S").ColumnWidth = 25
xlSheet.Columns("T:T").ColumnWidth = 20
xlSheet.Columns("U:U").ColumnWidth = 20
xlSheet.Columns("V:V").ColumnWidth = 50

xlSheet.Cells(1, 1).Value = " "
xlSheet.Cells(1, 3).Value = Date

' With xlSheet
' .Columns.Cells("A:A").HorizontalAlignment = "center"
' End With


John Nurick said:
I think you'll need to modify the cell format in Excel after the export,
either manually or by writing VBA code that automates Excel from Access.

On Wed, 15 Jun 2005 09:01:05 -0700, "tmj00345"

I have a query that I export (transferspreadsheet) to Excel. It has a column
explicity defined (using the property sheet for the column, in the query
itself) as currency, 4 decimal positions. That's also how the column is
defined on the table the query references.

In Excel, the column shows up formatted as currency, 2 decimal positions.

In another post, I noticed Ken Snell recommending the following to preserve
formatting when exporting a time column to Excel:

MyTimeField: Format([TimeFieldName], "hh:nn")

I figured that would do the same thing as I was doing by using the property
sheet, but I tried it anyway. It did. Same result. 2 decimal places, not 4.

However, the data was not truncated. For example, $12.1234 shows as $12.12.
But when I reformat the column from within Excel itself, the unrounded data
pops out, and I see $12.1234.

Any ideas on how to override Excel's default for currency fields of two
decimal places?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formating Excel Cells 2
Access stripping decimal places in query 1
exporting as text cutting off significant digits 2
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
Text columns exporting to currency columns 5
Access to Excel currency field loses decimal formatting 4
exporting to text file 1
Access Export of data to Excel using VBA - data looks awful 4

Top