使用 VBA 从 Access 格式化输出的 Excel 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14400818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formatting outputted Excel files from Access using VBA?
提问by Chris Jones
Here I have some VBA code that outputs a ton of files into Excel files. My question is, from this, is there anyway for it to Format the excel file a bit? What I would like to do is make the Columns bold and make the columns fit the size of the header as well.
在这里，我有一些 VBA 代码可以将大量文件输出到 Excel 文件中。我的问题是，从这里开始，它是否可以稍微格式化 excel 文件？我想做的是使列加粗并使列也适合标题的大小。
Sub OutPutXL()
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("OutputStudents")
Set rs = CurrentDb.OpenRecordset("Teachers")
Do While Not rs.EOF
qdf.SQL = "SELECT * FROM Students WHERE contact='" & rs!contact & "'"
''Output to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
qdf.Name, "C:\Users\chrisjones\Documents\ProjectionsFY14\Teachers\" _
& rs!contact & ".xls", True
rs.MoveNext
Loop
End Sub
采纳答案by Ed Heywood-Lonsdale
Yes it is possible! This is hacked together from one of my codes, might need a bit of editing before it works...
对的，这是可能的！这是从我的一个代码中破解出来的，可能需要进行一些编辑才能工作...
'This deals with Excel already being open or not
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
On Error GoTo 0
If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
End If
Set XlBook = GetObject(filename)
'filename is the string with the link to the file ("C:/....blahblah.xls")
'Make sure excel is visible on the screen
xl.Visible = True
XlBook.Windows(1).Visible = True
'xl.ActiveWindow.Zoom = 75
'Define the sheet in the Workbook as XlSheet
Set xlsheet1 = XlBook.Worksheets(1)
'Then have some fun!
with xlsheet1
.range("A1") = "some data here"
.columns("A:A").HorizontalAlignment = xlRight
.rows("1:1").font.bold = True
end with
'And so on...
回答by peter
this is a quick and dirty combination of Phil.Wheeler's Codeand my previous input, for me this is working. Don't forget to add Excel's Object Library in your Access-Macro.
这是 Phil.Wheeler 的代码和我之前的输入的快速而肮脏的组合，对我来说这是有效的。不要忘记在您的 Access-Macro 中添加 Excel 的对象库。
Sub doWhatIWantTheDirtyWay()
pathToFolder = "C:\Users\Dirk\Desktop\myOutputFolder\"
scaleFactor = 0.9
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(pathToFolder)
For Each objFile In objFolder.Files
If objFso.GetExtensionName(objFile.path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.path)
For Each sh In objWorkbook.Worksheets
If sh.UsedRange.Address <> "$A" Or sh.Range("A1") <> "" Then
With sh
columncount = .Cells(1, 256).End(xlToLeft).Column
For j = 1 To columncount
With .Cells(1, j)
i = Len(.Value)
.ColumnWidth = i * scaleFactor
.Font.Bold = True
End With
Next
End With
End If
Next
objWorkbook.Close True
End If
Next
objExcel.Quit
End Sub
回答by MMerry
I have come across this problem a couple of times as well. As @Remou said, you will need to open excel to format xls files, this modification of your code silently opens Excel and that should get you in the right direction. Remember to add a reference to the Microsoft Excel Object Library in your VBA project.
我也遇到过几次这个问题。正如@Remou 所说，您需要打开 excel 来格式化 xls 文件，对代码的这种修改会默默地打开 Excel，这应该会让您朝着正确的方向前进。请记住在 VBA 项目中添加对 Microsoft Excel 对象库的引用。
Sub OutPutXL()
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Dim xl as Excel.Application
Dim wb as Object
Dim strFile as string
Set qdf = CurrentDb.QueryDefs("OutputStudents")
Set rs = CurrentDb.OpenRecordset("Teachers")
Set xl = New Excel.Application
xl.DisplayAlerts = False
Do While Not rs.EOF
qdf.SQL = "SELECT * FROM Students WHERE contact='" & rs!contact & "'"
'Output to Excel
strFile = "C:\Users\chrisjones\Documents\ProjectionsFY14\Teachers\" & rs!contact & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qdf.Name, strFile, True
'Start formatting'
Set wb = xl.Workbooks.Open(strFile)
With wb.Sheets(qdf.name)
'Starting with a blank excel file, turn on the record macro function'
'Format away to hearts delight and save macro'
'Past code here and resolve references'
End With
wb.save
wb.close
set wb = Nothing
rs.MoveNext
Loop
xl.quit
set xl = Nothing
End Sub
回答by Bmo
You could (depending on the number of files) make a template for each file you are outputting. In the long run if someone needs to change the formatting they can change the template which is going to be easier on you now that you don't have to sift through a bunch of excel formatting garbage. You could even let a qualified end user do it.
您可以（取决于文件数量）为您输出的每个文件制作一个模板。从长远来看，如果有人需要更改格式，他们可以更改模板，这对您来说会更容易，因为您不必筛选一堆 excel 格式垃圾。您甚至可以让合格的最终用户来做。
It's one of the biggest problems I have with excel sheets if I wrote the VBA I am responsible until I die for it. This way (in theory) they should be able to change a column, without changing how the data is outputted, just presented without you.
如果我编写了我负责的 VBA，那么这是我在使用 excel 表时遇到的最大问题之一，直到我为此而死。这样（理论上）他们应该能够更改列，而无需更改数据的输出方式，只需在没有您的情况下呈现。
+1 To open the excel file itself and format it using that automation though.
+1打开excel文件本身并使用该自动化对其进行格式化。
