VBA Access/Excel - Create log file

Question

In MS Access is it possible to create a log file (or txt file) that shows just the modified workbook

The following code edits multiple excel workbooks however before editing the workbooks it first checks to see if the workbook is in read/write mode. If it isn’t then it will close and open the workbook till read/write is active.

For Each i In MyArray

xl.Workbooks.Open (i)
'If workbook in read only mode , close and open till read/write is active
Do Until xl.ActiveWorkbook.ReadOnly = False
    xl.ActiveWorkbook.Close (False)
    If GetAttr(i) = vbReadOnly Then _
        SetAttr i, vbNormal
    xl.Workbooks.Open (i)
If xl.ActiveWorkbook.ReadOnly = False Then Exit Do
Loop    'Loop above till read/write active

'''''More code here when workbook read/write mode
Next

A.S.H A.S.H · Accepted Answer · 2015-09-14T23:05:17

I assume you want to create a log file and write the name of each Excel workbook that you manage to open for R/W. Here is a solution using late binding:

Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.CreateTextFile("c:\myLogFile.txt")

For Each i In MyArray
  xl.Workbooks.Open (i)
  Do Until xl.ActiveWorkbook.ReadOnly = False
    xl.ActiveWorkbook.Close (False)
    If GetAttr(i) = vbReadOnly Then SetAttr i, vbNormal
    xl.Workbooks.Open (i)
    If xl.ActiveWorkbook.ReadOnly = False Then
        ts.WriteLine "opened file: " & xl.ActiveWorkbook.Name
        Exit Do
    End If 
Loop    'Loop above till read/write active

'''''More code here when workbook read/write mode
Next

VBA Access/Excel - Create log file

1 Answers