Write to Excel - Sharepoint (1 Viewer)

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?

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
 

Isaac

Lifelong Learner
Local time
Today, 02:27
Joined
Mar 14, 2017
Messages
8,738
You forgot to include a line of code to close the Excel workbook.

One last thought. Your code is going to create an Excel application if it's not already open on the host machine. If it is already open, your code will attempt to "use" that already-open Excel application instance. Then, at the end of the code, it makes that application instance invisible.
What if the user has something open, and doesn't want their Excel application to be rendered unexpectedly invisible?

I personally recommend using CreateObject so that you have full control over (and so that it actually makes sense to have full control over), that application instance, rather than trying to leverage GetObject at all.
 

Users who are viewing this thread

Top Bottom