Hi Everyone,
I've been reading through the various posts on this topic and while there is some similar stuff, nothing that I could directly apply. I know this has to be fairly simple, I'm just running into difficulties and I can't seem to find the necessary reference material. So here is my situation.
The Situation: My office utilizes an Access db to track administrative processes that I maintain but we have some remote staff who cannot use this system. For them we routinely export Access reports to Excel spreadsheets and transmit them via email so these remote staff receive regular updates. As many have discussed, the various Export to Excel options produce a spreadsheet with very little of the lovely formatting present in the Access report. I don't mind as I developed a macro in Excel that actually produces a format more custom tailored to those remote staff members. However, I am trying to both empower my coworkers and disperse some of the responsibilities with regard to this formatting. I tried copying and loading the Excel macro into each employee's copy of Excel but that has proved way to time consuming and not appreciated by the coworkers.
My Desired Solution: I want to write the formatting macro I have in Excel VBA into the Export to Excel macro I have triggered by a button on the header of the report. I am just having trouble translating the Excel VBA to Access. Before anyone asks, I have activated the MS Excel 14.0 Object Library. Below is the code for the Export to Excel button on my form:
Code:
Private Sub Excelbtn_Click()
Dim M As Long, D As Long, Y As Long
Dim xlApp As Excel.Application, oWB As Excel.Workbook, oSheet As Excel.Worksheet
On Error GoTo Excelbtn_Click_Err
M = Month(Date)
D = Day(Date)
Y = Year(Date)
fName = "Graduation and Hiring Tracker - Test " & M & D & Y & ".xls"
fPath = "\\comfort\SMART$\SMART\COHORT ADMIN\TRACKERS\Graduation and Hiring Trackers\" & Y & "\"
DoCmd.OutputTo acOutputReport, "Grad and Hire Report", "Excel97-Excel2003Workbook(*.xls)", fPath & fName, True, "", , acExportQualityPrint
Call GandHFormat(fName, fPath)
The GandHFormat is a sub in a module where I hope to construct the macro. It doesn't do anything right now but I figured I would need to have the file path and name so I made those necessary inputs. If I can get this working I plan to build similar macros into all reports we may be asked to provide in spreadsheet form.
Below is the macro I am trying to translate:
Code:
' GradHire Macro
' Formatting Graduation and Hiring Tracker output for distribution.
'
' Keyboard Shortcut: Ctrl+h
'
Range("A1:X1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.Borders.LineStyle = xlDouble
End With
ActiveCell.FormulaR1C1 = _
"SMART Graduation and Hiring Tracker For " & Date & ""
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Size = "16"
Selection.RowHeight = 24
Range("A1:X1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 34
Columns("D:W").ColumnWidth = 12
Rows("2:2").WrapText = True
Rows("2:2").EntireRow.AutoFit
Range("A1:X1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Set myRange = Columns("A:X")
RowCount = Application.WorksheetFunction.CountA(myRange)
Debug.Print RowCount
Range("A2:X282").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$X$282"), , xlYes).Name = _
"Table1"
Range("A2:X282").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.SmallScroll Down:=60
Range("B3:B277").Select
Range("B277").Activate
ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1").Sort. _
SortFields.Add Key:=Range("B2:B277"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Grad and Hire Report").ListObjects("Table1"). _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("3:283").Select
Selection.RowHeight = 28
Range("B283:B283").Value = "Total number of graduation participants for " & Year(Date)
With ActiveWorkbook
.Save
.Close
End Sub
I generated this code by simply recording the macro while I applied the desired formatting in Excel. There are probably some repetitive code snippets I could get rid of. Regardless, I would really like to get this macro into an Access module and I'm just not sure how to get started. If anyone could help me figure out how to get started I would greatly appreciate it.
Thanks!
Ryan