Access to excel formatting (1 Viewer)

Jovial

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2015
Messages
29
Hello,
I have a query in access which i export to excel via 'Output to' method and result look like this:
View attachment 61659
But I want the result like:
View attachment 61660

I want to create the function (modifyExport) in access vba which will be called after exporting the file and format the excel file
I am not very good at VBA, still learning. With the online help, I am able to export the file but could not figure out how to do this?
Please give me some suggestions.

Thanks,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:24
Joined
May 7, 2009
Messages
19,169
create a macro on the export worksheet that format your worksheet. this will be our basis on building the code in access. post the code of the macro/macros so that anybody can help you with the code.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 18:24
Joined
Oct 17, 2012
Messages
3,276
Personally, I generally use either TransferSpreadsheet or CopyFromRecordset, depending on the specifics of what I'm doing, and then use automation to apply the needed formatting. All of that can be done from Access.

Basically, you go to Tools - References, check the reference for the Microsoft xx Object Library (xx will be a number based on your version of office), and you can create Excel objects that allow you to use excel methods and functions.

One example I have sitting around that uses a number of different methods you might be interested in:

Code:
Public Sub FormatWorksheet(ByVal TargetPath As String, _
                           ByVal SheetName As String)
                           
[COLOR="SeaGreen"]'Note that this procedure requires that the XLS file being modified not be in use
'Note that this procedure does not actually check to see if the XLS file is in use.[/COLOR]

[COLOR="seagreen"]'Variable declaration[/COLOR]
Dim wb                      As Excel.Workbook[COLOR="seagreen"]       'Workbook being modified[/COLOR]
Dim ws                      As Excel.Worksheet[COLOR="seagreen"]      'Worksheet being modified[/COLOR]
Dim LastColumn              As Integer[COLOR="seagreen"]              'Last column used in the indicated worksheet[/COLOR]
Dim ProcName                As String[COLOR="seagreen"]               'Name of the procedure being run[/COLOR]

On Error GoTo FormatWorksheet_Err

[COLOR="seagreen"]    'Assign default values.[/COLOR]
    ProcName = "FormatWorksheet"
    
[COLOR="seagreen"]    'Assign to wb and open the indicated workbook.[/COLOR]
    Set wb = Excel.Workbooks.Open(TargetPath)
    
[COLOR="seagreen"]    'Assign to ws the indicated sheet.[/COLOR]
    Set ws = wb.Sheets(SheetName)
    
[COLOR="seagreen"]    'Determine the last used column.[/COLOR]
    LastColumn = ws.UsedRange.Columns.Count
    
[COLOR="seagreen"]    'Set the first row in the assigned range to grey.[/COLOR]
    ws.Range(Cells(1, 1), Cells(1, LastColumn)).Interior.ColorIndex = 15
    
[COLOR="seagreen"]    'Make the font in the first row bold.[/COLOR]
    ws.Range(Cells(1, 1), Cells(1, LastColumn)).Font.Bold = True
    
[COLOR="seagreen"]    'Outline every cell in the used area.[/COLOR]
    ws.UsedRange.Cells.Borders.LineStyle = xlContinuous
    ws.UsedRange.Cells.Borders.Weight = xlThin
    ws.UsedRange.Cells.Borders.Color = 1
    
[COLOR="seagreen"]    'Resize all columns in TargetRange.[/COLOR]
    ws.UsedRange.Columns.AutoFit
    
FormatWorksheet_Exit:
    If Not ws Is Nothing Then Set ws = Nothing
    If Not wb Is Nothing Then
        wb.Save
        wb.Close
        Set wb = Nothing
    End If
    Exit Sub
    
FormatWorksheet_Err:
    MsgBox "An error was encountered during execution of procedure '" & ProcName & "'" & vbCrLf & vbCrLf & _
           "Error number:" & vbTab & Err.Number & vbCrLf & _
           "Error desc:  " & vbTab & Err.Description, vbCritical
    Resume FormatWorksheet_Exit
    
End Sub
 

marlan

Registered User.
Local time
Tomorrow, 00:24
Joined
Jan 19, 2010
Messages
409
Hi,

My advice is to go with Frothingslosh's lead. You can read some more here and here.
Based on this object model, you can also record Excel Macros for other specific actions.

ATB
 

Users who are viewing this thread

Top Bottom