Solved

Format excel when exporting from access

Posted on 2014-03-16
6
1,152 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
  • 4
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 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 25

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 50

Accepted Solution

by:
Rgonzo1971 earned 500 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 25

Author Comment

by:-MAS
ID: 39933672
Many thanks. It worked
0
 
LVL 25

Author Closing Comment

by:-MAS
ID: 39933676
Many thanks. It worked
0
 
LVL 25

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 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