?
Solved

Format excel when exporting from access

Posted on 2014-03-16
6
Medium Priority
?
1,170 Views
Last Modified: 2014-03-18
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(outputFileName, "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.Application")
Set wbk = xlf.Workbooks.Open(fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\" & 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
0
Comment
Question by:☠MAS☠
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 2000 total points
ID: 39933655
Hi,

pls try

With ActiveWindow
    .SplitRow = 1
    .FreezePanes = True
End With
    
With ActiveSheet
    If .AutoFilterMode Then
        If .AutoFilter.FilterMode Then
            .AutoFilter.ShowAllData
        End If
    Else
        .Cells.AutoFilter
    End If
    .Cells.EntireColumn.AutoFit
End With

Open in new window

Regards
0
 
LVL 27

Author Comment

by:☠MAS☠
ID: 39933663
Where I have to apply this.

Within the function ?
if yes where? . I am not good in VB
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39933669
pls try

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.Application")
Set wbk = xlf.Workbooks.Open(fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\" & xls)
Set wks = wbk.Sheets(sheet)
With xlf.ActiveWindow
    .SplitRow = 1
    .FreezePanes = True
End With
    
With wks
    If .AutoFilterMode Then
        If .AutoFilter.FilterMode Then
            .AutoFilter.ShowAllData
        End If
    Else
        .Cells.AutoFilter
    End If
    .Cells.EntireColumn.AutoFit
End With 

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 

Open in new window

0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 27

Author Comment

by:☠MAS☠
ID: 39933672
Many thanks. It worked
0
 
LVL 27

Author Closing Comment

by:☠MAS☠
ID: 39933676
Many thanks. It worked
0
 
LVL 27

Author Comment

by:☠MAS☠
ID: 39937144
Appreciate if you can send the code to bold the heading. Not urgent reply when you are free
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question