Dim appExcel As Excel.Application Dim bokExcel As Object Dim shtExcel As Object Dim lngRowNbr As Long Dim lngColNbr As Long Dim lngCtr_Row As Long Dim lngCtr_Col As Long
On Error GoTo ErrorHandle
Set appExcel = New Excel.Application
Set appExcel = CreateObject("Excel.Application")
'For debug only if visible set to true appExcel.Visible = False
'Add a new book appExcel.Workbooks.Add Set bokExcel = appExcel.ActiveWorkbook
Set shtExcel = bokExcel.Worksheets("Sheet1")
'Get data lngRowNbr = mshfgCommLst.Rows lngColNbr = mshfgCommLst.Cols
shtExcel.Cells.Font.Size = 8
shtExcel.Cells(1, 1) = Me.Caption
With shtExcel
'Title For lngCtr_Col = 1 To lngColNbr .Cells(2, lngCtr_Col).Value = mshfgCommLst.TextMatrix(0, (lngCtr_Col - 1)) Next lngCtr_Col
'Contents If Me.QueryType = EnumQueryType.Detail Then 'Skip one row in mshfgCommLst (Date Value) For lngCtr_Row = 3 To (3 + (lngRowNbr - 3)) '-3: 1 is for title , 1 is for Date value
For lngCtr_Col = 1 To lngColNbr .Cells(lngCtr_Row, lngCtr_Col).Value = mshfgCommLst.TextMatrix((lngCtr_Row - 1), (lngCtr_Col - 1)) Next lngCtr_Col
Next lngCtr_Row
Else For lngCtr_Row = 3 To (3 + (lngRowNbr - 2)) '-2: 1 is for title
For lngCtr_Col = 1 To lngColNbr .Cells(lngCtr_Row, lngCtr_Col).Value = mshfgCommLst.TextMatrix((lngCtr_Row - 2), (lngCtr_Col - 1)) Next lngCtr_Col
然後就可以用vba來操縱這個文件囉!
Dim appExcel As Excel.Application
Dim bokExcel As Object
Dim shtExcel As Object
Dim lngRowNbr As Long
Dim lngColNbr As Long
Dim lngCtr_Row As Long
Dim lngCtr_Col As Long
On Error GoTo ErrorHandle
Set appExcel = New Excel.Application
Set appExcel = CreateObject("Excel.Application")
'For debug only if visible set to true
appExcel.Visible = False
'Add a new book
appExcel.Workbooks.Add
Set bokExcel = appExcel.ActiveWorkbook
Set shtExcel = bokExcel.Worksheets("Sheet1")
'Get data
lngRowNbr = mshfgCommLst.Rows
lngColNbr = mshfgCommLst.Cols
shtExcel.Cells.Font.Size = 8
shtExcel.Cells(1, 1) = Me.Caption
With shtExcel
'Title
For lngCtr_Col = 1 To lngColNbr
.Cells(2, lngCtr_Col).Value = mshfgCommLst.TextMatrix(0, (lngCtr_Col - 1))
Next lngCtr_Col
'Contents
If Me.QueryType = EnumQueryType.Detail Then
'Skip one row in mshfgCommLst (Date Value)
For lngCtr_Row = 3 To (3 + (lngRowNbr - 3)) '-3: 1 is for title , 1 is for Date value
For lngCtr_Col = 1 To lngColNbr
.Cells(lngCtr_Row, lngCtr_Col).Value = mshfgCommLst.TextMatrix((lngCtr_Row - 1), (lngCtr_Col - 1))
Next lngCtr_Col
Next lngCtr_Row
Else
For lngCtr_Row = 3 To (3 + (lngRowNbr - 2)) '-2: 1 is for title
For lngCtr_Col = 1 To lngColNbr
.Cells(lngCtr_Row, lngCtr_Col).Value = mshfgCommLst.TextMatrix((lngCtr_Row - 2), (lngCtr_Col - 1))
Next lngCtr_Col
Next lngCtr_Row
End If
End With
'Save
bokExcel.SaveAs FileName:=strFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'Quit
appExcel.Quit
On Error GoTo 0
OutputResult = True
Exit Function
ErrorHandle:
If Err.Number <> 1004 Then 'Cancel
MsgBox Err.Description, vbCritical, "Output result"
End If
If (appExcel Is Nothing = False) Then
appExcel.Quit
End If
On Error GoTo 0
OutputResult = False