格式化使用VBA从Access输出Excel文件？ [英] Formatting outputted Excel files from Access using VBA?

问题描述

这里我有一些VBA代码将大量文件输出到Excel文件中。我的问题是，从这一点，有没有为它格式化Excel文件？我想做的是使列变成粗体，并使列符合标题的大小。

  Sub OutPutXL （）
 
 
 Dim qdf As QueryDef 
 Dim rs As DAO.Recordset 
 
设置qdf = CurrentDb.QueryDefs（OutputStudents）
设置rs = CurrentDb.OpenRecordset（Teachers）
 
尽管没有rs.EOF 
 qdf.SQL =SELECT * FROM Students WHERE contact ='& rs！contact& '
 
''输出到Excel 
 DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel8，_ 
 qdf.Name，C：\Users\chrisjones\Documents\投影F144 \Teachers\_ 
& rs！contact& .xls，True 
 rs.MoveNext 
循环
 
 End Sub 
  

解决方案

是的，可以！这是从我的一个代码共同入侵的，可能需要一些编辑才能工作...

 '这涉及到Excel已经被打开
 On Error Resume Next 
设置xl = GetObject（，Excel.Application）
对错误GoTo 0 
如果xl是Nothing然后
 Set xl = CreateObject（Excel.Application）
 End If 
 
设置XlBook = GetObject（filename）
'filename是指向文件链接的字符串（ C：/ .... blahblah.xls）
 
'确保excel在屏幕上可见
 xl.Visible = True 
 XlBook.Windows（1）.Visible = True 
'xl.ActiveWindow.Zoom = 75 
 
'将工作簿中的工作表定义为XlSheet 
设置xlsheet1 = XlBook.Worksheets（1）
 
'然后有一些乐趣！ 
 with xlsheet1 
 .range（A1）=some data here
 .columns（A：A）。Horizo​​ntalAlignment = xlRight 
 .rows（1： 1）。font.bold = True 
结束
 
'等等... 
  

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.

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

解决方案

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...

这篇关于格式化使用VBA从Access输出Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！