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 49 50 51 52 53 54 55 56 57 58
|
' UpdateExcelFile
' copy the data from a table into an existing excel sheet
'
Public Function UpdateExcelFile(ByRef objXL As Excel.Application, strFile As String, strSheet As String, strTable As String) As Boolean
On Error GoTo ErrorHandler
Dim strPassword As String
Dim objDB As ADODB.Connection
Dim objRS As New ADODB.Recordset
Dim objField As ADODB.Field
Dim objSheet As Worksheet
Dim objPT As PivotTable
Dim strSQL As String
'retrieve excel file password
strPassword = getExcelFilePassword()
'open the workbook
Call objXL.Workbooks.Open(strFile, 0, False, , , strPassword, True)
'open the worksheet 100-3
objXL.Worksheets(strSheet).Activate
'delete existing data and keep the column header (A1:AT1)
objXL.ActiveSheet.Range("A2:AT65536").ClearContents
'current DB connection
Set objDB = CurrentProject.Connection
'open the recordset
strSQL = "SELECT * FROM " & strTable
objRS.Open strSQL, objDB, adOpenForwardOnly, adLockReadOnly
objRS.MoveFirst
'copy the data if any
If Not objRS.EOF And Not objRS.BOF Then
objXL.ActiveSheet.Range("A2").CopyFromRecordset objRS
End If
'refresh all
objXL.ActiveWorkbook.RefreshAll
'ok
UpdateExcelFile = True
ExitHandler:
'save and close the file
objXL.ActiveWorkbook.Close True, strFile
'Clean memory
If objRS.State = 1 Then objRS.Close
Set objRS = Nothing
Set objDB = Nothing
Exit Function
ErrorHandler:
UpdateExcelFile = False
MsgBox Err.Description, vbCritical, "UpdateExcelFile exception handler"
GoTo ExitHandler
End Function |
Partager