troubleshooting Question

Formatting Excel from Access with VBA...

Avatar of BSA2010
BSA2010 asked on
VBAMicrosoft AccessMicrosoft ExcelMicrosoft Office
5 Comments1 Solution317 ViewsLast Modified:
Here's what I am trying to accomplish:
From Access, I have vba code to copy records from a form and open Excel and paste records. The Access datasheet columns can be hidden / displayed by the user with checkboxes. I want the exported product in Excel to contain the same columns that are displayed on the form (This will vary depending on what the user selects). The acOutputForm option only exports all columns. I'm sure there is probably a better way to do this...I am stuck on the formatting of Excel cells with a border. Trying to use UsedRange, but it isn't working.


This is what I need help with:

1. Applying borders to the UsedRange of cells in the Exported Excel file. Would be even better if the code could shade (light gray), bold and border the 1st row of column header cells.

2. Clearing the Clipboard afterwards.


Here's the code -

Private Sub cmd_expExcel_Click()
Dim xlapp As Excel.Application

On Error GoTo cmd_expExcel_Click_Err
Me.frm_qcls_ttr.SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

Set xlapp = CreateObject("Excel.Application")

With xlapp
    .Workbooks.Add
    .ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    .Cells.Select
    .Cells.EntireColumn.AutoFit
    .Visible = True
    
    .Range("A1:L1").Select
    .Selection.Font.Bold = True
    .Range("A1").Select
End With

'Can't get border added on used range. This range will change depending on how many columns are selected on the form and how records exist.
'Need help with this part; this works as an Excel Macro, but doesn't work from Access. [embed=file 1153580][embed=file 1153581]
'Throwing Error message with the Border Code below "Object doesn't Support this Object or Method"
'With xlapp
'    .ActiveSheet.UsedRange.Borders
'    .LineStyle = xlContinuous
'    .Weight = xlHairline
'    .ColorIndex = xlAutomatic
'End With

DoCmd.GoToControl "cmd_expExcel"

'Also need help with clearing the clipboard

Set xlapp = Nothing

cmd_expExcel_click_Exit:
Exit Sub
cmd_expExcel_Click_Err:
MsgBox Error$
Resume cmd_expExcel_click_Exit

End Sub

Open in new window

Sample-Database.mdb
Sample-Excel-File.xlsx
ASKER CERTIFIED SOLUTION
NorieSenior Associate
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 5 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 5 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004