B
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
********************************
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
********************************