make exported excel field uppercase

[B. Meincke]

Hi all,
I hope this is the right forum for this question. If not, I apologize...

I have an Access 2007 table from which I export data to Excel in '97 - '03
format. Data entry is not always done consistantly.

At the invaluable suggestions from previous posts, I have installed the
Excel 12 object library and successfully copied all other necessary worksheet
formatting from a recorded Excel macro to the Access VBA code.

What I would like is also to code the Access form button that initiates the
export so that the resultant Excel worksheet column F field data is converted
to uppercase.

Is this possible, and if so, how?

Thanks for any advice or suggestions...

If it helps, here is the code for the button as it is stands at the moment:

**********************************
Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Dim strFile As String
Dim strDest As String

Dim strDate As String

Dim strMyDB As String

' Load variables
strMyDB = CurrentDb.Name
strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
strDate = Format(Date, "yyyy-mm-dd")
strFile = strPath & "Open House List (" & strDate & ").xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblOHList", strFile, True

Set objExcel = New Excel.Application

objExcel.Visible = True

Set xlWB = objExcel.Workbooks.Open(strFile)

Set xlWS = xlWB.ActiveSheet

With xlWS

.Range("A1:R1").Font.Bold = True
.Range("A1:R1").HorizontalAlignment = xlCenter
.Range("A1:R1").Font.ColorIndex = 2
.Range("A1:R1").Interior.ColorIndex = 1

.Range("A:R").HorizontalAlignment = xlLeft
.Range("E:E").NumberFormat = "$#,##0.00"
.Range("E:E").HorizontalAlignment = xlRight

.Range("A:R").Columns.AutoFit

.Range("2:2").Select
End With

With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With

ActiveWindow.FreezePanes = True

xlWS.Range("A1:A1").Select

Set objExcel = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

End Sub
********************************

 

[RonaldoOneNil]

Instead of exporting tblOHList, create a query based on tblOHList and you can
uppercase your column in there. Export this query instead of the table.

 

[B. Meincke]

Of course. Duh!

<blush>