M A
asked on
Format excel when exporting from access
I have a code to highlight a filed. below is the code.
I would like to enable filter, freeze the first row and autofit when exporting.
How to accomplish this?
Your help appreciated.
Private Sub Command48_Click()
Dim outputFileName As String
If Me.Dirty Then Me.Dirty = False
outputFileName = "Export_" & Format(Date, "dd-MM-yyyy") & ".xls"
DoCmd.OutputTo acOutputQuery, "Payment-Details", acFormatXLS, outputFileName
Call excel_format(outputFileNam e, "Payment-Details")
End Sub
Public Function excel_format(xls As String, sheet As String) As Long
Dim xlf As Object, wbk As Object, wks As Object
Dim i As Long, Lr As Long
Set xlf = CreateObject("Excel.Applic ation")
Set wbk = xlf.Workbooks.Open(fGetSpe cialFolder Location(C SIDL_PERSO NAL) & "\" & xls)
Set wks = wbk.Sheets(sheet)
Lr = wks.UsedRange.Rows.Count
For i = 2 To Lr
If wks.cells(i, 35).value = "Low" Then wks.cells(i, 11).Interior.Color = vbGreen
If wks.cells(i, 35).value = "Medium" Then wks.cells(i, 11).Interior.Color = vbYellow
If wks.cells(i, 35).value = "High" Then wks.cells(i, 11).Interior.Color = vbRed
Next i
wbk.Save
xlf.Application.Visible = True ' If you like to show workbook
'wbk.close
'xlf.Quit ' quit excel
Set xlf = Nothing
Set wbk = Nothing
Set wks = Nothing
End function
I would like to enable filter, freeze the first row and autofit when exporting.
How to accomplish this?
Your help appreciated.
Private Sub Command48_Click()
Dim outputFileName As String
If Me.Dirty Then Me.Dirty = False
outputFileName = "Export_" & Format(Date, "dd-MM-yyyy") & ".xls"
DoCmd.OutputTo acOutputQuery, "Payment-Details", acFormatXLS, outputFileName
Call excel_format(outputFileNam
End Sub
Public Function excel_format(xls As String, sheet As String) As Long
Dim xlf As Object, wbk As Object, wks As Object
Dim i As Long, Lr As Long
Set xlf = CreateObject("Excel.Applic
Set wbk = xlf.Workbooks.Open(fGetSpe
Set wks = wbk.Sheets(sheet)
Lr = wks.UsedRange.Rows.Count
For i = 2 To Lr
If wks.cells(i, 35).value = "Low" Then wks.cells(i, 11).Interior.Color = vbGreen
If wks.cells(i, 35).value = "Medium" Then wks.cells(i, 11).Interior.Color = vbYellow
If wks.cells(i, 35).value = "High" Then wks.cells(i, 11).Interior.Color = vbRed
Next i
wbk.Save
xlf.Application.Visible = True ' If you like to show workbook
'wbk.close
'xlf.Quit ' quit excel
Set xlf = Nothing
Set wbk = Nothing
Set wks = Nothing
End function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks. It worked
ASKER
Many thanks. It worked
ASKER
Appreciate if you can send the code to bold the heading. Not urgent reply when you are free
ASKER
Within the function ?
if yes where? . I am not good in VB