alexfwalker81
Member
- Local time
- Today, 02:27
- Joined
- Feb 26, 2016
- Messages
- 93
I'm using the code below to write into Excel. I know that I don't need all the Recordsets, just haven't tidied it up yet.
The problem that I'm experiencing is that although I can write into Excel without issue, the Sharepoint Excel then becomes locked and there seems to be no way to 'kill' whatever is locking it. The only thing that has worked is to reboot the machine which is doing the push to Excel.
Any ideas?
The problem that I'm experiencing is that although I can write into Excel without issue, the Sharepoint Excel then becomes locked and there seems to be no way to 'kill' whatever is locking it. The only thing that has worked is to reboot the machine which is doing the push to Excel.
Any ideas?
Code:
Public Function WriteToExcel2()
Dim oExcel As Excel.Application
Dim oExcelWrkBk As Excel.Workbook
Dim oExcelWrSht As Excel.Worksheet
Dim rsID As DAO.Recordset
Dim rsDT As DAO.Recordset
Dim rsDR As DAO.Recordset
Dim rsCD As DAO.Recordset
Dim rsCN As DAO.Recordset
Dim rsPN As DAO.Recordset
Dim rsPR As DAO.Recordset
Dim rsAD As DAO.Recordset
Dim rsCM As DAO.Recordset
Dim db As DAO.Database
Dim latest_entry_ID As String
Dim latest_entry_DT As String
Dim latest_entry_DR As String
Dim latest_entry_CD As String
Dim latest_entry_CN As String
Dim latest_entry_PN As String
Dim latest_entry_PR As String
Dim latest_entry_AD As String
Dim latest_entry_CM As String
latest_entry_ID = "SELECT qry_latest_entry.[ID] FROM qry_latest_entry"
latest_entry_DT = "SELECT qry_latest_entry.[DepositType] FROM qry_latest_entry"
latest_entry_DR = "SELECT qry_latest_entry.[DateReceived] FROM qry_latest_entry"
latest_entry_CD = "SELECT qry_latest_entry.[ChequeDate] FROM qry_latest_entry"
latest_entry_CN = "SELECT qry_latest_entry.[ChequeNumber] FROM qry_latest_entry"
latest_entry_PN = "SELECT qry_latest_entry.[PayeeName] FROM qry_latest_entry"
latest_entry_PR = "SELECT qry_latest_entry.[PayeeReference] FROM qry_latest_entry"
latest_entry_AD = "SELECT qry_latest_entry.[AmountDeposited] FROM qry_latest_entry"
latest_entry_CM = "SELECT qry_latest_entry.[Comments] FROM qry_latest_entry"
Set db = CurrentDb
Set rsID = db.OpenRecordset(latest_entry_ID)
Set rsDT = db.OpenRecordset(latest_entry_DT)
Set rsDR = db.OpenRecordset(latest_entry_DR)
Set rsCD = db.OpenRecordset(latest_entry_CD)
Set rsCN = db.OpenRecordset(latest_entry_CN)
Set rsPN = db.OpenRecordset(latest_entry_PN)
Set rsPR = db.OpenRecordset(latest_entry_PR)
Set rsAD = db.OpenRecordset(latest_entry_AD)
Set rsCM = db.OpenRecordset(latest_entry_CM)
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("Excel.Application")
Else
On Error GoTo Error_Handler
End If
oExcel.ScreenUpdating = False
oExcel.Visible = False 'This is false by default anyway
Set oExcelWrkBk = oExcel.Workbooks.Open("\\vh-sql\Databases\BACS Notifier\test.xlsx")
Set oExcelWrSht = oExcelWrkBk.Sheets("tbl_BACS_entry")
oExcelWrSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = rsID![ID]
oExcelWrSht.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = rsDT![DepositType]
oExcelWrSht.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = rsDR![DateReceived]
oExcelWrSht.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = rsCD![ChequeDate]
oExcelWrSht.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) = rsCN![ChequeNumber]
oExcelWrSht.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) = rsPN![PayeeName]
oExcelWrSht.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0) = rsPR![PayeeReference]
oExcelWrSht.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0) = rsAD![AmountDeposited]
oExcelWrSht.Cells(Rows.Count, 9).End(xlUp).Offset(1, 0) = rsCM![Comments]
oExcelWrSht.Range("A1").Select
oExcelWrkBk.Save
oExcel.ScreenUpdating = True
oExcel.Visible = False
Exit_Point:
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox Err & " - " & Err.Description
GoTo Exit_Point
End Function
[/code