access vba 输出 excel 时候，如何格式化导出的数据

[mdxk]

比如：access表里面的数据是 名称，数值，日期

载用代码实现输出的时候 如何格式化

数值 999，999，999
日期 yyyy年mm月dd日

这样的格式如何实现那

[tztz520]

'可以呀

Private Sub Command1_Click()
Dim Conn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim ExcelApp As New Excel.Application
Dim WorkBookObj As Workbook
Dim SheetObj As Worksheet


Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\BakDatabase.mdb"
Conn.Open
Rs.Open "Select format(index,'000,000'),format(ymdt,'yyyy-mm-dd') From tablename", Conn, adOpenKeyset, adLockOptimistic, adCmdText
'==========================================================================
Set WorkBookObj = ExcelApp.Workbooks.Open(App.Path & "\abc.xls")
Set SheetObj = WorkBookObj.Worksheets(1)
'========================================
SheetObj.Range("A1").CopyFromRecordset Rs
'========================================
'========================================
Set SheetObj = Nothing
WorkBookObj.Save
WorkBookObj.Close
Set WorkBookObj = Nothing
ExcelApp.Quit
Set ExcelApp = Nothing
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
MsgBox "OK!请您打开abc.xls文件察看!"
End Sub

[mdxk]

如何控制excel的sheet名字

[creazyfish]

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "改名"

[zuoxingyu]

看下下面的代码，希望对你有点帮助

Dim Excel As Excel.Application
Dim Book As Workbook

Private Sub Command1_Click()
Set Excel = New Excel.Application
Excel.Visible = True
Excel.SheetsInNewWorkbook = 1
Set Book = Excel.Workbooks.Add
Set xsheet = Book.Worksheets(1)


With xsheet
.Range("A1:E1").Merge
.Cells(1, 1) = "121313451" ----第一个单元格是数字

'格式化
.Cells(1, 1).Style = "comma" -----千位分隔样式，但后面带有“.00”
.Cells(1, 1).NumberFormatLocal = "_ * #,##0.0_ ;_ * -#,##0.0_ ;_ * ""-""??_ ;_@_ "----去掉一个0
.Cells(1, 1).NumberFormatLocal = "_ * #,##0_ ;_ * -#,##0_ ;_ * ""-""??_ ;_ @_ "--再去掉一个0
.cell(1.1).NumberFormatLocal = "yyyy""年""m""月""d""日""" ---日期格式
对于日期格式的数据，数据库中存的为“2004-12-12”这样的格式


记得送分哦。：）

[tztz520]

SheetObj.Name = "abc"就行了

[mdxk]

为什么

Dim ExcelApp As New Excel.Application
Dim WorkBookObj As Workbook
Dim SheetObj As Worksheet

失败

[tztz520]

你要在"引用"中引用Microsoft Excel 11.0 Object Library

[zuoxingyu]

同意楼上的

你把我给你的代码直接复制到代码框中，再慢慢调试吧

你会看到正如你所要的一样

[jilm168]

---为什么

Dim ExcelApp As New Excel.Application
Dim WorkBookObj As Workbook
Dim SheetObj As Worksheet

----失败


回答:
如果你的OFFICT版本是xp,你需要要通过 工程-----引用--Microsoft excel 10.0 object library.

如果是OFFICT版本是2000,你需要要通过 工程-----引用--Microsoft excel 8.0 object library.