Exporting a column's format to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
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...
 
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...
 
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...
 
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...
 
Back
Top