Retrieving Data From Closed Workbook

[jstevens]

Is it possible to retrieve data from a closed workbook using "readonly"? I cannot get it to work.

The code below works just fine for me but wanted to add: readonly:=true

Code: Select all

Public Function GetValue(P, F, S, ref)
'Retrieves a value from a closed workbook
Dim arg As String

'Make sure the file exists
If Right(P, 1) <> "\" Then P = P & "\"
If Dir(P & F, vbHidden) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If

'Create the argument
arg = "'" & P & "[" & F & "]" & S & "'!" & _
    range(ref).range("A1").Address(, , xlR1C1)

'Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Regards,
John

[HansV]

You're not opening the workbook, so read-only doesn't come into it.

[jstevens]

Hans,

The source file that is read continually gets updated or I should say overwritten. If the code runs while the file is being overwritten then the process fails.

Any suggestions?

Regards,
John

[HansV]

Try this version of the function:

Code: Select all

Public Function GetValue(P, F, S, ref)
  'Retrieves a value from a closed workbook
  Dim arg As String
  Dim i As Integer

  'Make sure the file exists
  If Right(P, 1) <> "\" Then P = P & "\"
  If Dir(P & F, vbHidden) = "" Then
    GetValue = "File Not Found"
    Exit Function
  End If

  'Create the argument
  arg = "'" & P & "[" & F & "]" & S & "'!" & _
    Range(ref).Range("A1").Address(, , xlR1C1)

  On Error Resume Next
  ' Up to 10 attempts
  For i = 1 To 10
    Err.Clear
    'Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    ' Get out if we succeeded
    If Err = 0 Then Exit Function
    ' Wait for one second
    Application.Wait Now + TimeSerial(0, 0, 1)
  Next i

  'We only get here if all failed
  GetValue = "Error retrieving value"
End Function

It will attempt up to 10 times to retrieve the value, at 1 second intervals.