1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
| Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
With xlApp
.Application.Sheets("rqt_formula").Select
.Application.Sheets("rqt_formula").Name = "Formulas"
.Application.Cells.Select
.Application.Selection.ClearFormats
.Application.Selection.Font.Name = "calibri"
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True
.Application.Selection.Interior.ColorIndex = 15
.Application.Rows("2:2").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Columns("A:S").Autofilter
.Application.Columns("A:S").EntireColumn.AutoFit
.Application.Columns("A:S").Select
.Application.Range("A1").Select
.Application.ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$S$474"), , xlNo).Name = _
"Table1"
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit
End With
Set xlApp = Nothing
Set xlSheet = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)
Exit_ModifyExportedExcelFileFormats:
Exit Sub
Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats
End Sub |
